Check the event trace enable in Oracle

Check the list of event traces set in Oracle current session

The following package is used to get the current event set in Oracle at the current session:

dbms_system.read_ev(iev IN  BINARY_INTEGER,oev OUT BINARY_INTEGER);
iev: event numbers 10000 to 10999
oev: default is 0 if not set otherwise returns the event level

Following is the example of the current session:


--Check the current session SID and serial#
SELECT sid, serial# FROM SYS.V_$SESSION
WHERE SID = (SELECT DISTINCT SID FROM SYS.V_$MYSTAT);
    SID    SERIAL#
---------- ----------
       223      43129

-- Set the following event 
SQL> EXEC DBMS_SYSTEM.set_ev(si=>223, se=>43129, ev=>10001, le=>8, nm=>'');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SYSTEM.set_ev(si=>223, se=>43129, ev=>10005, le=>8, nm=>'');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SYSTEM.set_ev(si=>223, se=>43129, ev=>10010, le=>8, nm=>'');
PL/SQL procedure successfully completed.


-- Check the Event enabled at current session with following script:
SET SERVEROUTPUT ON
DECLARE
      l_level NUMBER;
  BEGIN
      FOR l_event IN 10000..10999
      LOOP
          dbms_system.read_ev (l_event,l_level);
          IF (l_level > 0) THEN
              dbms_output.put_line ('Event '||TO_CHAR (l_event) ||
              ' is set at level '||TO_CHAR (l_level));
          END IF;
      END LOOP;
  END;
  /
Event 10001 is set at level 8
Event 10005 is set at level 8
Event 10010 is set at level 8

PL/SQL procedure successfully completed.

Disable the EVENT :

ALTER SYSTEM SET EVENTS '<event> trace name context off';

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.