Pre-requisites for successful tracing
Set TIMED_STATISTICS to TRUE.
Set MAX_DUMP_FILE_SIZE  to a high value or œunlimited.


Tracing the Current Session
1.SQL_TRACE

To start tracing:

ALTER SESSION SET SQL_TRACE = TRUE ;
/* execute your selects to be traced */
To stop tracing:

ALTER SESSION SET SQL_TRACE = FALSE ;

2.10046

To start tracing:

Alter session set events ˜10046 trace name context forever, level 12;
/* execute your selects to be traced */
To stop tracing:

Alter session set events 10046 trace name context off;

3.DBMS_SUPPORT

To start tracing:

exec sys.dbms_support.start_trace ;
/* execute your selects to be traced */
To stop tracing:

exec sys.dbms_support.stop_trace ;

Tracing Another Session
In these examples, we wish to trace a session with SID of 8  Serial# of 226 obtained from V$SESSION.


1.Using "dbms_system.SET_BOOL_PARAM_IN_SESSION"

To start tracing:

exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', TRUE);
/* execute your selects to be traced */
To stop tracing:

exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', FALSE);

2.Using "dbms_system.set_ev"

To start tracing:

exec dbms_system.set_ev(18, 226, 10046, 12, '');
/* execute your selects to be traced */
To stop tracing:

exec dbms_system.set_ev(18, 226, 10046, 0, '');

3.Using "dbms_system.set_sql_trace_in_session"

To start tracing:

exec dbms_system.set_sql_trace_in_session(18,226,TRUE);
/* execute your selects to be traced */
To stop tracing:

exec dbms_system.set_sql_trace_in_session(18,226,FALSE);

4.Using "sys.dbms_monitor"

To start tracing:

exec sys.dbms_monitor.session_trace_enable(session_id=>18,serial_num=>226, waits=>true, binds=>true);
/* execute your selects to be traced */
To stop tracing:

exec sys.dbms_monitor.session_trace_disable(session_id=>18,serial_num=>226);

5.Using Oradebug (as SYS)

To start tracing:

oradebug setospid xxxx
oradebug event 10046 trace name context forever, level 12;
/* execute your selects to be traced */
To stop tracing:

oradebug event 10046 trace name context off ;

6.Use a Logon Trigger

To start tracing:

create or replace trigger user_logon_trg
after logon on database
begin
if USER = 'xxxx' then
execute immediate
'Alter session set events ''10046 trace name context forever, level 8''';
end if;
end;
/

/* Login a new session as User 'xxxx' and execute your selects to be traced */
To stop tracing: via LogOff Trigger (needs to be created before logging off)

create or replace trigger user_logoff_trg
before logoff on database
begin
if USER = 'xxxx' then
execute immediate
'Alter session set events ''10046 trace name context off''';
end if;
end;
/

Pre-requisites for successful tracing
Set TIMED_STATISTICS to TRUE.
Set MAX_DUMP_FILE_SIZE  to a high value or œunlimited.


Tracing the Current Session
1.SQL_TRACE

To start tracing:

ALTER SESSION SET SQL_TRACE = TRUE ;
/* execute your selects to be traced */
To stop tracing:

ALTER SESSION SET SQL_TRACE = FALSE ;

2.10046

To start tracing:

Alter session set events ˜10046 trace name context forever, level 12;
/* execute your selects to be traced */
To stop tracing:

Alter session set events 10046 trace name context off;

3.DBMS_SUPPORT

To start tracing:

exec sys.dbms_support.start_trace ;
/* execute your selects to be traced */
To stop tracing:

exec sys.dbms_support.stop_trace ;

Tracing Another Session
In these examples, we wish to trace a session with SID of 8  Serial# of 226 obtained from V$SESSION.


1.Using "dbms_system.SET_BOOL_PARAM_IN_SESSION"

To start tracing:

exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', TRUE);
/* execute your selects to be traced */
To stop tracing:

exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', FALSE);

2.Using "dbms_system.set_ev"

To start tracing:

exec dbms_system.set_ev(18, 226, 10046, 12, '');
/* execute your selects to be traced */
To stop tracing:

exec dbms_system.set_ev(18, 226, 10046, 0, '');

3.Using "dbms_system.set_sql_trace_in_session"

To start tracing:

exec dbms_system.set_sql_trace_in_session(18,226,TRUE);
/* execute your selects to be traced */
To stop tracing:

exec dbms_system.set_sql_trace_in_session(18,226,FALSE);

4.Using "sys.dbms_monitor"

To start tracing:

exec sys.dbms_monitor.session_trace_enable(session_id=>18,serial_num=>226, waits=>true, binds=>true);
/* execute your selects to be traced */
To stop tracing:

exec sys.dbms_monitor.session_trace_disable(session_id=>18,serial_num=>226);

5.Using Oradebug (as SYS)

To start tracing:

oradebug setospid xxxx
oradebug event 10046 trace name context forever, level 12;
/* execute your selects to be traced */
To stop tracing:

oradebug event 10046 trace name context off ;

6.Use a Logon Trigger

To start tracing:

create or replace trigger user_logon_trg
after logon on database
begin
if USER = 'xxxx' then
execute immediate
'Alter session set events ''10046 trace name context forever, level 8''';
end if;
end;
/

/* Login a new session as User 'xxxx' and execute your selects to be traced */
To stop tracing: via LogOff Trigger (needs to be created before logging off)

create or replace trigger user_logoff_trg
before logoff on database
begin
if USER = 'xxxx' then
execute immediate
'Alter session set events ''10046 trace name context off''';
end if;
end;
/