oracle trace session 1

oracle trace session

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.

Similar Posts