Dev's Weblog

Reading time: 1 - 2 minutes

Sometimes it becomes very tough to find the relevant oracle trace file using session id in oracle.You can alter your session before setting an event to identify trace file quite easily but following method doesn't require alteration of session.

SELECT s.sid,
s.server,
lower(
CASE
WHEN s.server IN ('DEDICATED','SHARED') THEN
i.instance_name || '_' ||
nvl(pp.server_name, nvl(ss.name, 'ora')) || '_' ||
p.spid || '.trc'
ELSE NULL
END
) AS trace_file_name
FROM v$instance i,
v$session s,
v$process p,
v$px_process pp,
v$shared_server ss
WHERE s.paddr = p.addr
AND s.sid = pp.sid (+)
AND s.paddr = ss.paddr(+)
AND s.type = 'USER'
ORDER BY s.sid;

Sample Output:

       SID SERVER    TRACE_FILE_NAME
---------- --------- --------------------------------------------------
     8    DEDICATED orcl_ora_17842.trc
    10   DEDICATED orcl_ora_17846.trc
    16   DEDICATED orcl_ora_17856.trc
    199 DEDICATED orcl_ora_17848.trc
    394 DEDICATED orcl_ora_17844.trc
    400 DEDICATED orcl_ora_17850.trc
    580 DEDICATED orcl_ora_17852.trc
    585 DEDICATED orcl_ora_17840.trc

8 rows selected.