All About Events

WHAT IS A DATABASE EVENT AND HOW IS ONE SET?

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 init.ora parameter file. For example:

EVENT='1401 trace name errorstack, level 12'

or, by issuing an ALTER SESSION SET EVENTS command. For example:

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.

WHAT DATABASE EVENTS CAN BE SET?

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
  • This 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 is to be traced.
1401 trace name errorstack, level 12 
1401 trace name errorstack, level 4 
1401 trace name processstate
  • This shows where in the code Oracle gets a deadlock (ORA-0060), and may help to diagnose the problem.
60 trace name errorstack level 10

The following list of events are examples only. They might be version specific, so check with 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
  • This protects memory cursors.
10049 trace name context forever, level 2
  • This performs data block checks.
10210 trace name context forever, level 2
  • This performs index block checks.
10211 trace name context forever, level 2
  • This performs memory heap checks.
10235 trace name context forever, level 1
  • This allows a 300 bytes memory leak for connections.
10262 trace name context forever, level 300
  • NOTE: The Unix oerr command can be used to get the description of an event. To get event details, from the command prompt, type:
oerr ora <event number>
© copyright 2001-2014 ABCdba.com | all rights reserved