Rommel's Notes

Flushing the SGA

2005-08-29T17:19:00.000-07:00

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

2005-08-25T22:38:00.000-07:00

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

2005-08-25T22:34:00.000-07:00


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

2005-08-25T22:31:00.000-07:00

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

2005-08-25T22:30:00.000-07:00

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

2005-08-25T22:24:00.000-07:00

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

2005-08-25T22:17:00.000-07:00

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

2005-08-25T16:36:00.000-07:00

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

2005-08-25T00:03:00.000-07:00

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

Killing a Session

alter system kill session ‘sid,serial#’;

Set TRACE for an Explain Plan

set autotrace traceonly explain

Estimate Statistics

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

Whenever you want to exit

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

Escape Character _

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

Recompile Package Bodies

alter package <package_name> compile body;

Archive Log Start Stop and switching logs

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