Enable trace in a running session from PL/SQL or SQL*Plus

Using Oracle SID and SERIAL# can turn trace on/off a Oracle session that has already started. The values of SID and serial# can be obtained from GV$SESSION. This will create trace file in directory set by the parameter user_dump_dest. To find the SID and SERIAL# you want to debug you can query GV$session to query by program, username, machine, terminal.

To start trace:
SQL> exec dbms_support.start_trace_in_session(<SID>, <Serial#>);

To stop trace:
SQL> exec dbms_support.stop_trace_in_session(<SID>, <Serial#>);

To start trace with Wait Event data with SQL trace
exec sys.dbms_support.start_trace(<SID>, <Serial#>,waits => TRUE, binds=> TRUE);

If one needs to turn timed statistics on for another session, one can execute it through the package dbms_system.

SQL> exec sys.dbms_system.set_bool_param_in_session(sid => <sid>, serial# => <seral#>, parnam => ‘TIMED_STATISTICS’, bval => true);

To install the DBMS_SUPPORT package if it doesn’t exists in the database run the following script to create/install the package and setup access for other users other than sysdba.
SQL> connect / AS SYSDBA
SQL> @?/rdbms/admin/dbmssupp.sql
SQL> GRANT execute ON dbms_support TO SCOTT_DBA;
SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;

In 10g there is a new utility DBMS_MONITOR which you can use to achieve the, eg:
exec dbms_monitor.session_trace_enable (session_id=>139, serial_num=>53, waits=>true, binds=>false); For additional information you can the following article by Oracle http://www.oracle.com/technology/oramag/oracle/04-sep/o54talking.html

One can also turn trace on an already running session using oradebug, once session id (SID) has been identified.
SQL> select p.PID, p.SPID, s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = 62;

———- ———— ———-
68 5243378 62

1 row selected.

SQL> connect /as sysdba
— attach to session by OS process id
SQL> oradebug setospid 5243378;
Oracle pid: 68, Unix process pid: 5243378, image: oracle@hostname (TNS V1-V3)
— sets maximum dump file size to be unlimited
SQL> oradebug unlimit
Statement processed.
— turn trace on
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
— after gathering information from the same session which is already attached to ospid 5243378 turn off the trace
SQL> oradebug event 10046 trace name context off
Statement processed.

SQL> ALTER SESSION SET EVENTS='10046 trace name context forever, level 12';

Session altered.


One thought on “Enable trace in a running session from PL/SQL or SQL*Plus

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.