Search notes:

SQLPATH - find-trace-file.sql

Note: the following SQL script is quite old. When I originially wrote it, possibly more than 15 years ago, it was supposed to find the location of a «my» trace file. Yet, I just found out today, that it does not correctly work. Better seems to be tracefile.sql.

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');

Github repository Oracle-SQLPATH, path: /find-trace-file.sql

SQLPATH - find-trace-file.sql

See also