8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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
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...