Identify Trace File

Different methods to identify your trace file:

     1) From v$diag_info
     2) By Joining v$session, v$process, and v$parameter
     3) Set TRACEFILE_IDENTIFIER parameter

1) Identify the current trace file.
SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
VALUE
-----------------------------------------------------------------------------
d:\app\diag\rdbms\obieedb\obieedb\trace\obieedb_ora_7728.trc


2)  You can get trace file using this query:

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


3) You can set the TRACEFILE_IDENTIFIER parameter at session level to allow you to include some recognizable text into the trace file name:


ALTER SESSION SET TRACEFILE_IDENTIFIER = "POST_FIX_TEXT";