Oracle dbms_system

Oracle Database Tips by Donald Burleson

Using Oracle DBMS_SYSTEM.SET_EV and Oracle DBMS_SYSTEM.READ_EV

There are two useful procedures related to events in the DBMS_SYSTEM package. They are SET_EV, used to set a specific event, and READ_EV, used to see the current status of an event. The procedures are defined as follows:


									PROCEDURE DBMS_SYSTEM.SET_EV


 Argument Name                  
									Type                    
									In/Out Default?

 ------------------------------ ----------------------- ------ --------

 SI                             
									BINARY_INTEGER          
									IN

 SE                             
									BINARY_INTEGER          
									IN

 EV                             
									BINARY_INTEGER          
									IN

 LE                             
									BINARY_INTEGER          
									IN

 NM                             
									VARCHAR2                
									IN

Oracle DBMS_SYSTEM.SET_EV To Set A Trace Event

The Oracle 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
   nm varchar2 -- sets the Event Name. 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(22,99,10046,0,'');

To disable tracing for another user's session use:

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,false);

��