10046 trace

July 30, 2012 admin Oracle

To gather 10046 trace at the session level:

SQL> alter session set timed_statistics = true;
SQL> alter session set statistics_level=all;
SQL> alter session set max_dump_file_size = unlimited;
SQL> alter session set events ‘10046 trace name context forever,level 12’;

SQL> — run your select(s) —

SQL> select * from dual;
SQL> exit;

This will produce a trace file in your user_dump_dest.

See: Note:376442.1 Recommended Method for Obtaining 10046 trace for Tuning

Remove an agent target that won’t delete. alter database commands to get backup controlfile