How to write the event in Alert log or trace file in Oracle

The DBMS_SYSTEM package in Oracle is a powerful tool that provides various functions to interact with the database system. Although Oracle states that these routines are not officially supported, they can be incredibly useful for database administrators and developers. Let’s dive into some of the key functions and their usages.

Key Functions of DBMS_SYSTEM

1. ksdwrt

The ksdwrt function is used to write messages to the alert log and/or trace files. This can be helpful for logging custom messages during debugging or monitoring.

Usage:

EXEC DBMS_SYSTEM.ksdwrt(1, 'My Test Trace File Message');
EXEC DBMS_SYSTEM.ksdwrt(2, 'My Test Alert Log Message');
EXEC DBMS_SYSTEM.ksdwrt(3, 'My Test Message to Both');

• 1: Write to trace file.
• 2: Write to alert log.
• 3: Write to both.

2. SET_SQL_TRACE_IN_SESSION

This function enables or disables SQL trace for another user’s session. It’s useful for performance tuning and monitoring specific sessions.

Usage:

EXEC DBMS_SYSTEM.set_sql_trace_in_session(31, 97, true);

• 31: Session ID (SID).
• 97: Serial number.
• true: Enable trace (use false to disable).

3. SET_EV

The SET_EV function sets trace for a specific event, allowing detailed tracing of SQL execution, bind variables, and wait events.

Usage:

EXEC DBMS_SYSTEM.set_ev(31, 97, 10046, 12, '');

• 10046: Event number for SQL trace.
• 12: Trace level (1 for standard, 4 for bind variables, 8 for wait events, 12 for both).

4. READ_EV

This function checks if a specific event is currently being traced, which can be useful for verifying trace settings.

Usage:

EXEC DBMS_SYSTEM.read_ev(10046, output);

output: Returns 1 if the event is being traced.