Thursday, July 24, 2014

[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

No comments: