[Oracle Tips] How to find out if a trace event is enabled or disabled?

You can use dbms_system.read_ev procedure to find the event status. If the level is zero, the event is disabled, otherwise, enabled. The following wrapper function makes it easier to use this procedure:

1 2 3 4 5 6 7 8 9 10 11

create or replace function is_event_enabled(event IN INTEGER) return VARCHAR2 is level binary_integer; begin dbms_system.read_ev(event, level); IF (level > 0) THEN return 'Y'; END IF; return 'N'; end; /

The following example shows a simple use case:
SQL> ALTER SYSTEM SET EVENTS '1031 trace name context forever, level 16';

System altered.

SQL> select is_event_enabled(1031) from dual;

IS_EVENT_ENABLED(1031)
----------------------
Y


SQL> ALTER SYSTEM SET EVENTS '1031 trace name context off';

System altered.

SQL> select is_event_enabled(1031) from dual;

IS_EVENT_ENABLED(1031)
----------------------
N