Home : A Trace Procedure For Oracle

Revision Notes

July 24, 2001 - Document Created

Downloads/Links

• None

Overview

One of the problems with tracing a process associated with an application is that the process is frequently spawned on the server or middle tier.  The SID and SERIAL# associated with the job are not the same as the user who requested the job.  This can make it very difficult to quickly identify the session you want to trace.  This script solves the problem for you.  When run it will set SQL_TRACE to TRUE on all new sessions since the time you initialized the procedure.  Then you will be sure you are capturing the trace file for the session associated with the job.  The procedure accepts three possible values for it's single parameter.

SET - Initializes the procedure by grabbing the max logon time from V$SESSION and stores it in a table.  Also sets SQL_TRACE to false for any existing sessions that are being traced by this procedure.

START - Sets SQL_TRACE = TRUE for all sessions that have a logon time after the stored logon time.

STOP - Sets SQL_TRACE to false for any existing sessions that are being traced by this procedure.

You can now check your user dump destination for the expected trace files.

Cut and paste the code into a .sql file and run it on you system.  Make sure you log in as SYS and grant execute permissions on the DBMS_SYSTEM package to the user before you run the script.  You will also need access to the V$SESSION view.

DROP TABLE trace_table / CREATE TABLE trace_table ( sid NUMBER, serial# NUMBER) / DROP TABLE trace_time / CREATE TABLE trace_time ( time DATE) / INSERT INTO trace_time (time) (SELECT MAX(logon_time) FROM v$session) / COMMIT / CREATE OR REPLACE PROCEDURE trace(p_mode VARCHAR2 DEFAULT 'SET') AS CURSOR c_traces IS SELECT sid, serial# FROM trace_table; CURSOR c_sessions IS SELECT sid, serial# FROM v$session WHERE logon_time > (SELECT max(time) FROM trace_time); BEGIN -- if this is a new trace...then...disable existing traces...store the max login time IF p_mode = 'SET' or p_mode = 'STOP' THEN FOR l_row in c_traces LOOP sys.dbms_system.set_sql_trace_in_session(l_row.sid, l_row.serial#, FALSE); END LOOP; DELETE FROM trace_table; UPDATE trace_time SET time = (SELECT MAX(logon_time) FROM v$session); ELSIF p_mode = 'START' THEN -- activate tracing for all new sessions FOR l_row IN c_sessions LOOP sys.dbms_system.set_sql_trace_in_session(l_row.sid, l_row.serial#, TRUE); INSERT INTO trace_table (sid, serial#) VALUES (l_row.sid, l_row.serial#); dbms_output.put_line('Tracing session ' || l_row.sid); UPDATE trace_time SET time = (SELECT MAX(logon_time) FROM v$session); END LOOP; END IF; COMMIT; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(dbms_utility.format_error_stack); END; /