René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -

Find trace file in Oracle

The following script returns the path to the trace file that the current session writes. It returns the path whether or not tracing is enabled.
  u_dump.value   || '/'     || 
  db_name.value  || '_ora_' || 
  v$process.spid || 
  nvl2(v$process.traceid,  '_' || v$process.traceid, null ) 
  || '.trc'  "Trace File"
             v$parameter u_dump 
  cross join v$parameter db_name
  cross join v$process 
        join v$session 
          on v$process.addr = v$session.paddr
where   = 'user_dump_dest' and  = 'db_name'        and
In order to make a more informative trace file name, the following command can be used:
alter session set tracefile_identifier = here_is_my_session;
A trace file will then have this identifier (here: here_is_my_session) in it's filename.
The trace file's name can also be found with oradebug:
SQL> oradebug setmypid
SQL> oradebug tracefile_name



Thanks to Doug Hersh for two corrections on this page.