event 10046 – tracing sessions

how to trace session by
– alter session
– oradebug
– logon trigger

tracefiles will be placed in user_dump_dest (10g) or in diag_dest/db/inst/trace (11g)

Session Tracing

alter session set max_dump_file_size = unlimited;
alter session set tracefile_identifier='tr10046';
alter session set events '10046 trace name context forever, level 12';

— level 1 trace
— level 4 binds
— level 8 waits
— level 12 binds and waits

— Execute the queries or operations to be traced here —

select /* tr10046 */ to_char(sysdate,'dd-mm-yy hh24:mi:ss') now from dual;
exit;

Tracing a running process

select p.PID,p.SPID,s.SID, s.username
 from v$process p,v$session s
 where s.paddr = p.addr
 and s.sid = 518
 /
connect / as sysdba
oradebug setospid 1234
 oradebug unlimit
 oradebug event 10046 trace name context forever,level 12
 oradebug tracefile_name
 oradebug event 10046 trace name context off

Tracing via trigger

CREATE OR REPLACE TRIGGER SYS.enable_trace
 AFTER LOGON ON DATABASE
 WHEN (USER like  'USERNAME')
 DECLARE
 lcommand varchar(200);
 BEGIN
 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;
 /

— the traced schema needs alter session privilege

grant alter session to ;