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...

url:http://www.oracle-base.com/articles/8i/dbms_system.php

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9838838/viewspace-1131584/，如需轉載，請註明出處，否則將追究法律責任。