ORACLE列出已經開啟的event set[ID 436036.1]

Applies to:

Goal

Oracle Internal Events, both named event (eg: ERRORSTACK) as numeric events (eg 10046) can be set at the session level. This document outlines how DataBase Administrators can verify which events have been set in a specific session or in the entire system.

Fix

This can be achieved through the ORADEBUG functionality. The exact syntax to use depends on the Oracle version you are using. 

If you want to retrieve the events set in a specific session, then use the following oradebug commands to identify the session:

1. identify the session  

   SQL> select p.pid, p.spid, s.username  
        from v$process p, v$session s  
        where p.addr = s.paddr;  


2. Attach to the desired session  

   SQL> connect / as sysdba
   SQL> oradebug setorapid  
   - OR -  
   SQL> oradebug setospid  
   ...

If you want to retrieve the events set in your current SESSION or at the SYSTEM level, you can use the following commands:

SQL> connect / as sysdba 
SQL> oradebug setmypid 
...

Oracle 8i / 9i 

SQL> oradebug dump events  

Level 1 - Dump session group's event settings  
Level 2 - Dump process group's event settings  
Level 4 - Dump system group's event settings (Ie the instance wide events) 

Oracle 10g / 11g and above

SQL> oradebug eventdump  

session - Dump session group's event settings  
process - Dump process group's event settings  
system  - Dump system group's event settings(Ie the instance wide events)

Note: level needs to be specified in lowercase !

             -- OR --

SQL> alter session set events 'immediate eventdump()'; 

SESSION - Dump session group's event settings 
PROCESS - Dump process group's event settings   
SYSTEM  - Dump system group's event settings(Ie the instance wide events)

Example 1:  Oracle 9i - system events

This example illustrates how to retrieve the events set at the system level in an Oracle 9i database.

After the event is set we dump the events information

The following information will be dumped in the in the trace file ORCL_ora_2620.trc.

Notice the line 2 "Dump event group for level SYSTEM" which tells you which level was used to dump the information. In our case level 1 was used to dump the session information.

In line 4 the second column value tells us the event that was set, in our case 942.

In line 6 the first column tells us the name of the event, in our case the named event "ERRORSTACK" appears. On the same line check the second column, the determines the level at which the event was set, in our case "3"

Hence from the above information was can conclude that the event "942 trace name ERRORSTACK level 3" was set at the system level

Example 2: Oracle 11g - events set at session user SCOTT

This example illustrates how to retrieve the events user SCOTT has set at his session in an Oracle 11g database: 

Attach to the above identified session:

Get the event information:

Example 3: Oracle 11g - events set at your current session 

This example illustrates how to retrieve the events set in your current session environment in an Oracle 11g database:

The following information will be dumped in the in the trace file l112_ora_32493.trc