How to find/list the events set in a session?

Using “oradebug dump events 1” one can find/list events set in a session.

For example:
In a session events 10046 and 1410 events are set
SQL> ALTER SESSION SET EVENTS='10046 trace name context forever, level 12';

Session altered.

SQL> ALTER SESSION SET EVENTS='1410 trace name context forever, level 12';

Session altered.

SQL> SELECT distinct sid FROM v$mystat
SID
———-
131
In another session login as an account as sysdba
SQL> SELECT pid, spid FROM v$process where addr IN (SELECT paddr FROM V$session where sid = 131);
PID SPID
———- ——-
12 3420

SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)

SQL> oradebug dump events 1
Statement processed.

SQL> oradebug tracefile_name
/u01/oradata/admin/TEST/udump/test_ora_10932.trc

Or in 11g:
SQL> oradebug eventdump session
1410 trace name context forever, level 12
sql_trace level=12

After running the above oradebug will dump the events in the trace file in the above case /u01/oradata/admin/TEST/udump/test_ora_10932.trc shows event 1410 and 10046 events set.

Dump event group for level SESSION
TC Addr Evt#(b10) Action TR Addr Arm Life
974FED50 1410 1 974fede0 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
974FEBF8 10046 1 974fec88 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
*** 2010-07-15 23:40:05.671
..

One thought on “How to find/list the events set in a session?

  1. this is easier method from Julian Dyke

    Listing Enabled Events

    To check which events are enabled in the current session

    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;
    /

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 )

Google+ photo

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

Connecting to %s