| |
 |
|
Oracle
Tips by Burleson |
Tracing Errors Using
ORADEBUG
What about setting up to trace other errors? It is really quite
simple, once you set the SID you are monitoring as shown in previous
examples, just use the error code as the input to the event command
in oradebug. For example, to trace the occurance of ORA-00942 errors
you would enter the command:
ORADEBUG EVENT 942 TRACE NAME
ERRORSTACK LEVEL 3
..which will only produce anything if this session hits an ORA-942
error.
The output from oradebug is a raw trace file. Some experienced DBAs
can read these trace files in their raw state, however, I find it
much easier to use another Oracle utility, tkprof, to format the
output into human readable output.
The 'raw' Trace File is the opposite of the tkprof'd version, in
that it shows you the exact sequence in which the various pieces of
SQL were run.
Just bear in mind the following: before it is actually executed, any
piece of SQL is parsed into the SGA. It is allocated a CURSOR # at
this point. This CURSOR # will remain in memory, containing the same
piece of SQL code, until another piece of SQL needs to overwrite the
memory, at which point the CURSOR # becomes available for re-use as
well.
Whenever a piece of SQL is actually executed, an EXEC line is
written to the Trace File. Highly simplified, you might see
something like this in the 'raw' Trace File:
PARSE #1
SELECT 'x' FROM TABLE1;
PARSE #2
SELECT 'y' FROM TABLE2;
EXEC #1
EXEC #1
EXEC #2
PARSE #1
UPDATE TABLE1 SET COLUMN1 = :b0;
EXEC #1
Note that CURSOR #1 has now been over-written with a new SQL
statement, so any further EXEC statements for that cursor will
relate to the 'new' SQL.
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
|
