Find trace file in Oracle

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage. 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. set linesize 300 pagesize 50 column "Trace File" format a60 column username format a12 select   v$process.spid, v$session.sid, v$session.serial#, v$session.username, v$session.sql_id, v$session.status,   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: SQL> alter session set tracefile_identifier = 'trace_location_and_name'; A trace file will then have this identifier (trace_location_and_name) in it's specified path The trace file's name can also be found with oradebug: SQL> oradebug setmypid SQL> oradebug tracefile_name