Find Tracefile Name

The SQL below shows the tracefile name and location for the current session:

select ud.value || '/' || 
       dn.value || '_ora_' || 
       p.spid || 
       nvl2(p.traceid,  '_' || p.traceid, null ) ||
       '.trc'  "Trace File"
from   v$session s
  inner join v$process p
          on s.paddr = p.addr
  cross join (
     select value 
     from   v$parameter
     where  name = 'user_dump_dest'
             ) ud
  cross join (
     select value 
     from   v$parameter
     where  name = 'db_name'
             ) dn
where s.audsid = sys_context('userenv','sessionid')
;

With Oracle 11g, the tracefile is available directly in v$process:

select p.tracefile 
from   v$session s 
  inner join v$process p
          on s.paddr = p.addr
where  sys_context('userenv','sessionid') = s.audsid
;

Note that there seems to be a bug in pre Oracle 11g releases. v$process.traceid returns to the tracefile_identifier you have set for your session on all non-background processes... Therefore the result of the query below is wrong for pre 11g releases.

alter session set tracefile_identifier = 'mytraceid';

select ud.value || '/' || 
       dn.value || '_ora_' || 
       p.spid || 
       nvl2(p.traceid,  '_' || p.traceid, null ) ||
       '.trc'  "Trace File"
from   v$session s
  inner join v$process p
          on s.paddr = p.addr
  cross join (
     select value 
     from   v$parameter
     where  name = 'user_dump_dest'
             ) ud
  cross join (
     select value 
     from   v$parameter
     where  name = 'db_name'
             ) dn