14 February, 2016

Trace Files -- 12 : Tracing a Particular Process

Unlike tracing for particular SQL statements, you can also trace by PID  (Oracle PID) or Server Process ID (SPID).

SQL> select s.sid, p.pid
  2  from v$session s join v$process p
  3  on (s.paddr=p.addr)
  4  and s.username = 'HEMANT';

       SID   PID
---------- ----------
 19    22

SQL> 
SQL> alter system set events 'sql_trace {process: orapid=22}';

System altered.

SQL> 
SQL> select s.sid, p.pid, p.spid
  2  from v$session s join v$process p
  3  on (s.paddr=p.addr)
  4  and s.username = 'HR';

       SID   PID SPID
---------- ---------- ------------------------
 14    26 3207

SQL> alter system set events 'sql_trace {process:3207}';

System altered.

SQL> 


Tracing for the processes is disabled with :

SQL> alter system set events 'sql_trace {process: orapid=22} off';

System altered.

SQL> alter system set events 'sql_trace {process:3207} off';

System altered.

SQL> 


Tracing seems to get disabled after the next SQL, not immediately.

Remember : This is SQL Tracing, not Optimizer Tracing.
.
.
.

1 comment:

Mladen Gogala said...

You can also put a level, which is important:

alter system set events 'sql_trace {process : ospid = 2345} level=12';

Note that turning the trace off is equivalent to setting the level 0;