How To List All The Named Events Set For A Database [ID 436036.1]
Applies to:
Oracle Server - Enterprise Edition - Version: 8.0.3.0 to 11.2.0.1.0 - Release: 8.0.3 to 11.2
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 8.0.3.0 to 11.2.0.1.0
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.
Solution
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;
PID SPID USERNAME
---------- ------------ ------------------------------
2 594006
3 536586
4 598028
5 176348
6 606232
7 471092
8 335978
9 380998
10 569512
11 241800
12 282644
PID SPID USERNAME
---------- ------------ ------------------------------
15 512214 SYS
16 401526
17 426118 SYSTEM
18 331802 TEST
22 266414
25 360504
2. Attach to the desired session
SQL> connect / as sysdba
SQL> oradebug setorapid <pid>
--OR--
SQL> oradebug setospid <spid>
...
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>
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 <level>
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(<level>)';
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.
SQL> alter system set events '942 trace name errorstack level 3';
Session altered.
After the event is set we dump the events information
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump events 4;
Statement processed.
SQL> oradebug tracefile_name
/u01/oracle/product/9.2.0/admin/ORCL/udump/ORCL_ora_2620.trc
The following information will be dumped in the in the trace file ORCL_ora_2620.trc.
1. *** 2007-06-13 20:12:11.421
2. Dump event group for level SYSTEM
3. TC Addr Evt#(b10) Action TR Addr Arm Life
4. 78B6584 942 1 78b65c4 0 0
5. TR Name TR level TR address TR arm TR life TR type
6. ERRORSTACK 3 0 1 2 1961960
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:
SQL> select p.pid, p.spid, s.username
from v$process p, v$session s
where p.addr = s.paddr;
For example:
PID SPID USERNAME
---- ------- ---------
15 28390 SCOTT
Attach to the above identified session:
SQL> connect / as sysdba
SQL> oradebug setorapid 15
--OR--
SQL> oradebug setospid 28390
Unix process pid: 32493, image: oracle@jschelfh-pc (TNS V1-V3)
Get the event information:
SQL> oradebug eventdump session
942 trace name ERRORSTACK level 3
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:
SQL> alter system set events '942 trace name errorstack level 3';
Session altered.
SQL> oradebug tracefile_name
/oracle/admin/L111/udump/l112_ora_32493.trc
The following information will be dumped in the in the trace file l112_ora_32493.trc
*** ACTION NAME:() 2009-03-06 12:48:52.533
Dumping Event (group=SYSTEM)
942 trace name errorstack level 3
Comparison with DBMS_SYSTEM.READ_EV
We can also use the prodecure READ_EV from the package DBMS_SYSTEM to get the events set in the database. But the main difference between the usage would be that the DBMS_SYSTEM.READ_EV will generate information only on the numeric events like 10235, 10046, etc., that are set in the database. But, checking the events with the help of this named event "EVENTS", we can check for both the named and numerical events that are set. Also we can get additional information regarding each event that is set, from the trace file generated.
For example, ERRORSTACK, HEAPDUMP in following format cannot be checked through DBMS_SYSTEM.READ_EV,
ALTER SYSTEM SET EVENTS '4031 TRACE NAME HEAPDUMP LEVEL 2';
ALTER SYSTEM SET EVENTS '942 TRACE NAME ERRORSTACK LEVEL 3';
but can be checked through the named event 'EVENTS'.
How To List All The Named Events Set For A Database
最新推荐文章于 2024-08-29 14:49:59 发布