sql_trace及10046

=================================参数===============================================================
SQL> show parameter timed_statistics


NAME             TYPE             VALUE
------------------------------------ --------------------------------- ------------------------------
timed_statistics         boolean             TRUE
SQL> show parameter max_dump_file


NAME             TYPE             VALUE
------------------------------------ --------------------------------- ------------------------------
max_dump_file_size         string             unlimited


===============================SQL TRACE=============================================================
全局跟踪：pfile/spfile中设置：sql_trace=true
SQL> alter system set sql_trace=true;


System altered.


SQL> show parameter sql_trace


NAME             TYPE             VALUE
------------------------------------ --------------------------------- ------------------------------
sql_trace           boolean             TRUE
SQL> alter system set sql_trace=false;


System altered.


SQL> show parameter sql_trace


NAME             TYPE             VALUE
------------------------------------ --------------------------------- ------------------------------
sql_trace           boolean             FALSE
SQL> 
-----------------------------------------------------------------------------------------------------
跟踪当前会话：
SQL> alter session set sql_trace=true;


Session altered.


SQL> create table t1 as select * from scott.emp;


Table created.


SQL> alter session set sql_trace=false;


Session altered.
-----------------------------------------------------------------------------------------------------
跟踪其他会话：dbms_system包完成操作
SQL> desc dbms_system
.......
PROCEDURE SET_SQL_TRACE_IN_SESSION
 Argument Name      Type      In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SID        NUMBER      IN
 SERIAL#      NUMBER      IN
 SQL_TRACE      BOOLEAN     IN
......


SQL> select sid,serial#,username from v$session where username='SCOTT';


       SID    SERIAL# USERNAME
---------- ---------- ------------------------------------------------------------------------------------------
  14    217 SCOTT
       203   2659 SCOTT


SQL> exec dbms_system.set_sql_trace_in_session(14,217,true);


PL/SQL procedure successfully completed.


SQL> 
SQL> exec dbms_system.set_sql_trace_in_session(14,217,false);


PL/SQL procedure successfully completed.


SQL> 
======================================10046==============================================
10046 事件可以设置以下4 个级别：
 Level 1：启用标准的SQL_TRACE 功能，等价于SQL_TRACE。
 Level 4：等价于Level 1 + 绑定值（bind values）。
 Level 8：等价于Level 1 + 等待事件跟踪。
 Level 12：等价于Level 1 + Level 4 + Level 8。
-------------------------------------------------------------
全局设置10046：event='10046 trance name context forever,level 12'


SQL> alter system set events '10046 trace name context forever,level 12';


System altered.


SQL> alter system set events '10046 trace name context forever,level 0';


System altered.


SQL> alter system set events '10046 trace name context off';


System altered.
--------------------------------------------------------------------------------------------------
跟踪当前会话：
SQL> alter session set events '10046 trace name context forever,level 8';


Session altered.


SQL> alter session set events '10046 trace name context forever,level 12';


Session altered.


SQL> alter session set events '10046 trace name context forever,level 4';


Session altered.


SQL> alter session set events '10046 trace name context forever,level 1';


Session altered.


SQL> alter session set events '10046 trace name context off';


Session altered.
-------------------------------------------------------------------------------------------
跟踪其他会话：dbms_system.set_ev


SQL> desc dbms_system
........
PROCEDURE SET_EV
 Argument Name Type In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SI BINARY_INTEGER IN
 SE BINARY_INTEGER IN
 EV BINARY_INTEGER IN
 LE BINARY_INTEGER IN
 NM VARCHAR2 IN
......
SQL> select sid,serial#,username from v$session where username='SCOTT';


       SID    SERIAL# USERNAME
---------- ---------- ------------------------------------------------------------------------------------------
       394  157 SCOTT


SQL> exec dbms_system.set_ev(394,157,10046,12,'SCOTT');             --启动跟踪


PL/SQL procedure successfully completed.


SQL> exec dbms_system.set_ev(394,157,10046,0,'SCOTT');              --结束跟踪


PL/SQL procedure successfully completed.
===============================获取跟踪文件============================


select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
       p.spid || '.trc' trace_file_name
  from (select p.spid
          from sys.v$mystat m, sys.v$session s, sys.v$process p
         where m.statistic# = 1
           and s.sid = m.sid
           and p.addr = s.paddr) p,
       (select t.instance
          from sys.v$thread t, sys.v$parameter v
         where v.name = 'thread'
           and (v.value = 0 or t.thread# = to_number(v.value))) i,
       (select value from sys.v$parameter where name = 'user_dump_dest') d
=======================读取当前session的设置参数===============================================
set feedback off
set serveroutput on


declare
  event_level number;
begin
  for event_number in 10000 .. 10999 loop
    sys.dbms_system.read_ev(event_number, event_level);
    if (event_level > 0) then
      sys.dbms_output.put_line('Event ' || to_char(event_number) ||
                               ' is set at level ' || to_char(event_level));
    end if;
  end loop;
end;

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

转载于:http://blog.itpub.net/29612373/viewspace-2103738/