René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

diagnostic events [Oracle]

Oracle has a few diagnostic events that can be set. Depending on the event, Oracle will produce a trace file, a dump file or change Oracle's behaviour.
For example event 10046 produces a trace files with SQL statements, event 10053 will trace the work of the CBO, event 10104 dumps join statistics and event 31098 monitors object types and tables that are created during dbms_xmlschema.registerSchema.
Diagnostic events are not the same as wait events.

Event types

Dump events

There are two dump event types: those that immediatly dump information (such as systemstate, errorstack, controlf, file_hdrs and redohdr) and thos that only dump if an error occurs.
The information dumped is written to a trace file.

Trace events

A trace events continuously writes information while the session is running.
The most famous trace event is probably the 10046 event.

Change the behaviour

For example event 10262

Setting events

There are three ways to set an event:

Unsetting (disabling) events

The following statement unsets event 10053;
alter session set events '10053 trace name context off';