Query to Identify Trace File

In 11g:

Identification of trace files is simpler in Oracle 11g due to the introduction of diagnostic views.


SELECT *
FROM   v$diag_info

WHERE  name = 'Default Trace File';

Prior to 11g versions:

SELECT s.sid,
       s.serial#,
       pa.value || '/' || LOWER(SYS_CONTEXT('userenv', 'instance_name')) ||
       '_ora_' || p.spid || '.trc' AS trace_file
  FROM v$session s, v$process p, v$parameter pa
 WHERE pa.name = 'user_dump_dest'
   AND s.paddr = p.addr

   AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

Identify for specific session:

SELECT p.tracefile

FROM   v$session s

       JOIN v$process p ON s.paddr = p.addr

WHERE  s.sid = 309;

we can get SID from below query:

select * from v$session where schemaname='PHPMETREG' AND STATUS='ACTIVE'

From 12.2 version we can use below query:

SELECT *

FROM   v$diag_trace_file