Trace events

From Oracle FAQ
Jump to: navigation, search

Oracle trace events are useful for debugging the Oracle database server. The following two examples are simply to demonstrate syntax. Refer to later notes on this page for an explanation of what these particular events do.

Events can be activated by either adding them to the SPFILE/INIT.ORA parameter file. E.g.:

event='1401 trace name errorstack, level 12'

... or, by issuing an ALTER SESSION SET EVENTS command: E.g.

alter session set events '10046 trace name context forever, level 4';

The alter session method only affects the user's current session, whereas changes to the INIT.ORA file will affect all sessions once the database has been restarted.

Contents

[edit] Frequently Used Events

The following events are frequently used by DBAs and Oracle Support to diagnose problems:

  • Trace SQL statements and show bind variables in trace output.
10046 trace name context forever, level 4
  • This shows wait events in the SQL trace files:
10046 trace name context forever, level 8
  • This shows both bind variable names and wait events in the SQL trace files:
10046 trace name context forever, level 12
  • Dumps out trace information if an ORA-1401 "inserted value too large for column" error occurs. The 1401 can be replaced by any other Oracle Server error code that you want to trace.
1401 trace name errorstack, level 12
1401 trace name errorstack, level 4
1401 trace name processstate
  • Show where in the code Oracle gets a deadlock (ORA-60), and may help to diagnose the problem.
60 trace name errorstack level 10

[edit] Oracle Support Events

The following list of events are examples only. They might be version specific, please call Oracle before using them:

  • These events prevent database block corruptions:
10210 trace name context forever, level 10
10211 trace name context forever, level 10
10231 trace name context forever, level 10
  • Memory protect cursor:
10049 trace name context forever, level 2
  • Data block check:
10210 trace name context forever, level 2
  • Index block check:
10211 trace name context forever, level 2
  • Memory heap check:
10235 trace name context forever, level 1
  • Allow 300 bytes memory leak for connections
10262 trace name context forever, level 300

[edit] Event descriptions

You can use the Unix oerr command to get the description of an event. For example, type "oerr ora 10053" from the command prompt to get event details:

$ oerr ora 10053
10053, 00000, "CBO Enable optimizer trace"
// *Cause:
// *Action:

[edit] Also see

  • Oradebug, debugging utility that can set trace events.