待機しているセッションを調べる

DBサーバ作業中にコマンドが返ってこないなど問題のあるセッションを探すのに使うと効果的。

set time on
set pages 10000
set lines 110
col event for a60
col state for a18
col username for a15
col sid for 99999
select w.sid,s.username,w.state,w.event
from v$session s,v$session_wait w
where s.sid=w.sid
  and w.event not like '%Net message % client'
  and w.event not like 'rdbms ipc message';

   SID USERNAME   STATE                EVENT
------ ---------- -------------------- ----------------------------------------------------------
   536            WAITING              Streams AQ: qmn slave idle wait
   537            WAITING              Streams AQ: waiting for time management or cleanup tasks
   540            WAITING              Streams AQ: qmn coordinator idle wait
   548            WAITING              smon timer
   555            WAITING              pmon timer