开启/查看 sql trace

开启/查看 sql trace

原创 Oracle 作者：westzq1984 时间：2014-03-11 19:20:06 2595 0

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，最好是用其对应的方法，或者是同一个类别的方法。否则可能存在无法关闭的现象