Oracle Study Notes: 10046 SQL tracle event

10046event can track the SQL statements related to Orale. The trace records are recorded in the directory specified by user_dump_dest.

There are four levels:

• 1: Execute SQL statement tracking;
• 4: bind variables;
• 8: Wait for the event;
• 12: bind variables and wait

 

Open event tracking:

1. For all sessions, set-open orAlter system set: event = '2014 trace name context forever, level n' [scope = spfile]-- Restart Oracle
2. For all sessions, set-close orAlter system set: event = ''[scope = spfile]-- Restart Oracle
3. For the current session, open:Alter session set events = '10046 trace name context forever, level N'-- Takes effect immediately
4. For the current session, close:Alter session set events = '2014 trace name context off'-- Takes effect immediately
5. How to track other sessions:

Step 1: Determine the PID (processid of Oracle) or spid (process PID of OS) to be tracked ):

Select a. Sid, A. Serial #, B. spid, B. PID, A. username, A. osuser, A. Machine

From v $ session A, V $ process B

Where a. username is not null and A. paddr = B. ADDR;

Step 2: UseSysdbaLogin identity sqlplus

Step 3: Initialize the trail,Oradebug setpid pid_n | oradebug setospid spid_n

Step 4: Open the trail,Oradebug event 10046 trace name context forever, lever n

Step 5: Disable tracing,Oradebug event 10046 trace name context off

Others: oradebu can also set the fullpathname of the trace file,Oradebug tracefile_name filepec

6. Use dbms_system

dbms_system.set_ev (
Si binary_integer, -- sid
Se binary_integer, -- serial #
EV binary_integer, -- Event code or number to set.
le binary_integer, -- usually level to trace
CM binary_integer -- when to trigger (null = context forever .)
to enable 10046 Level 12 tracing for Sid 22 with a serial # of 99 we wowould execute the set_ev procedure as shown here:
exec dbms_system.set_ev (10046, '');
to disable tracing enter the following:
exec dbms_system.set_ev (12, 22, 10046,0, '');