Flushing the SGA

To avoid producing gaps in sequences when flushing the shared pool, it is recommended to keep/pin the sequences before flushing the SGA.

declare   
   v_sqlstmt varchar2(500);
begin   
   for c1 in (select object_name from user_objects
              where object_type='SEQUENCE') loop
       SYS.DBMS_SHARED_POOL.KEEP(c1.object_name,'Q');
   end loop;
   execute immediate 'alter system flush shared_pool';
end;
/

Detecting Broken DBMS_JOBS

Jobs are automatically re-tried when it fails. However, after 16 re-tries, the job is marked as BROKEN and will not be tried again. Here is a query that will monitor the Job Status so that it can send a SMS to the DBA when broken jobs are detected.

DECLARE
     BROKEN_JOBS_DETECTED EXCEPTION;
     V_COUNT NUMBER;
BEGIN
     SELECT COUNT(*) INTO V_COUNT FROM DBA_JOBS WHERE BROKEN='Y';
     IF (V_COUNT > 0) THEN
          RAISE BROKEN_JOBS_DETECTED;
     END IF;
EXCEPTION
    WHEN BROKEN_JOBS_DETECTED THEN
           RAISE_APPLICATION_ERROR (-20199,'Broken Jobs Detected');
END;
/
EXIT

So in the command file:

sqlplus user/password@instance @broken_job.sql
if %ERRORLEVEL% = 20199 sendmail email@address "Broken Job Detected" "Additional Text"

The sendmail is a 4NT command. You can replace it by any command line e-mail. In 4NT.INI, you need an entry for your 'MailServer=pop.address.of.email' and 'MailAddress=email@address'.

Fixing Watermark Error in Replication

connect as REPADMIN
begin
sys.dbms_defer_internal_sys.parallel_push_recovery(<destination>,<origin>);
end;
/
commit;

Where destination = DBLINK ID of the destination database, e.g. RAPSP.ACNIELSEN.COM.AU
and origin = DBLINK of the originating database, e.g. RECODEP.ACNIELSEN.COM.AU

LARGE ROLLBACK Segments

Using a LARGE ROLLBACK SEGMENT Temporarily

alter rollback segment LARGE_RBS online;
begin
    set transaction use rollback segment LARGE_RBS;
    ... delete/insert, drop unused columns, etc...
    commit;
end;
/
alter rollback segment LARGE_RBS offline;

PL/SQL Exceptions Summary

DECLARE  
  PARENT_KEY_NOT_FOUND exception;  
  PRAGMA EXCEPTION_INIT(PARENT_KEY_NOT_FOUND,-2291);
BEGIN  
  ...sql code...  
  BEGIN      
    ... insert into a table with foreign key constraints ...  
  EXCEPTION      
    WHEN PARENT_KEY_NOT_FOUND THEN        
       ...handle it here...
       RAISE; -- raise it again--  
  END;
EXCEPTION  
  WHEN OTHERS THEN      
    ...trap it here again...
END;
/

Analyze Notes

Quick Way to Analyze a Table/Index
ANALYZE TABLE|CLUSTER|INDEX schema.name ESTIMATE|COMPUTE STATISTICS SAMPLE n ROWS|PERCENT;
Using DBMS_STATS Package
exec DBMS_STATS.GATHER_TABLE_STATS(ownname='owner', tabname=>'table_name', estimate_percent=>5, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1', block_sample=>TRUE, degree=>8, granularity=>'GLOBAL|ALL', cascade=>TRUE);

For the meaning of each parameter and other possible parameters, look at $ORACLE_HOME/rdbms/admin/dbmsstat.sql for the program documentation.

Recovery Manager (RMAN) Notes

Deleting Expired Backup of a Database in NOARCHIVELOG Mode This was raised in a TAR No. 4618448.993 which affects Oracle 8.1.7 databases in NOARCHIVELOG mode that are backed up by RMAN. Issuing a 'crosscheck backup' or a 'delete expired backup' results into a RMAN-20242 Error.


The work around is to use 'delete expired backup of database' as follows:

$ export ORACLE_SID=RAPST  /* SID of the target database */
$ rman target / catalog rman_userid/password@catalog_database
RMAN> allocate channel for maintenence type 'SBT_TAPE';
RMAN> crosscheck backup of database;
RMAN> delete expired backup of database;

Replication Push Example

declare rc binary_integer; begin rc := sys.dbms_defer_sys.push(destination=>'RECODEP.ACNIELSEN.COM.AU', stop_on_error=>FALSE, delay_seconds=>0, parallelism=>1); end;

Frequently Forgotten SQL Codes

Last modified by RommelPascual on Aug 12, 2005 at 03:37:36 AM

1. Killing a Session

alter system kill session ‘sid,serial#’;

1. Set TRACE for an Explain Plan

   set autotrace traceonly explain

1. Estimate Statistics

   ANALYZE TABLE|CLUSTER|INDEX [<schema>.]<name>[<method>] STATISTICS
           [SAMPLE <n>[ROWS|PERCENT]]

1. Whenever you want to exit

   whenever sqlerror exit sql.sqlcode rollback
   whenever oserror exit failure rollback  

1. Escape Character _

   select table_name
   from user_tables
   where table_name like 'MOS\_%' ESCAPE '\';

1. Recompile Package Bodies

   alter package <package_name> compile body;

1. Archive Log Start Stop and switching logs

    alter system archive log stop;    
    alter system archive log start;
    alter system switch logfile;