venqueue_g

rem  venqueue_g.sql

rem

set linesize 132

rem

ttitle 'Locked and Blocking Sessions'

rem

col inst_id format 9 heading 'WAIT|INST|ID'

col sid format 9999 heading 'WAIT|SID'

col type format a4 heading 'TYPE'

col lmode format b9999 heading 'LMODE'

col request format b9999 heading 'REQUEST'

col ctime format 99999 heading 'SECONDS|HELD OR|WAITING'

col block format b9999 heading 'BLOCK'

col h_inst_id format 9 heading 'HOLD|INST|ID'

col h_sid format 9999 heading 'HOLD|SID'

col h_type format a4 heading 'TYPE'

col h_lmode format b9999 heading 'LMODE'

col h_request format b9999 heading 'REQUEST'

col h_ctime format 99999 heading 'SECONDS|HELD OR|WAITING'

col h_block format b9999 heading 'BLOCK'

col id1 format 999999999 heading 'ID1'

col id2 format 999999999 heading 'ID2'

rem

select w.inst_id, w.sid, w.type, w.block, w.ctime, w.lmode, w.request,

       h.inst_id h_inst_id, h.sid h_sid, h.type h_type, h.block h_block,

       h.ctime h_ctime, h.lmode h_lmode, h.request h_request,

       w.id1, w.id2

  from gv$lock h,

       gv$lock w

 where w.id1 = h.id1

   and w.id2 = h.id2

   and w.type = h.type

   and w.request > 0

   and h.block > 0

 order by w.id1, w.id2, w.block desc, w.ctime desc;