How To List All The Named Events Set For A Database (Doc ID 436036.1)
In this Document
Goal |
Solution |
References |
Applies to:
Oracle Database - Enterprise Edition - Version 8.0.3.0 and laterInformation in this document applies to any platform.
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.
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;
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.
Session altered.
After the event is set we dump the events information
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.
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:
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> oradebug setorapid 15
--OR--
SQL> oradebug setospid 28390
Unix process pid: 32493, image: oracle@jschelfh-pc (TNS V1-V3)
Get the event information:
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:
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
Dumping Event (group=SYSTEM)
942 trace name errorstack level 3
References
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17252115/viewspace-1135591/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- How To List All The Named Events Set For A Database [ID 436036.1]Database
- How to Duplicate a Database in NOARCHIVELOG mode (Doc ID 275480.1)DatabaseHive
- How To Size the Database Smart Flash Cache (Doc ID 1317950.1)Database
- How to Collect Diagnostics for Database Hanging Issues (Doc ID 452358.1)Database
- List all javascript events wired up on a page using jqueryJavaScriptjQuery
- 【健康檢查】How to Perform a Health Check on the Database (Doc ID 122669.1)ORMDatabase
- 11.2.0.4 Patch Set - List of Bug Fixes by Problem Type (Doc ID 1562142.1)
- List of Oracle Database Patch Set Updates (PSU)OracleDatabase
- ORACLE列出已經開啟的event set[ID 436036.1]Oracle
- How to Prepare Storage for ASM (Doc ID 452924.1)ASM
- oracle set eventsOracle
- ALTER SESSION SET EVENTSSession
- alter session|system set eventsSession
- * Master Note: Database Performance Overview (Doc ID 402983.1)ASTDatabaseORMView
- Troubleshooting Database Hang Issues (Doc ID 1378583.1)Database
- How to Disable or set SELinux to Permissive mode [ID 457458.1]Linux
- How to free space from an ASM diskgroup? (Doc ID 1553744.1)ASM
- Oracle跟蹤事件 -- set eventsOracle事件
- How to Quiesce a DatabaseUIDatabase
- alter system set event和set events的區別
- Oracle10G RMAN Database Duplication (Doc ID 259694.1)OracleDatabase
- How to Start 11gR2 Grid Infrastrucure in Exclusive Mode (Doc ID 1364971.1)AST
- How to Resolve Invalid Objects in a Database [ID 158185.1]ObjectDatabase
- How to Monitor and tune Open and Cached Cursors (Doc ID 1430255.1)
- alter session set events /Oracle跟蹤SessionOracle
- [zt]Oracle跟蹤事件 - set eventsOracle事件
- Oracle跟蹤事件:set events 整理Oracle事件
- [20190522]How to get dump or list parameters set at session level.txtSession
- Performing duplicate database with ASM/OMF/RMAN (Doc ID 340848.1)ORMDatabaseASM
- Information On Installed Database Components and Schemas (Doc ID 472937.1)ORMDatabase
- Can Tapes Be Used For RMAN Convert Database (Doc ID 563816.1)Database
- Recover database after disk loss (Doc ID 230829.1)Database
- RMAN10g: backup copy of database (Doc ID 266980.1)Database
- How to Perform a Healthcheck on the DatabaseORMDatabase
- How to enable the flashback database:Database
- How a Database Is Mounted (293)Database
- Java List/Set/MapJava
- List、Set、Queue、Map