How-to trace oracle sessions?

There are lots of methods to trace oracle sessions.

1. alter session set events ‘10046 trace name context forever,level 12’;

alter session set tracefile_identifier=’10046′;
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;

Start trace:

alter session set events ‘10046 trace name context forever,level 12’;

Stop trace:

alter session set events ‘10046 trace name context off’;

Note: level can be 4,8,12, only trace your own session
 2. Using package DBMS_MONITOR
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27, serial_num => 60,waits => TRUE, binds => TRUE);
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 27, serial_num => 60);
Trace a module:
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(
     service_name=>'vasont.world', module_name=>'VasontU.exe',
     action_name=>DBMS_MONITOR.ALL_ACTIONS,waits=>TRUE,
     binds=>TRUE,instance_name=>NULL);
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(
  service_name=>'vasont.world',module_name=>'VasontU.exe');
Note: not available before 10g, can trace any session 

3. Using package DBMS_SESSION

EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE,
     binds => TRUE);
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE();
Note: only trace your own session
 4. Using package DBMS_SYSTEM
execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>507,
     serial#=>4957,sql_trace=>TRUE);
execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>507,
     serial#=>4957,sql_trace=>FALSE);
 Note: available in 8i/9i/10g, can trace any session, package is wrapped.

5. oradebug
Trace session sid=58;select p.PID,p.SPID from v$process p,v$session s where s.paddr = p.addr and s.sid = 58;
PID SPID
———- ———
32 12943
connect / as sysdba
oradebug setospid 12943 //or   oradebug setorapid 32
oradebug unlimit
oradebug event 10046 trace name context forever,level 12

Stop trace:
oradebug event 10046 trace name context off
Note: available in 8i/9i/10g, can trace any session 
6. Using DBMS_SUPPORT 
exec DBMS_SUPPORT.START_TRACE_IN_SESSION(&SID, 
     waits=>true, binds=>true );
exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION( &SID , null );
NOTE: need to install before use
SQL> connect / AS SYSDBA
SQL> @?\rdbms\admin\dbmssupp.sql
SQL> GRANT execute ON dbms_support TO schema_owner;
SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support; 
7. Using trcsess
trcsess  [output=output_file_name]
         [session=session_id]
         [clientid=client_id]
         [service=service_name]        
         [action=action_name]        
         [module=module_name]        
         [trace_files] 
Trace a module:
trcsess service=vasont.world module=VasontU.exe trc.log

8. Tracing whole system 
alter system set events '10046 trace name context forever,level 12';
or
event="10046 trace name context forever,level 12"
Stop system wider trace:
alter system set events '10046 trace name context off'; 
9. Using trigger to start traces
There may be some situations where it is necessary to trace
 the activity of a specific user. In this case a logon trigger
 could be used.
An example is provided below:
  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;
  /
 
Last step, after trace processing, using tkprof
tkprof vasont_ora_22103.trc vasont_ora_22103.trc.log sys=no
   waits=yes sort=(prscnt, execnt)
Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

9 Responses to How-to trace oracle sessions?

  1. Pirsey says:

    Hey, cool tips. Perhaps I’ll buy a glass of beer to the man from that forum who told me to visit your site 🙂

  2. neworacledba says:

    this is a great post and most needed for daily operational task

  3. alan says:

    Nice reference. Thank you.

  4. Good effort to provide such wonderful tip of session tracing enable in all versions of Oracle. Great work. Keep it up.

  5. Alex says:

    I agree with aforesaid, this is short, comprehensive and, what is most important, easy for reading and understanding.

  6. joseph says:

    Thanks Tinku head

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: