| |
 |
|
Oracle 10g data mining
tools?
Oracle Tips by
Robert Freeman |
Using ORADEBUG To Set A Trace Event
ORADEBUG is the method I
normally use to enable 10046 tracing of another session. It
seems to be the most straightforward to me. Before we can use
ORADEBUG, we need some information on the session we want to
trace. In the query that we did earlier to identify the session
we wanted to trace, did you notice that we also returned the
SPID and PID columns? We did this to allow us to use ORADEBUG to
connect to the session and direct it to start tracing, as you
will see shortly.
To use ORADEBUG, we first need
to start SQL*Plus and connect to the database as SYS using the
SYSDBA login. You must have SYSDBA privileges to run ORADEBUG.
Next, we need to connect to
the session by setting the ORADEBUG session to the OS Process id
(PID) or to the Oracle ID (SPID) of the session we want to
trace. For example, if we want to trace session SID 144, which
is serial number 36 we would note that the SPID of that session
is 1864, and the PID of that session is 21.
Now that we know this
information, we are ready to use ORADEBUG. Here is an example
using the SPID of the session to start tracing:
SQL> oradebug setospid 1864;
We could also use the PID to
connect to the session as seen in this example:
SQL> oradebug setpid 21;
You can then instruct the session to begin
tracing with this oradebug statement:
SQL> oradebug event 10046
trace name context forever, level 12
To disable tracing use this
statement:
SQL> oradebug event 10046
trace name context off
Using DBMS_SYSTEM.SET_EV To
Set A Trace Event
The dbms_system.set_ev
procedure can be used to set events too. The procedure has the
following syntax:
Dbms_system.set_ev (
si
binary_integer, -- SID
se binary_integer,
-- Serial#
ev binary_integer,
-- Event code or number to set.
le binary_integer,
-- Usually level to trace
cm binary_integer
-- When to trigger (NULL = context
forever.)
To enable 10046 level 12
tracing for SID 22 with a serial# of 99 we would execute the
set_ev procedure as shown here:
Exec
dbms_system.set_ev(22,99,10046,12,??);
To disable 10046 tracing enter
the following:
Exec
dbms_system.set_ev(12,22,10046,0,??);
Locating the Oracle Trace File
The location of the trace file
that will get created is pointed to by the user_dump_dest
parameter. ORADEBUG will also provide the location and name of
the trace file:
SQL> oradebug tracefile_name
c:\oracle\product\admin\booktst\udump\booktst_ora_220.trc
Click here
to read next section
Click here to read
previous section
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
|
|
and include the URL for the page.
