ITPub博客

首页 > 数据库 > Oracle > 开启/查看 sql trace

开启/查看 sql trace

原创 Oracle 作者:westzq1984 时间:2014-03-11 19:20:06 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,最好是用其对应的方法,或者是同一个类别的方法。否则可能存在无法关闭的现象

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8242091/viewspace-1107799/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-04-06

  • 博文量
    251
  • 访问量
    953069