Oracle: enable, disable and list events with oradebug
Enable and disable Oracle events, list active events with oradebug eventdump, and a selection of common event codes.
Oracle events let you turn on diagnostic traces or change instance behaviour. This article shows how to enable and disable them, how to list the active ones with oradebug, and gives a selection of common event codes.
Enable an event
alter system set events '1017 trace name errorstack level 10';
alter system set events '942 trace name errorstack level 3';
Disable an event
alter system set event="10852 trace name context off" scope=spfile;
alter system set events '1652 trace name errorstack off';
alter system set events '1017 trace name errorstack off';
List the active events
The oradebug eventdump command shows the events enabled for a given group:
| Group | Content |
|---|---|
session |
Session group's event settings |
process |
Process group's event settings |
system |
System group's event settings (the instance-wide events) |
SQL> oradebug eventdump [level]
oradebug eventdump session
oradebug eventdump process
oradebug eventdump system
Events set in another user's session
Identify the session's process (here the user SCOTT):
set linesize 300
column pid format 99999
column spid format 99999
column username format a50
select p.pid, p.spid, s.username
from v$process p, v$session s
where p.addr = s.paddr;
PID SPID USERNAME
----- ------ ---------
15 28390 SCOTT
Attach to that session with oradebug, using either the Oracle process id or the operating system process id:
SQL> connect / as sysdba
SQL> oradebug setorapid 15
--OR--
SQL> oradebug setospid 28390
oradebug tracefile_name
Then read the session's active events:
SQL> oradebug eventdump session
942 trace name ERRORSTACK level 3
Events set in your current session
SQL> alter session set events '942 trace name errorstack level 3';
Session altered.
SQL> oradebug tracefile_name
/oracle/admin/ORCL/udump/orcl_ora_32493.trc
ORA-00074 error
If oradebug eventdump session returns ORA-00074: no process has been specified, first select your own process:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug eventdump session;
3171 trace name errorstack level 3
Selected event codes
These codes come from internal event lists and are mostly not officially documented. Use them with care, preferably on a test database.
| Code | Description |
|---|---|
| 10005 | Trace latch operations for debugging |
| 10015 | Undo segment recovery |
| 10029 | Session logon (KSU) |
| 10030 | Session logoff (KSU) |
| 10035 | Parse SQL statement (OPIPRS) |
| 10046 | Enable SQL statement timing |
| 10049 | Protect library cache memory heaps |
| 10053 | CBO: enable optimizer trace |
| 10060 | CBO: enable predicate dump |
| 10092 | CBO: disable hash join |
| 10093 | CBO: force hash joins |
| 10104 | Dump hash join statistics to trace file |
| 10120 | CBO: disable index fast full scan |
| 10200 | Block cleanout |
| 10201 | Consistent read undo application |
| 10210 | Check data block integrity |
| 10211 | Check index block integrity |
| 10231 | Skip corrupted blocks on table scans |
| 10233 | Skip corrupted blocks on index operations |
| 10246 | Print trace of PMON actions to trace file |
| 10270 | Debug shared cursors |
| 10289 | Do block dumps to trace file in hex rather than formatted |
| 10352 | Report direct path statistics |
| 10390 | Trace parallel query slave execution |
| 10393 | Print parallel query statistics |
| 10400 | Turn on system state dumps for shutdown debugging |
| 10500 | Turn on traces for SMON |
| 10704 | Print out information about what enqueues are being obtained |
| 10710 | Trace bitmap index access |
Notes
- A change made with
scope=spfileonly takes effect after the instance is restarted. - The trace file of a session is obtained with
oradebug tracefile_name.