Script: identify_trace_file.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays the name of the trace file associated with the current session.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @identify_trace_file
    -- -----------------------------------------------------------------------------------
    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');