DBA_ENABLED_TRACES

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	VARCHAR2(64)		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级别的跟踪并没有进行记录。