Database Reference
In-Depth Information
The
serv_mod_act_trace_disable
procedure removes the corresponding information added to the data
dictionary through the
serv_mod_act_trace_enable
procedure. All parameters have the same default values and
behavior as for the
serv_mod_act_trace_enable
procedure.
Database Level
For enabling and disabling SQL trace for all sessions that connect to a database (except those created by background
processes), the
dbms_monitor
package provides the
database_trace_enable
and
database_trace_disable
procedures, respectively.
The following PL/SQL call enables SQL trace at level 12 for a single database instance:
dbms_monitor.database_trace_enable(waits => TRUE,
binds => TRUE,
instance_name => 'DBM11203',
plan_stat => 'first_execution')
All parameters have default values. In the case of Real Application Clusters, by using the
instance_name
parameter, restricting the tracing to a single database instance is possible. The value for a specific database instance
is available in the
instance_name
column of the
gv$instance
view. If the
instance_name
parameter is set to
NULL
,
which is also the default value, SQL trace is enabled for all database instances. Again, note that the
instance_name
parameter is case sensitive.
Because the setting is stored in the data dictionary, it persists across database instance restarts.
As for SQL trace at the client and component levels, the
dba_enabled_traces
and, in a 12.1 multitenant
environment,
cdb_enabled_traces
views display which database instance SQL trace has been enabled for, and which
parameters it's been enabled with, through the
database_trace_enable
procedure. For example, after enabling SQL
trace with the previous PL/SQL call, the following information is given:
SQL> SELECT instance_name, waits, binds, plan_stats
2 FROM dba_enabled_traces
3 WHERE trace_type = 'DATABASE';
INSTANCE_NAME WAITS BINDS PLAN_STATS
------------- ----- ----- ----------
DBM11203 TRUE TRUE FIRST_EXEC
The following PL/SQL call disables SQL trace for a database by removing from the database dictionary the
corresponding information added through the
database_trace_enable
procedure:
dbms_monitor.database_trace_disable(instance_name => 'DBM11203')
Be aware that it doesn't disable SQL trace enabled at the session, client, or component level. If the
instance_name
parameter is set to
NULL
, which is also the default value, SQL trace is disabled for all database instances.
Enabling SQL Trace with DBMS_SESSION
As pointed out in the previous section, by default the access to the
dbms_monitor
package is restricted. If you want
to enable or disable SQL trace for the session you're connected with, but neither have the privilege to execute the
dbms_monitor
package nor want to use the
ALTER SESSION
statement (for example, because its syntax isn't easy to
remember), you can use the
dbms_session
package.
