Show waiting sessions and the event they wait on

#!/bin/ksh

sqlplus -s "/ as sysdba" <<EOF

select  sw.sid     sid,
        p.spid     spid,
        s.username username,
        s.osuser   osuser,
        sw.event   event,
        s.machine  machine,
        s.program  program,
        decode(sw.event,'db file sequential read', sw.p3, 'db file scattered read', sw.p3, null) blocks
   from v$session_wait sw,v$session  s, v$process p
  where s.paddr = p.addr
    and event not in ('pipe get','client message')
    and sw.sid  = s.sid

EOF

Leave a Reply

Your email address will not be published. Required fields are marked *