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