Andrew Fraser DBA

Oracle top session info in RAC with tracefile name

See real time top session info in either RAC or standalone database, including tracefile name qualified by instance name, with:

-- ALTER SESSION SET nls_date_format = 'Dy DD-Mon-YYYY HH24:MI:SS' ;
SELECT DISTINCT s.inst_id , s.sid , s.serial# , s.username , s.osuser , s.program || ' ' || s.module AS program
     , s.status , ROUND ( s.last_call_et / 60 ) AS minutes , s.logon_time
     , s.state , s.event , s.seconds_in_wait , s.sql_id , q.sql_text
     , p.spid , s.service_name
     , CASE s.sql_trace WHEN 'ENABLED' THEN s.inst_id || ':' || p.tracefile END AS tracefile
  FROM gv$session s
  JOIN gv$sql q
    ON q.inst_id = s.inst_id
   AND q.sql_id = s.sql_id
  JOIN gv$process p
    ON p.inst_id = s.inst_id
   AND p.addr = s.paddr
 WHERE ( s.status = 'ACTIVE' OR last_call_et < 60 )  -- active in last minute
   AND s.username != 'SYS'
 ORDER BY s.inst_id , s.sid ;