10046 tracing in another session

To switch 10046 tracing on in another session (first setting on timed statistics and making max_dump_file_size very large):

set pages 9999 verify off
col serial new_value serial noprint
select serial# serial from v$session where sid = &1 ;
exec sys.dbms_system.set_bool_param_in_session(&1,&serial,'timed_statistics',true)
exec sys.dbms_system.set_int_param_in_session(&1,&serial,'max_dump_file_size',999999999)
exec sys.dbms_system.set_ev(&1,&serial,10046,8,'')

And to switch it off:

set pages 9999 verify off
col serial new_value serial noprint
select serial# serial from v$session where sid = &1 ;
exec sys.dbms_system.set_ev(&1,&serial,10046,0,'')

Note that you can run new (9i+) tkprof against old (8i and below) 10046 trace files in order to report the wait information. It is also possible to cat multiple trace files together before tkprof’ing.

Based on Alexander Bubernak’s post at http://www.dbasupport.com/oracle/ora10g/10046event.shtml