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');
Here is what the above code is Doing:
1. It’s querying the V$SESSION view to get the SID and SERIAL# of the session that is currently running the query.
2. It’s querying the V$PROCESS view to get the SPID of the process that is currently running the query.
3. It’s querying the V$PARAMETER view to get the value of the user_dump_dest parameter.
4. It’s concatenating the value of the user_dump_dest parameter with the instance name and the SPID to get the name of the trace file.
5. It’s displaying the SID, SERIAL#, and trace file name.