SQL Session Tracing

1. Start a sqlplus session to the database, and enable sql tracing with this command:

alter session set events '10046 trace name context forever, level 12';

The resulting trace file will be put in the USER_DUMP_DEST directory.
To find the location of this directory, as system, do:
SQL>show parameters user_dump_dest

2. Execute your code to reproduce the error/issue.

3. Turn off the trace using this command:

alter session set events '10046 trace name context off';

4. Please upload the trace file and the alert.log file


A 10046 trace creates an output dump file. Therefore, before you enable the 10046 trace event, you will need to set some database parameters that control the output of the dump file. These parameters include:

TIMED_STATISTICS – Must be set to TRUE to get timing information in your trace files.

MAX_DUMP_FILE_SIZE – Controls the maximum size of the trace file. For 10046 trace files, the default setting is generally too small.

USER_DUMP_DEST – This is the location the 10046 trace file(s) are written to.

STATISTICS_LEVEL – This should be set, at a minimum, to TYPICAL. When set to ALL, more information will be collected in the resulting trace files. All examples in this white paper are with STATISTICS_LEVEL set to TYPICAL.