Tracing Oracle Sessions using Trace Files:

Oracle 10046 trace can be used to identify the problematic SQL and its behavior.

Start New Session:
First of all we will start new session by starting SQL* PLUS.

Enable Session Trace:
Now we will run enable tracing for the current session by running below command.
ALTER SESSION SET SQL_TRACE=TRUE;

Enable Timed Statistics:
Now we will run below command to enable the timed statistics for our current session.
ALTER SESSION SET TIMED_STATISTICS=TRUE;

Set Dump File Size:

Now we will set the max_dump_file_size parameter to a value of unlimited so as to have a complete trace with all necessary information.
ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;

Add Identification Suffix:
Now we will set an identification suffix for our trace file so that we can identify the trace file for our current session amongst other trace files in the same directory.
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TRACE1';

Turn On Oracle 10046 Trace Event:
Now we will turn on Oracle 10046 Trace Event on for our current session. The valid values for the Trace Level are as under:

Level 1 Standard SQL trace. Traces all activities until the end of the session.
Level 4 Trace the SQL statements for binds only.
Level 8 Trace the SQL statements for waits only.
Level 12 Trace the SQL statements for both binds and waits.

Attempting to run ALTER SESSION SET EVENTS…. directly may result in ORA-01031: insufficient privileges error. Therefore you will have to log in as SYS, grant privileges and then run the ALTER SESSION command.
LOGGED IN AS SYS..
GRANT ALTER SESSION TO MYTF1;
LOGGED IN AS MYTF1..
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

Turn Off Oracle Trace:
Now run some simple query and then turn off Oracle trace.
SELECT * FROM MYTABLE;
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

Look for Trace File:

Now we will look for your trace file by finding the value specified in USER_DUMP_DEST parameter.
SELECT VALUE FROM V$PARAMETER WHERE NAME='USER_DUMP_DEST';
Once you have found the value then change to that directory in your OS.
TRACE1.TRC
You can find the correct trace file by running below command in UNIX prompt.
find . -name "*TRACE1*" –print
Finally we will use the TKPROF utility to create a human readable file of our trace file.
TKPROF TRACE1.TRC