Trace you session

Trace you session

1. Regular
Trace your own session
alter session set sql_trace=true;
or
alter session set events '10046 trace name context forever, level 4';
-- levels: 1(sql_trace=TRUE), 4, 8, 12

1.1. Regular 1
Trace your own session
exec dbms_session.set_sql_trace(TRUE);

2. Distance 1
dbms_system.set_bool_param_in_session(
sid IN NUMBER,
serial# IN NUMBER,
parnam IN VARCHAR2,
bval IN BOOLEAN);
example:
exec dbms_system.set_bool_param_in_session(10, 161, 'sql_trace', TRUE/FALSE);

3. Distance 2
dbms_system.set_sql_trace_in_session(
sid
NUMBER,
serial# NUMBER,
sql_trace BOOLEAN);
example
exec dbms_system.set_sql_trace_in_session(10, 1008, TRUE/FALSE);
or
EXEC DBMS_System.Set_Ev(sid, serial#, event, level, name);
EXEC DBMS_System.Set_Ev(31, 97, 10046, 4, '');

4. Distance 3 (new 11g)
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL,
waits
IN BOOLEAN
DEFAULT TRUE,
binds
IN BOOLEAN
DEFAULT FALSE,
plan_stat IN VARCHAR2
DEFAULT NULL);