Oracle Tips and Tricks — David Fitzjarrell

July 25, 2008


Filed under: General — dfitzjarrell @ 19:07

I’ve heard this lament many, many times:

“I need to trace a session that is already connected but isn’t mine. How do I do that?”

It’s really a fairly easy task. Oracle provides three packaged procedures to make that possible:




and, of the three, I prefer DBMS_SYSTEM.SET_EV as it provides a level of control not easily implemented with DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION or DBMS_SUPPORT.START_TRACE_IN_SESSION. The procedure is fairly easy to use (you must be connected as SYS to use it):

exec dbms_system.set_ev(<sid>,<serial#>, <event>, <level>, <name, usually NULL>)

So, if your user has been assigned sid 459 and serial# 11703 and you want a full-bore 10046 trace you would execute:

exec dbms_system.set_ev(459, 11703, 10046, 12, NULL)

and you’d have a level 12, 10046 event trace started on that session. Woohoo! So, what is this NM parameter used for? Oracle says it’s to document the trace name, and you can use it to ‘name’ the trace you’re starting so you can keep track of whose session you’re monitoring. Of course if you do provide a name string when you start the trace then you need to provide that same string when you terminate that trace else it won’t be stopped. So, I just leave it NULL.

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION is a rather simplistic interface to DBMS_SYSTEM.SET_EV, giving you the opportunity to start and stop a trace, period. No setting levels, no deciding what you want to see, just the ability to start and stop a basic trace on a session which is not your own. Oh, and there’s also the possibility that you won’t get a trace started with this interface (I don’t know why it fails, but I’ve had spotty results using this particular procedure.) I prefer to use the DBMS_SYSTEM.SET_EV procedure for that very reason.

DBMS_SUPPORT.START_TRACE_IN_SESSION provides a bit more flexibility by allowing you to include waits, binds, both or neither in your trace output:

exec dbms_support.start_trace_in_session(<sid>,<serial#>, <waits, boolean>, <binds, boolean>)

To use DBMS_SUPPORT.START_TRACE_IN_SESSION to effect the same level of trace you started using DBMS_SYSTEM.SET_EV you’d submit:

exec dbms_support.start_trace_in_session(459, 11703, TRUE, TRUE)

and you’d have the job done.

So you have the trace started, how on earth do you stop it? The trace will end when:

1) The traced session terminates
2) You execute DBMS_SYSTEM.SET_EV(sid,serial#, event, 0, NULL) if that’s the procedure you used to start the trace

The DBMS_SYSTEM package is installed when you create a database; unfortunately the DBMS_SUPPORT package isn’t, so if you want to use it you’ll need to execute the dbmssupp.sql script in $ORACLE_HOME/rdbms/admin:

SQL> @?/rdbms/admin/dbmssupp

Don’t run this as any user other than SYS, or it’s not likely to work properly, if at all. And, since DBMS_SUPPORT isn’t installed by default that’s all the more reason to fire up DBMS_SYSTEM.SET_EV to trace other sessions.

Once the trace file is written you’d process it just like any other 10046 trace file, by using the tkprof utility to format the raw trace data into a really pretty report; the trace files generated by ‘alter session set events …’ and the DBMS_SYSTEM.SET_EV procedure are the same. The only difference is in how you get the event trace started.

Because Oracle support personnel needed the ability to trace user sessions the DBMS_SYSTEM package was created. Of course you shouldn’t be using this all of the time, on every session, because, in reality, not every session needs to be traced. And, if you do feel that need it’s simpler and easier to change the init.ora or spfile to enable event 10046 tracing.

You may not use this utility even once per year, but it’s nice to know you have access to it should the need arise. It’s always better to have something and not need it rather than need something and not have it. So tuck this information away for that ‘rainy day’ that will eventually arrive. I think you’ll be glad that you did.


1 Comment »

  1. In 10g, you get DBMS_MONITOR.SESSION_TRACE_ENABLEand you don't have to worry about running extra scripts.

    Comment by Gary Myers — June 9, 2009 @ 01:03

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

Blog at

%d bloggers like this: