table sizings ...

select owner, table_name, num_rows, avg_row_len, round((num_rows * avg_row_len)/(1024*1024)) "Table Size"
from dba_tables where table_name like 'DEPOSITS_BLUE_WK%';

select owner, segment_name, tablespace_name, round(bytes/(1024*1024))
from dba_segments where tablespace_name = 'OFS_INS_DAT01' and segment_name = 'DEPOSITS_BLUE_WK' ORDER BY 1,2;

select owner, table_name, tablespace_name, num_rows, avg_row_len
from dba_tables where tablespace_name = 'OFS_INS_DAT01';

select owner, table_name, round(num_rows * avg_row_len * (1 - (pct_free / 100))/(1024*1024)) "Table Size (M)"
from dba_tables where tablespace_name = 'OFS_INS_DAT01' and table_name = 'DEPOSITS_BLUE_WK';

select owner, table_name, round((num_rows * avg_row_len)/(1024*1024)) "Table Size (M)"
from dba_tables where tablespace_name = 'OFS_INS_DAT01' and table_name = 'DEPOSITS_BLUE_WK';

select owner, segment_name, tablespace_name, segment_type, bytes/(1024*1024)
from dba_segments where segment_name = 'DEPOSITS_BLUE_WK';

select * from dba_tables where table_name = 'DEPOSITS_BLUE_WK';

Logging in as another user ...

Somtimes DBAs may want to log in as another user. Use the following mechanism to do this.

1. List all details for user by quering the DBA_USERS database view.

n205554@edrtst> select * from dba_users where username = 'OPS$N175442';

USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DAT EXPIRY_D
------------------------------ ---------- ------------------------------ ---------------------------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE INITIAL_RSRC_CONSUMER_
------------------------------ ------------------------------ -------- -----------------------------
EXTERNAL_NAME
----------------------------------------------------------------------------------------------------
OPS$N175442 394 23EFD25A727EF805 OPEN
USER_DAT01 TEMP01 10/04/03 DEFAULT DEFAULT_CONSUMER_GROUP

2. Change the password of the user shown in (1).

n205554@edrtst> alter user OPS$N175442 identified by geoff;

User altered.

3. Do whatever you need to do.

4. Change the password back by using the 'identified by values' keyword in the 'alter user' command. The string shown below was found in the password field of the dba_users view (see 1) before the password was changed.

n205554@edrtst> alter user OPS$N175442 identified by values '23EFD25A727EF805';

User altered.

Be very careful when using this as as you may be bypassing corporate security rules, depending on your organization.

RMAN Recovery

connect target /

-- ensure database is in mount mode.

run {
SET UNTIL TIME "to_date('09-OCT-2007 15:00:00', 'DD-MON-YYYY HH24:MI:SS')";
RESTORE DATABASE;
RECOVER DATABASE;
}

-- startup database, must use resetlogs if recovery to point in time is used.
-- note: to_date function is used, so you can choose whatever date format you want can be used.

Oracle Client / Server Interoperability Support Note

METALINK NOTE

ORA-4030 summary

4030 errors relate to server process errors. That is a user is running a query and the session they are running hits this 4030 error because the server is not able to allocate more memory to the process.

questions to ask:

is there sufficient memory available on the server ?

is there an operating system limit present ?

which process is requesting too much memory ?

how to collect information on what the process is doing ?

Metalink Note of interest: 233869.1

DBMS_STATS

- DBMS_STATS DEFINITION

Useful DBA Links

- SKILL BUILDERS
- SCIBD
- ORACLE DEMOS

User Environment Parameters

Use the following examples and documentation to find database environmental information.

-- find current database.

select sys_context('USERENV', 'DB_NAME') from dual;

Use the following documentation to find a full list of parameters.

SYS_CONTEXT ORACLE DOCUMENTATION

PLSQL and MAIL

Use the following plsql package to use internal plsql packages to send emails.

NOTE:
You need to ensure the domain and mail servers are updated.

demo_mail.sql

Oracle Support Notices

oracle unsupported notice for database 9.2

Resumable Space Allocation

Privileges Required

To allow a user to enable resumable at the session level --> grant resumable to
To allow all sessions to have resumable setup -->

Setting Up Resumable Statements at Session level

alter session enable resumable;
alter session enable resumable timeout
alter session enable resumable timeout name 'some descriptive name'

… run the sql …

alter session disable resumable;

Resumable Space Allocation Documentation

DBA_RESUMABLE VIEW DEFINITION
DBMS_RESUMABLE PACKAGE DEFINITION
MANAGING RESUMABLE SPACE ALLOCATION
PSOUG Resumable Space Management