Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | 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.

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.

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.