| |
 |
|
Oracle
Tips by Burleson |
Using ORADEBUG to Trace A
Sessions SQL
Why would you want to use oradebug to capture trace information
rather than say, DBMS_SYSTEM? If all you are interested in is a
level 1 trace to say, capture SQL generated by Discoverer User
Edition in order to run the same statements from SQL PLUS then
DBMS_SYSTEM is fine. Let's look at this, let me remind you that
Discoverer User Edition opens two sessions on the database and you
need system privileges to see the trace, i.e. log in as SYS.
First you will need to get SID, SERIAL#, PADDR from V$SESSION, for
example:
SQL> select username, sid,
serial#, paddr from v$session where username='VIDEO31';
USERNAME SID SERIAL# PADDR
-----------------------------------------------------------------
VIDEO31 14 13202 820532C8
VIDEO31 15 4665 82053EC8
Once you know the SID and the SERIAL#, you can enable the trace for
each session running the command:
EXECUTE
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(<SID>, <SERIAL#>, TRUE)
Everything the user does will now be traced at trace level 1 until
you execute the SQL command again, but replacing 'TRUE' with
'FALSE'.
But what if you want a more detailed trace? Say a level 4?
Obviously, since there is no way to set the level using DBMS_SYSTEM,
we should use oradegub instead. Let's look at that example next.
Sometimes the trace at level 1 isn't enough, because in the sql
statements there are some bind variables. You need their values
before you run the query into SQLPLUS. In this case you have to
perform trace at level 4, so that you have the value of each bind
variable in the .trc file.
This is a book excerpt from:
Using the
Oracle oradebug Utility
Debugging Oracle Applications
Mike Ault
ISBN: 0-9740716-7-6
http://www.rampant-books.com/ebook_oradebug.htm
|
