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
)

For CM: NULL means CONTEXT FOREVER.

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)!
Usn