Martin Klier


Oracle: Tracing of another session

I simply love 10046 level 12 traces. I described session tracing based on a logon trigger quite earlier, but some situations in real DBA life need this trace event switched on for a session other than my own AND for a short peroid in time (with no need for the connected user to log out). The package/function dbms_system.set_ev is cool for that – the syntax and the handling is very simple, and I like it much more than ORADEBUG. So it’s syntax is like:

dbms_system.set_ev (
   si    binary_integer, -- SID
   se    binary_integer, => SERIAL#
   ev    binary_integer, => event code
   le    binary_integer, => trace level
   cm    binary_integer => condition


So let’s do it step by step:
First, we need SID and SERIAL# of the session in question:

select sid, serial#
  from v$session
  where <something>;

Now, we call the package mentioned above, logged in SYS AS SYSDBA:

exec dbms_system.set_ev(10,223344,10046,12,'');

The DB traces the session verbosely into a .trc file in UDUMP directory now.

At some point, you may want to stop the trace event again, so switch it to level zero:

exec dbms_system.set_ev(10,223344,10046,0,'');

Be careful (as always)!

Oracle: Audit a failed logon attempt without auditing
Oracle: Executing an OS command from PL/SQL – ICMP ping from database

Leave a Reply

Your email address will not be published. Required fields are marked *

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