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. 
select 
  u_dump.value   || '/'     || 
  db_name.value  || '_ora_' || 
  v$process.spid || 
  nvl2(v$process.traceid,  '_' || v$process.traceid, null ) 
  || '.trc'  "Trace File"
from 
             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 
 u_dump.name   = 'user_dump_dest' and 
 db_name.name  = 'db_name'        and
 v$session.audsid=sys_context('userenv','sessionid');
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

Links

On reading trace files with PL/SQL.

Thanks

Thanks to Doug Hersh for two corrections on this page.