Use oradebug To List All Internal Events Set In Oracle Database

Oracle internal events can be set at both sesson level and system level as following,

A. Session level

Session level events only affect current session. Users, who has ALTER SESSION privilege granted, can set event at session level with SQL command,

    alter session set events '<event context>' ;

For example

# -- Set sql_trace (event 10046) to level 12
user01@orcl> alter session set events '10046 trace name context forever, level 12';

# -- Unset/disable sql_trace (event 10046)
user01@orcl> alter session set events '10046 trace name context off';

# -- Set event for ORA-1652: unable to extend temp segment by %s in tablespace %s
user01@orcl> alter session set events '1652 trace name errorstack';

# -- Unset event for ORA-1652
user01@orcl> alter session set events '1652 trace name context off';

B. System level

Events set at systm level affect all database sessions. Users, who has both ALTER SYSTEM and ALTER SESSION privileges granted, can set events at system level with SQL command,

    alter system set events '<event context>';

For example

# -- Set sql_trace (event 10046) to level 12
user01@orcl> alter system set events '10046 trace name context forever, level 12';

# -- Unset/disable sql_trace (event 10046)
user01@orcl> alter system set events '10046 trace name context off';

# -- Set event for ORA-1652: unable to extend temp segment by %s in tablespace %s
user01@orcl> alter system set events '1652 trace name errorstack';

# -- Unset event for ORA-1652
user01@orcl> alter system set events '1652 trace name context off';

Note: ALTER SYSTEM SET EVENTS will set the events at system level for all sessions, as well as session level for current session. Therefore, the user has to be granted both ALTER SYSTEM and ALTER SESSION privileges to run this command.

In order to find out / list all events set at system level or session level, following steps is needed

1. Run SQL*Plus and connect to database as sysdba

The oradebug command will be run in SQL*Plus.

2. Identify the session, this step is only needed if you want to list session level events set in the sessions other than current session

Find out the session's Oracle process identifier (PID), Operating system process identifier (SPID) and Oracle process name (PNAME) with following SQL,

select p.pid, p.spid, p.pname, s.username, s.machine, s.program
  from v$process p, v$session s 
 where p.addr = s.paddr;

Sample output

PID SPID   PNAME  USERNAME   MACHINE         PROGRAM
--- ------ ------ ---------- --------------- ----------------------
 45 24432         USER01     workstation01   sqlplus.exe

The the sample output shows that USER01 is running sqlplus.exe on machine workstation01, which session PID is 45 and SPID is 24432.

3. Attached to the identified session

A. If you want to list system level events or session level events in current session, run oradebug command ,

   oradebug setmypid

For example

sys@orcl> oradebug setmypid
Statement processed.

B. If you want to list events set in the session other than current session, run one of following oradebug commands,

   oradebug setorapid      <PID>

   oradebug setospid       <SPID>

   oradebug setorapname    <PNAME>

For example, attaching the session found in step 2

sys@sys@orcl> oradebug setorapid 45
Oracle pid: 45, Unix process pid: 24432, image: oracle@dbhost01

Or

sys@sys@orcl> oradebug setospid 24432
Oracle pid: 45, Unix process pid: 24432, image: oracle@dbhost01

Here, oradebug setorapname cannot be used to attach the session because the process of the session does not have PNAME (PNAME is null). 

4. List the events

A. List events set at system level, run command

   oradebug eventdump system   -- 10g or newer

   oradebug dump events 4      -- 8i/9i

Sample output

sys@orcl> oradebug eventdump system
1652 trace name errorstack

The trace is enabled for ORA-1652 at system level.

B. List events set at session/process level, run command

   oradebug eventdump session   -- 10g or newer

   oradebug dump events 1          -- 8i/9i for session

   oradebug eventdump process  -- 10g or newer

   oradebug dump events 2          -- 8i/9i for process

Sample output

sys@orcl> oradebug eventdump process
1652 trace name errorstack
sys@orcl> oradebug eventdump session
sql_trace level=12
1652 trace name errorstack

The trace is set for ORA-1652 at both process and session level. And sql_trace is set at session level, the trace level is 12.