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.