• Welcome
• Projects
• Presentations
• Research

DBMS_MONITOR.DATABASE_TRACE_ENABLE Procedure

This procedure, which was introduced in Oracle 10.2, enables event 10046 trace for the entire database or in a RAC environment for the specified instance

There are two optional Boolean parameters, WAITS and BINDS, both of which default to FALSE.

There is a third optional parameter, INSTANCE_NAME, which defaults to NULL.

If the INSTANCE_NAME is NULL then trace is enabled for all sessions in the database

If the INSTANCE_NAME is NOT NULL then trace is enabled for all sessions in the specified instance.

This functionality was not previously available in a clustered database environment.

In Oracle 11.1 and above PLAN_STAT specifies the dump frequency for row source (STAT) statistics. Values can be:

• NEVER
• FIRST EXECUTION (default)
• ALL_EXECUTIONS

PLAN_STAT is useful when individual statement executions are affected by data cardinality / selectivity

In Oracle 10.2.0.1 (on Linux at least), take care when using this procedure in a RAC environment. If you use this procedure to enable instance-wide trace, you may not be able to disable it again. This appears to be a bug and can result in the rapid generation of significant amounts of trace. The only workaround appears to be to update the base table in the data dictionary (SYS.WRI$_TRACING_ENABLED) which will not be supported by Oracle.

For example to enable event 10046, level 1 trace for all sessions in the database use:

EXECUTE dbms_monitor.database_trace_enable

In a single-instance database, this is similar to:

ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 1';

To enable event 10046, level 4 trace (bind variables) for all sessions in the database use:

EXECUTE dbms_monitor.database_trace_enable (binds=>true);

In a single-instance database, this is similar to:

ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 4';

To enable event 10046, level 8 trace (waits) for all sessions in the database use:

EXECUTE dbms_monitor.database_trace_enable (waits=>true);

In a single-instance database, this is similar to:

ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 8';

To enable event 10046, level 1 trace for all sessions in instance RAC1 use:

EXECUTE dbms_monitor.database_trace_enable (instance_name=>'RAC1');

In the current instance this is equivalent to:

ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 1';

To enable event 10046, level 4 trace (bind variables) for all sessions in instance 'RAC1' use:

EXECUTE dbms_monitor.database_trace_enable (instance_name=>'RAC1',binds=>true);

In the current instance this is equivalent to:

ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 4';

To enable event 10046, level 8 trace (waits) for all sessions in instance 'RAC1' use:

EXECUTE dbms_monitor.database_trace_enable (instance_name=>'RAC1',waits=>true);

In the current instance this is equivalent to:

ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 8';

The current state of database and instance trace is reported in the data dictionary view DBA_ENABLED_TRACES. This contains the following columns:

The DBA_ENABLED_TRACES view is based on the underlying data dictionary table SYS.WRI$_TRACING_ENABLED.