LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon