DBA_ENABLED_TRACES

DBA_ENABLED_TRACES displays information about enabled SQL traces.

ColumnDatatypeNULLDescriptionTRACE_TYPEVARCHAR2(21) Type of the trace:

• CLIENT_ID

• SERVICE

• SERVICE_MODULE

• SERVICE_MODULE_ACTION

• DATABASE

PRIMARY_IDVARCHAR2(64) Primary qualifier (specific client identifier or service name)QUALIFIER_ID1VARCHAR2(48) Secondary qualifier (specific module name)QUALIFIER_ID2VARCHAR2(32) Additional qualifier (specific action name)WAITSVARCHAR2(5) Indicates whether waits are traced (TRUE) or not (FALSE)BINDSVARCHAR2(5) Indicates whether binds are traced (TRUE) or not (FALSE)PLAN_STATSVARCHAR2(10) Frequency at which row source statistics are dumped in the trace files for each cursor:

• never

• first_execution

• all_executions

INSTANCE_NAMEVARCHAR2(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_ID1QUALIFIER_ WAITS BINDS PLAN_STATS INSTANCE_NAME--------------------- -------------------- -------------------- ---------- ----- ----- ---------- ----------------CLIENT_ID      easy   TRUE  FALSE FIRST_EXECSQL> 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_ID1QUALIFIER_ WAITS BINDS PLAN_STATS INSTANCE_NAME--------------------- -------------------- -------------------- ---------- ----- ----- ---------- ----------------CLIENT_ID      easy   TRUE  FALSE FIRST_EXECSERVICE       service1   TRUE  FALSE FIRST_EXECSQL> select sid,serial# from v$session where sid = (select distinct sid from v$mystat);       SID    SERIAL#---------- ----------66    3SQL> 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_ID1QUALIFIER_ WAITS BINDS PLAN_STATS INSTANCE_NAME--------------------- -------------------- -------------------- ---------- ----- ----- ---------- ----------------CLIENT_ID      easy   TRUE  FALSE FIRST_EXECSERVICE       service1   TRUE  FALSE FIRST_EXECSQL> alter session set sql_trace =true;会话已更改。SQL> select * from dba_enabled_traces;TRACE_TYPE      PRIMARY_ID   QUALIFIER_ID1QUALIFIER_ WAITS BINDS PLAN_STATS INSTANCE_NAME--------------------- -------------------- -------------------- ---------- ----- ----- ---------- ----------------CLIENT_ID      easy   TRUE  FALSE FIRST_EXECSERVICE       service1   TRUE  FALSE FIRST_EXEC

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