The DBMS_System Package

The DBMS_System package contains a number of routines that can be useful on occasion. Oracle clearly state that these routines are not supported so proceed at your own risk:

ksdwrt
Set_Sql_Trace_In_Session
Set_Ev
Read_Ev

ksdwrt

Used to write messages to the alertlog and/or trace files:

EXEC DBMS_System.ksdwrt(n, message);
EXEC DBMS_System.ksdwrt(2, 'My Test Alertlog Message');

Where the value of "n" indicates the destination:

1 - Write to trace file.
2 - Write to alertlog.
3 - Write to both.

Set_Sql_Trace_In_Session

Used to set trace on or off in another users session:

EXEC DBMS_System.Set_Sql_Trace_In_Session(sid, serial#, true );
EXEC DBMS_System.Set_Sql_Trace_In_Session(31, 97, true );

The values for SID and SERIAL# can be found using the V$SESSION view.

Set_Ev

Used to set trace on for a specific event:

EXEC DBMS_System.Set_Ev(sid, serial#, event, level, name);
EXEC DBMS_System.Set_Ev(31, 97, 10046, 4, '');

Where level indicates the following levels of trace:

1 - Standard SQL_TRACE functionality.
4 - As level 1 plus tracing of bind variables.
8 - As level 1 plus wait events.
12 - As level 1 plus bind variables and wait events.

Read_Ev

Used to check if a specific event is currently being traced:

EXEC DBMS_System.Read_Ev(event, output);

If output = 1 the event is being traced.

Hope this helps. Regards Tim...

Back to the Top.