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);


Examples:

-- enable tracing for a client with a given client session ID:
SELECT schemaname, sid, serial#
FROM gv$session;

exec dbms_monitor.session_trace_enable(144, 253, TRUE, FALSE);

-- disable tracing specified in the previous step:
exec dbms_monitor.session_trace_disable(144, 253);

-- either
exec dbms_monitor.session_trace_enable(144);

-- or
exec dbms_monitor.session_trace_enable(144, NULL);

-- traces the session with session ID of 144, while either
exec dbms_monitor.session_trace_enable;

-- or
exec dbms_monitor.session_trace_enable(NULL, NULL);

-- trace the current user session
exec dbms_monitor.session_trace_enable(NULL, NULL, TRUE, TRUE);

-- traces the current user session including waits and binds.
-- the same can be also expressed using keyword syntax:
exec dbms_monitor.session_trace_enable(binds=>TRUE);

-- enable tracing for a client with a given client session ID:
exec dbms_monitor.session_trace_enable(144, 253, TRUE, FALSE);

-- disable tracing specified in the previous step:
exec dbms_monitor.session_trace_disable(144, 253);