开启/查看 sql trace
开启/查看 sql trace
1.设置event开启
<== 重启/连接断开后失效，当然，如果是设置event参数，那重启后肯定还保持
alter session set events '10046 trace name context forever,level 8';
oradebug session_event 10046 trace name context forever, level 12;
alter system set events '10046 trace name context forever,level 8';
SQL> oradebug setospid 20055
Oracle pid: 22, Unix process pid: 20055, image: oracle@zhangqiaoc (TNS V1-V3)
SQL> oradebug eventdump session
10046 trace name CONTEXT level 8, forever
SQL> oradebug eventdump system
10046 trace name CONTEXT level 8, forever
2.通过sql_trace开启
<== 重启/连接断开后失效，当然，如果把参数写入了spfile，那重启后肯定还保持
alter session set sql_trace=true;
exec dbms_system.set_bool_param_in_session(148,52,'sql_trace',true);
SQL> oradebug setospid 20096;
Oracle pid: 18, Unix process pid: 20096, image: oracle@zhangqiaoc (TNS V1-V3)
SQL> oradebug DUMP MODIFIED_PARAMETERS 0
Statement processed.
<== trace文件中
*** 2014-03-11 14:45:43.959
Received ORADEBUG command 'DUMP MODIFIED_PARAMETERS 0' from process Unix process pid: 20101, image:
DYNAMICALLY MODIFIED PARAMETERS:
sql_trace = TRUE
3.dbms包设置
<== 会话级别是断开就失效，但是其他级别，是一直生效，直到手工关闭
exec dbms_support.start_trace_in_session(148,52,true,true);
exec dbms_support.start_trace(true,true); <== 开启自己
exec dbms_system.set_sql_trace_in_session(148,52,true);
exec dbms_system.set_ev( 148,52,10046,12,'');
exec dbms_monitor.session_trace_enable(148,52,true,true);
exec dbms_monitor.serv_mod_act_trace_enable(service_name,module_name,action_name,true,true,instance_name);
exec dbms_monitor.client_id_trace_enable(client_id,true,true);
exec dbms_monitor.database_trace_enable(true,true,instance_name)
SQL> select sid,SQL_TRACE from v$session where sql_trace='ENABLED';
SID SQL_TRAC
---------- --------
148 ENABLED
SQL> select * from DBA_ENABLED_TRACES;
TRACE_TYPE
---------------------
PRIMARY_ID
----------------------------------------------------------------
QUALIFIER_ID1
------------------------------------------------
QUALIFIER_ID2 WAITS BINDS INSTANCE_NAME
-------------------------------- ----- ----- ----------------
DATABASE
TRUE FALSE
SQL> select * from wri$_tracing_enabled;
TRACE_TYPE PRIMARY_ID
---------- ----------------------------------------------------------------
QUALIFIER_ID1
------------------------------------------------
QUALIFIER_ID2 INSTANCE_NAME FLAGS
-------------------------------- ---------------- ----------
4 hrdb
w3wp.exe
8
4 hrdb
WorkflowService.exe
8
关闭sql trace，最好是用其对应的方法，或者是同一个类别的方法。否则可能存在无法关闭的现象
