Search My Oracle Blog

Custom Search

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;

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016