log_buffer

Consider increasing the log_buffer database parameter if the following two system statistics are noticably abover zero.

SELECT name, value from SYS.v_$sysstat
WHERE NAME in
('redo buffer allocation retries','redo log space wait time');

These statistics are cumulative since database startup.

database upgrades

Links relating to database upgrades.

http://searchoracle.techtarget.com/guide/allInOne/category/0,,sid41_tax306960_idx0_off10,00.html

partition management

Create partitioned tables using "partition exchange" method

http://myracle.wordpress.com/2007/09/10/creating-partitioned-table-using-partition-exchange-method/

AQ Notes

Documentation relating to AQ Tables / Views

http://download.oracle.com/docs/cd/B19306_01/server.102/b14257/aq_views.htm#CHDBACDJ

trunc function

Use the following syntax that returns the start of the previous month. The sql uses the add_months function, which moves back x number of months, currently set to 12.

select trunc(to_date(add_months(sysdate,-12)), 'MONTH') from dual;

Rename Tablespace

Until 10g, this is not really possible, although you can create a staging tablespace etc.

However in 10g, use the following simple command and it works a treat.

ALTER TABLESPACE "old tablespace name " RENAME TO "new tablespace name";

oracle managed files

Refer to the following link for further information.

http://download.oracle.com/docs/cd/B14117_01/server.101/b10739/omf.htm

To add temp files, do the following:

alter system set db_create_file_dest='...' scope = both;
create temporary tablespace temp_01 tempfile size 1000m;

Renaming ORACLE datafiles

1.Take the tablespace where the datafile resides offline.
i.e.: alter tablespace ... offline;

2. Rename the datafile (at the OS level)
i.e.: mv old_file new_file

3. Rename the datafile at the database level
i.e.: alter database rename file 'old_file' to 'new_file';

4. Bring the tablesace online.
i.e.: alter tablepsace ... online

Finding the time of the last transaction

Use the following to find when the last transaction occured for a particular session.

to_char(sysdate - (last_call_et) / 86400, 'dd-mon-yyyy HH24:MI:SS')

last_call_et is found in v$session. 86, 400 refers to the number of seconds in a given day.

Therefore if the last_call_etl gives a time of 1000, this equates to 0.0116 of a day, then by including sysdate in a function gives you the date of the last activity by the session.

A value of '0', of course means that this session is working away.

ORA-1031

This error is caused for numerous permission reasons.
Below is an explanation of some of these problems and are they can be resolved.

1. Selecting a view when underlying tables are owned by an alternative schema.

The problem is the underlying tables havn't been given the correct privs to the user that owns the view. The grants for these underlying tables need to be given to owner of the view with the grant option.

ie: GRANT SELECT ON "underlying table" TO "view owner" WITH GRANT OPTION

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