Oracle Trace File Info .....

Oracle Trace File path ....

Trace file info ..

select value || '/' ||
(select lower(instance_name) from v$instance) ||
'_ora_' ||
(
select spid
from v$process
where addr = (
select paddr
from v$session
where sid = (
select sid
from v$mystat
where rownum = 1
)
)
) ||
'.trc'
from
v$parameter
where
name = 'user_dump_dest'
;

/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_17513.trc

1 row selected.




SET ECHO OFF FEEDBACK 6 HEADING ON LINESIZE 200 PAGESIZE 0 TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL ON VERIFY OFF

SELECT  a.trace_path || ' > ' || b.trace_file "Trace File Path"
FROM
    (  SELECT value trace_path   FROM   v$parameter  WHERE  name='user_dump_dest' ) a
  , (  SELECT c.instance || '_ora_' || spid ||'.trc' TRACE_FILE   FROM   v$process,
    (select lower(instance_name) instance from v$instance)  c
    WHERE  addr = ( SELECT paddr   FROM v$session  WHERE (audsid, sid) = (  SELECT  sys_context('USERENV', 'SESSIONID') , sys_context('USERENV', 'SID')   FROM dual))) b
/

==============================================================

SQL>  ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database altered.


Oracle 11gr2 +

to check  trace file name ...


SET ECHO OFF FEEDBACK 6 HEADING ON LINESIZE 200 PAGESIZE 0 TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL ON VERIFY OFF
SELECT  a.trace_path || ' > ' || b.trace_file         "Trace File Path",
        '! cat '||a.trace_path || '/' || b.trace_file "Trace File Path"
FROM
    (  SELECT VALUE trace_path   FROM   v$diag_info   WHERE  name='Diag Trace' ) a
  , (  SELECT c.instance || '_ora_' || spid ||'.trc' TRACE_FILE   FROM   v$process,
    (select instance_name instance from v$instance)  c
    WHERE  addr = ( SELECT paddr   FROM v$session  WHERE (audsid, sid) = (  SELECT  sys_context('USERENV', 'SESSIONID') , sys_context('USERENV', 'SID')   FROM dual))) b
/

set linesize 200 pagesize 200 
col name  for a20
col value for a70
select * from v$diag_info;