Oracle DBA Daily Experiences

The following example shows how to set event on a oracle session

To turn on event, needs to be run as sysdba
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.

To turn off event
– 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.

List of events can be found in $ORACLE_HOME/rdbms/mesg/oraus.msg

To turn on trace for a specific event.
exec dbms_system.set_ev(<SID>, <serial#>, <event>, 8, '');
Example: Enable trace at level 8 for session id 10046
exec dbms_system.set_ev(12345, 543211, 10046, 8, '');

– To turn off the tracing:
exec dbms_system.set_ev(<SID>, <serial#>, <event>, 0, '');
Example: exec dbms_system.set_ev( 1234, 56789, 10046, 0, '');