dba_enabled_tracesdba_enabled_traces displays information about enabled sql traces.
columndatatypenulldescription
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_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_id1 qualifier_ 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_id1 qualifier_ waits binds plan_stats instance_name--------------------- -------------------- -------------------- ---------- ----- ----- ---------- ----------------client_id easy true false first_execservice service1 true false first_exec
可以看出，dba_enabled_traces对于session级别的跟踪并没有进行记录。