Database Reference
In-Depth Information
SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS SQL_TRACE_PLAN_STATS
--------- --------------- --------------- --------------------
ENABLED TRUE FALSE FIRST EXEC
The following PL/SQL call disables SQL trace for the session identified by ID 127 and serial number 29:
dbms_monitor.session_trace_disable(session_id => 127,
serial_num => 29)
Be aware that both parameters have default values. If they aren't specified, SQL trace is disabled for the session
executing the PL/SQL call.
If Real Application Clusters is used, the
session_trace_enable
and
session_trace_disable
procedures have to
be executed on the database instance where the session resides.
Client Level
To enable and disable SQL trace for a client, the
dbms_monitor
package provides the
client_id_trace_enable
and
client_id_trace_disable
procedures, respectively. Naturally, these procedures can be used only if the session
attribute client identifier is set.
The following PL/SQL call enables SQL trace at level 12 for all sessions having the client identifier specified as a
parameter:
dbms_monitor.client_id_trace_enable(client_id => 'helicon.antognini.ch',
waits => TRUE,
binds => TRUE,
plan_stat => 'first_execution')
The
client_id
parameter has no default value and is case sensitive.
Because the setting is stored in the data dictionary, not only does it persist across database instance restarts, but
in addition, in a RAC environment, it applies to all database instances.
The
dba_enabled_traces
and, in a 12.1 multitenant environment,
cdb_enabled_traces
views display which
client identifier SQL trace has been enabled for, and which parameters were used to enable it, through the
client_id_trace_enable
procedure. For example, after enabling SQL trace with the previous PL/SQL call, the
following information is given:
SQL> SELECT primary_id AS client_id, waits, binds, plan_stats
2 FROM dba_enabled_traces
3 WHERE trace_type = 'CLIENT_ID';
CLIENT_ID WAITS BINDS PLAN_STATS
-------------------- ----- ----- ----------
helicon.antognini.ch TRUE TRUE FIRST_EXEC
The following PL/SQL call disables SQL trace for all sessions having the client identifier specified as a parameter:
dbms_monitor.client_id_trace_disable(client_id => 'helicon.antognini.ch')
The
client_id_trace_disable
procedure removes the corresponding information added to the data dictionary
through the
client_id_trace_enable
procedure. The
client_id
parameter has no default value.
