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.

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.

A trace events continuously writes information while the session is running.

The most famous trace event is probably the 10046 event.

For example event 10262

There are three ways to set an event:

• through the event init parameter.
• alter session set event
• oradebug (orambx on VMS)

The following statement unsets event 10053;

alter session set events '10053 trace name context off';