Monday, December 13, 2010

Date function in Oracle

Date function in Oracle
 examples of date function:
   insert into mydate values (sysdate -1);
   insert into mydate values (sysdate - 6/24); #   6 hours ago
  insert into mydate values (sysdate - 720/1440); # 12 hours ago
  click read for complete example

usage
 delete noprompt archivelog until time 'sysdate - 1';
 delete archivelog all backed up 1 times to device type disk completed before 'sysdate-6/24';

Sunday, December 12, 2010

Tablespace details ( includes datafiles, type, autoextend, maxsize etc)


Summary
1. list datafiles
2. get type, auroextend, management type etc

Tablespace report




Run the following sql to get the space report in Oracle

Archiver Hung in Oracle database - ORA-16038, ORA-19504, ORA-00257

Archiver Hung in Oracle database

Steps to delete archive log file not needed for recovery (older than the last backup of the database)

Summary
1. check how much space is used by archiver
2. check the last good backup of the database
3. delete archive log files older than last good backup
4. crosscheck archive log

Friday, December 10, 2010

Blocking in Oracle Database

Follow the steps to locate and terminate blocking session from Oracle database (10g & up). Blocking_session_status contains ‘VALID’ when blocking_session is populated in 10g and up.
steps:
1. Display blocked session and their blocking session details
2. Find what is Blocking session Doing
3. Find sid and serial # of blocking session
4.  To terminate the session: