enable tracing in own and other's session in oracle

There are documented as well as non documented both methods available for enabling tracing in oracle sessions. Some trace files like trace for event 10046 requires tkprof utility to format them while some trace files like for 10053  does not require

Documented method:

enable/disable trace in own session,requies alter session system privilege
Alter session set sql_trace=true; --enaled basic tracing
Alter session set sql_trace=false;  --disables basic tracing


or dbms_session.swt_sql_trace(sql_trace=>true); enabled basic tracing
dbms_session.swt_sql_trace(sql_trace=>false);  --disabled basic tracing

to enable/disable tracing in other's session using DBA login

• dbms_system.set_sql_trace_in_session(sid,serial,true) --enabled tracing
• dbms_system.set_sql_trace_in_session(sid,serial,false) --disabled tracing

Undocuemnted methods:

• dbms_system.set_ev(sid,serial#,10046,1,''); --enables basic tracing
• dbms_system.set_ev(sid,serial#,10046,4''); --enables tracing with bind values capturing
• dbms_system.set_ev(sid,serial#,10046,8,'');--enables tracing with waits events information capturing
• dbms_system.set_ev(sid,serial#,10046,12,'');--enables tracing with bind values + wait events
• dbms_system.set_ev(sid,serial#,10046,0,''); --disables tracing

its documented equilavent has come into oracle 10g:

DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);

DBMS_SUPPORT.stop_trace;
DBMS_SUPPORT.start_trace_in_session(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE);

EXEC DBMS_SUPPORT.stop_trace_in_session(sid=>123, serial=>1234);

 Optimizer thought proces and sort tracing:

• dbms_system.set_ev(sid,serial#,10053,1,''); --enables formatted tracing for optimizer information
• dbms_system.set_ev(sid,serial#,10033,1,''); --enables formatted tracing trace sorts for query

Undocumented enabling tracing by setting events methods:

• ALTER SESSION SET events ''10046 trace name context forever, level 12''; --enables level 12 tracing i.e it captures bind data and wait events also
• ALTER SESSION SET events ''10046 trace name context off''; --disables tracing

If changing the application code for enabling tracing is not possible as most are the cases then tracing can be enabled from logon triggers

CREATE OR REPLACE TRIGGER db_logon_trigger
AFTER LOGON ON DATABASE
DECLARE
BEGIN
IF USER = 'username_to_be_traced'
THEN
 EXECUTE IMMEDIATE 'ALTER SESSION SET events ''10046 trace name context forever, level 12''';
END IF;
END;