DBA_ENABLED_TRACES

来源：UU财经

DBA_ENABLED_TRACES DBA_ENABLED_TRACES displays information about enabled SQL traces. Column Datatype NULL Description TRACE_TYPE VARCHAR2(21) Type of the trace: CLIENT_ID SERVICE SERVICE_MODULE SERVICE_MODULE_ACTION DATABASE PRIMARY_ID VAR

DBA_ENABLED_TRACES

DBA_ENABLED_TRACES displays information about enabled SQL traces.

Column	Datatype	Description
TRACE_TYPE	VARCHAR2(21)	Type of the trace: CLIENT_ID SERVICE SERVICE_MODULE SERVICE_MODULE_ACTION DATABASE
PRIMARY_ID	VARCHAR2()	Primary qualifier (specific client identifier or service name)
QUALIFIER_ID1	VARCHAR2(48)	Secondary qualifier (specific module name)
QUALIFIER_ID2	VARCHAR2(32)	Additional qualifier (specific action name)
WAITS	VARCHAR2(5)	Indicates whether waits are traced (TRUE) or not (FALSE)
BINDS	VARCHAR2(5)	Indicates whether binds are traced (TRUE) or not (FALSE)
PLAN_STATS	VARCHAR2(10)	Frequency at which row source statistics are dumped in the trace files for each cursor: never first_execution all_executions
INSTANCE_NAME	VARCHAR2(16)	Instance name for tracing restricted to named instances

查看sql trace开启的情况。

SQL> select * from dba_enabled_traces;

未选定行

SQL> BEGIN dbms_monitor.client_id_trace_enable(client_id=>'easy',waits=>true,binds=>false); END;
 2 /

PL/SQL 过程已成功完成。

SQL> select * from dba_enabled_traces;

TRACE_TYPE	 PRIMARY_ID	 QUALIFIER_ID1	QUALIFIER_ WAITS BINDS PLAN_STATS INSTANCE_NAME
--------------------- -------------------- -------------------- ---------- ----- ----- ---------- ----------------
CLIENT_ID	 easy	 TRUE FALSE FIRST_EXEC	

SQL> exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'service1',waits=>true,binds=>false);

PL/SQL 过程已成功完成。

SQL> select * from dba_enabled_traces;

TRACE_TYPE	 PRIMARY_ID	 QUALIFIER_ID1	QUALIFIER_ WAITS BINDS PLAN_STATS INSTANCE_NAME
--------------------- -------------------- -------------------- ---------- ----- ----- ---------- ----------------
CLIENT_ID	 easy	 TRUE FALSE FIRST_EXEC
SERVICE 	 service1	 TRUE FALSE FIRST_EXEC

SQL> select sid,serial# from v$session where sid = (select distinct sid from v$mystat);

 SID SERIAL#
---------- ----------
	66	 3

SQL> exec dbms_monitor.session_trace_enable(session_id=>66,serial_num=>3,waits=>true,binds=>false);

PL/SQL 过程已成功完成。

SQL> select * from dba_enabled_traces;

TRACE_TYPE	 PRIMARY_ID	 QUALIFIER_ID1	QUALIFIER_ WAITS BINDS PLAN_STATS INSTANCE_NAME
--------------------- -------------------- -------------------- ---------- ----- ----- ---------- ----------------
CLIENT_ID	 easy	 TRUE FALSE FIRST_EXEC
SERVICE 	 service1	 TRUE FALSE FIRST_EXEC

SQL> alter session set sql_trace =true;

会话已更改。

SQL> select * from dba_enabled_traces;

TRACE_TYPE	 PRIMARY_ID	 QUALIFIER_ID1	QUALIFIER_ WAITS BINDS PLAN_STATS INSTANCE_NAME
--------------------- -------------------- -------------------- ---------- ----- ----- ---------- ----------------
CLIENT_ID	 easy	 TRUE FALSE FIRST_EXEC
SERVICE 	 service1	 TRUE FALSE FIRST_EXEC

可以看出，dba_enabled_traces对于session级别的跟踪并没有进行记录。