Oracle Session Trace File Identifier
This script displays the trace file name associated with the current database session. It joins session, process, and parameter views to construct the full trace file path, helping DBAs and developers quickly locate session-specific trace files for debugging and performance analysis.
oraclesqlmonitoring-alertsv1.0.0
0 stars0 downloads11 views0 comments
By OracleDba • Created
Code
(21 lines)123456789101112131415161718192021
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/identify_trace_file.sql
-- Author : Tim Hall
-- Description : Displays the name of the trace file associated with the current session.
-- Requirements : Access to the V$ views.
-- Call Syntax : @identify_trace_file
-- Last Modified: 17-AUG-2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 100
COLUMN trace_file FORMAT A60
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');