開啟/檢視 sql trace

westzq1984發表於2014-03-11
SQL
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，最好是用其對應的方法，或者是同一個類別的方法。否則可能存在無法關閉的現象

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-1107799/，如需轉載，請註明出處，否則將追究法律責任。

相關文章