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:
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:
Be careful (as always)!