How to use alter session set events 'immediate trace name systemstate level 10';

1.Session Tracing
   alter session set events '10046 trace name context forever,level 12';
   alter session set events '10046 trace name context off';
   e.g.
   C:\Documents and Settings\zhanglei>sqlplus /nolog
   SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 17 13:57:56 2009
   Copyright (c) 1982, 2005, Oracle. All rights reserved.
   SQL> conn ecc_view/ecc@devdb
   已連線。
   SQL> alter session set events '10046 trace name context forever,level 12';
   會話已更改。
   SQL> select * from dual;
   D
   -
   X
   [oracle@rac1 udump]$ ll -t
   total 340
   -rw-r----- 1 oracle oinstall 2360 Apr 17 13:57 devdb1_ora_5509.trc
   SQL> alter session set events '10046 trace name context off';

2.Tracing a process after it has started

   A.start a session to find the OS process id (spid) for the target session
     select p.PID,p.SPID,s.SID
     from v$process p,v$session s
     where s.paddr = p.addr
     and s.USERNAME = 'ECC_VIEW'
     PID    SPID     SID
     ---    ----     ---
      28 10878 129

   B.trace can be initialised

     1)use spid to trace
       connect / as sysdba
       oradebug setospid "&spid"
       oradebug unlimit
       oradebug event 10046 trace name context forever,level 12
       oradebug event 10046 trace name context off
       e.g.
       SQL> conn / as sysdba
       Connected.
       SQL> oradebug setospid 10878
       Oracle pid: 28, Unix process pid: 10878, image:
       SQL> oradebug unlimit
       Statement processed.
       SQL> oradebug event 10046 trace name context forever,level 12
       Statement processed.
       [oracle@rac1 udump]$ ll -t
       total 352
       -rw-r----- 1 oracle oinstall   987 Apr 17 14:12 devdb1_ora_10878.trc

     2)use pid to trace
       connect / as sysdba
       oradebug setorapid "&pid"
       oradebug unlimit
       oradebug event 10046 trace name context forever,level 12
       oradebug event 10046 trace name context off
       e.g.
       SQL> conn / as sysdba
       Connected.
       SQL> oradebug setorapid 28
       Unix process pid: 10878, image:
       SQL> oradebug unlimit
       Statement processed.
       SQL> oradebug event 10046 trace name context forever,level 12
       Statement processed.
       [oracle@rac1 udump]$ ll -t
       total 360
       -rw-r----- 1 oracle oinstall 1369 Apr 17 14:17 devdb1_ora_10878.trc
       To disable oradebug tracing once tracing is finished:
       SQL> oradebug event 10046 trace name context off

     3)using DBMS_SUPPORT package to trace session
       exec DBMS_SUPPORT.START_TRACE_IN_SESSION( &SID, waits=>true, binds=>true )
       exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION( &SID , null )
       e.g.
       (Doc ID: 377204.1 )
       SQL> conn / as sysdba
       SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/dbmssupp.sql
       Package created.
       Package body created.
       SQL> grant execute on dbms_support to ecc_view;
       Grant succeeded.
       SQL> create public synonym dbms_support for dbms_support;
       Synonym created.
       SQL> exec dbms_support.start_trace_in_session(sid => 129,serial => 2144,waits => true,binds => true);
       PL/SQL procedure successfully completed
       [oracle@rac1 udump]$ ll -t
       total 364
       -rw-r----- 1 oracle oinstall 7857 Apr 17 14:42 devdb1_ora_10878.trc
       SQL> exec dbms_support.stop_trace_in_session(sid => 129, serial => 2144);
       PL/SQL procedure successfully completed

3.Instance wide tracing
     alter system set events '10046 trace name context forever,level 12';
     alter system set events '10046 trace name context off';

     SQL> alter system set events '10046 trace name context forever,level 12';
     System altered
     SQL> conn ecc_view/ecc@devdb
     已連線。
     SQL> select sysdate from dual;
          SYSDATE
         --------------
         17-4月 -09
     SQL> conn / as sysdba
          select p.PID,p.SPID,s.SID,s.SERIAL#
          from v$process p,v$session s
          where s.paddr = p.addr
          and s.USERNAME = 'ECC_VIEW'
          PID    SPID     SID   s.SERIAL#
          ---    ----     ---   ---------
          28 29109   129   2146
     [oracle@rac1 udump]$ ll -t
     total 392
     -rw-r----- 1 oracle oinstall 22918 Apr 17 14:57 devdb1_ora_29109.trc

4.Initialisation parameter setting
   This setting will trace every session in the instance when it is restarted.
   event="10046 trace name context forever,level 12"
   The setting can be disabled by removing the parameter and restarting the instance or by using an alter system command as
   alter system set events '10046 trace name context off';

5.Via a Logon Trigger
   CREATE OR REPLACE TRIGGER SYS.set_trace
   AFTER LOGON ON DATABASE
   WHEN (USER like '&USERNAME')
   DECLARE
       lcommand varchar(200);
   BEGIN
       EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
       EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
       EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
   END set_trace;
   /
   e.g.
    CREATE OR REPLACE TRIGGER SYS.set_trace
    AFTER LOGON ON DATABASE
    WHEN (USER = 'ECC_VIEW')
    DECLARE
        lcommand varchar(200);
    BEGIN
        EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
        EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
        EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
    END set_trace;
    select p.PID,p.SPID,s.SID,s.SERIAL#
    from v$process p,v$session s
    where s.paddr = p.addr
    and s.USERNAME = 'ECC_VIEW'
    [oracle@rac1 udump]$ ll -t
    total 432
    -rw-r----- 1 oracle oinstall 10573 Apr 17 15:26 devdb1_ora_8328.trc

Subject: Recommended Method for Obtaining 10046 trace for Tuning
Doc ID: 376442.1 Type: FAQ
Modified Date : 15-APR-2009 Status: PUBLISHED

how to start '10046 trace name context forever,level 12'

相關文章