Finding Your SQL Trace File in Oracle Database

 So this question was asked to me a few months back in one of my presentations.  How do I find a trace file from SQL trace on my Oracle Database?

Sorry for the delay, but here are my suggestions.  First you will need to know where your trace file will be located.  By default it will be in the trace directory as part of the diagnostic destination. Make sure you know where your diagnostic destination is located.

SQL> show parameter diag
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/app/oracle

There are basically three four main ways to find your trace file.

Option 0 - Just ask

Well I learned something new, so I wanted to update this article.  There is a view you can directly query to see your current trace file name.  

SELECT value 
      FROM v$diag_info 
      WHERE name = 'Default Trace File';

This will show the correct trace file name when you update your TRACEFILE_IDENTIFER.  So I would still recommend setting that as outlined in Option 1.

Option 1 - Manually set the trace file name

For this option, we set a session level parameter to drive the name of the trace file.

ALTER SESSION SET tracefile_identifier = 'SQLID_fua0hb5hfst77';

Note: if you receive an error turning on tracing, make sure the user has been granted the alter session privilege.

Next we enable tracing:

EXEC DBMS_SESSION.SET_SQL_TRACE(sql_trace => true);

Run the SQL statement you want to trace:

SELECT sum(t1.c), sum(t2.c)
  FROM t1, t2
  WHERE t1.a = t2.a
   AND t1.d = 10;

Then disable the tracing:

EXEC DBMS_SESSION.SET_SQL_TRACE(sql_trace => false);

Here we can not see the trace file which contains the identifier we gave above:

cd /u01/app/oracle/diag/rdbms/t1db/t1db/trace/

[oracle@srvr03 trace]$ ls -ltrh *fua0*
-rw-r----- 1 oracle oinstall 4.3K Feb  5 08:29 t1db_ora_4978_SQLID_fua0hb5hfst77.trc
-rw-r----- 1 oracle oinstall 2.0K Feb  5 08:29 t1db_ora_4978_SQLID_fua0hb5hfst77.trm

If you are trying to do extended SQL or CBO tracing you can also use different options for doing the SQL trace.  If the trace is going to be large you should also check the size of the maximum dump file.  This can also be adjusted at the session level.

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'; -- CBO tracing
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; -- Additional SQL trace information

SQL> show parameter max_dump_file_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size                   string      unlimited

Option 2 - get your session diagnostic information

You can query the name of the current diagnostic trace file for the session.

set linesize 100
column name format a20
column value format a70
select name, value from v$diag_info where name = 'Default Trace File';

NAME		     VALUE
-------------------- ----------------------------------------------------------------------
Default Trace File   /u01/app/oracle/diag/rdbms/t1db/t1db/trace/t1db_ora_31523.trc

Now repeat the process of turning on trace, running your SQL, and turning trace back off.

Option 3 - Searching for the trace file

You can search for the trace file at the OS level.  This will probably give you a list of files to verify which one contains the trace file in question.  If you get the SID from the session you can include that in the file search.

SQL> select userenv('SID') from dual;
USERENV('SID')
--------------
	    19

Then search the trace directory (based on the diagnostic destination setting of your database):

find /u01/app/oracle/diag/rdbms/t1db/t1db/trace -name t1db_ora\*.trc -mtime 0 -exec grep -l "*** SESSION ID:(19." {} \;
/u01/app/oracle/diag/rdbms/t1db/t1db/trace/t1db_ora_26655.trc
/u01/app/oracle/diag/rdbms/t1db/t1db/trace/t1db_ora_4978_SQLID_fua0hb5hfst77_.trc
/u01/app/oracle/diag/rdbms/t1db/t1db/trace/t1db_ora_31523.trc
/u01/app/oracle/diag/rdbms/t1db/t1db/trace/t1db_ora_14396.trc

Note the mtime option for find is looking for files that were updated in the past 24 hours.  If you want to look for older files adjust the number after mtime number.  E.G. +1 will look for files modified 48 hours ago.

There are other ways, but these should be some of the easiest ways.

Gary