How to Trace Oracle Sessions

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)