Home
 Oracle at AU
 Oracle Tips
 PERL Stuff
 Family Photos
 Page Brad
 Oracle Tips

Here are some Oracle tips I have found useful in my time as a DBA.

  • To kill a user session, execute the command `alter system kill session `$SID,$SERIALŽ where the $SID value is the sid from the v$session view and the $SERIAL value is the serial# from the v$session table of the Oracle user connection you wish to kill.
  • To generate a trace file containing all the SQL executed in a specific Oracle session:
    1. Connect to Oracle using SQLPlus with a user having the ALTER SYSTEM privilege and issue the command ALTER SYSTEM SET TIMED_STATISTICS=true;
    2. Find out the sid and serial# of the user session which you want to capture the SQL executed by executing the following query:
      SELECT SID, SERIAL#, USERNAME
      FROM V$SESSION;
    3. Turn on tracing for the desired session by substituting the appropriate sid and serial# values from step 2 above in the following:
      EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION( sid, serial#, true );
    4. Execute the application code you want to trace
    5. Turn OFF the SQL trace file output with the following SQLPlus command:
      EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESION( sid, serial#, false );
    6. Examine the trace output file for the SQL executed by the monitored session. To find the correct trace file, grep for the following pattern in the user dump destination directory for the instance:
      grep sid.serial# *.trc

Banner Specific Oracle Tips

  • To re-link Banner (an Oracle app), run the file $BANNER_HOME/install/maklinks.shl.
  • To recompile all Banner Pro*C and Pro*Cobol programs, execute the *cmpl*.shl scripts in $BANNER_HOME/links.  You should do this each time you upgrade Oracle versions.

For Oracle tips from my classmate Jonathan Gennick, please visit http://www.gennick.com/.  YouŽll also find a list of books heŽs written including the OŽReilly title Oracle SQL*Plus, The Definitive Guide.