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;