• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to list all the named events set for a database

by admin

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

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 [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

Filed Under: oracle, Oracle 10g, Oracle 11g

Some more articles you might also be interested in …

  1. How To Add New Disk to An Existing Diskgroup on RAC Cluster or Standalone ASM Configuration
  2. Oracle Database – How to Rename a Datafile with Special Characters Created by Mistake
  3. Error: ORA-16810: multiple errors or warnings detected for the database
  4. Transaction Control Statement Examples in SQL
  5. How to Start and Stop Oracle RAC Instances (CDB/PDB)
  6. Oracle RAC : understanding split brain
  7. How to Drop Existing Temporary Tablespace and create new in Oracle 11g
  8. SLES 12: Database Startup Error with ORA-27300 ORA-27301 ORA-27303 While Starting using Srvctl
  9. Real-time SQL Monitoring for Developers – Oracle Database 19c New Feature
  10. How to Verify if a Disk/Partition is in Use by Oracle ASM, was used by Oracle ASM or is never used by Oracle ASM

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright