oracle 8

锁定查询系列...
----------------------------------------
Q: 如何查询哪些进程相互锁定？
A: 执行以下SQL语句
set pages 800
set lines 150
column blocking format a30
column blocked format a30
select 'INST_ID:'||l1.inst_id||',SID:'||l1.sid "Blocking", 'INST_ID:'||l2.inst_id||',SID:'||l2.sid "Blocked"
from gv$lock l1, gv$lock l2
where l1.block = 1 and l2.request > 0
and l1.id1 = l2.id1 and l1.id2 = l2.id2
/
Q: 如何查询哪些对象被哪些进程锁定？
A: 执行以下SQL语句查询所有对象
set pages 800
set lines 150
column object_name format a40
select s.inst_id,s.sid,s.serial#,o.owner,o.object_name,o.object_type,l.locked_mode
from gv$session s,
gv$locked_object l,
dba_objects o
where s.sid = l.session_id
and s.inst_id = l.inst_id
and l.object_id = o.object_id
/
Q: 如何查询某特定对象被哪些进程锁定？
A: 执行以下语句查询特定对象
select s.inst_id,s.sid,s.serial#,o.owner,o.object_name,o.object_type,l.locked_mode
from gv$session s,
gv$locked_object l,
dba_objects o
where s.sid = l.session_id
and s.inst_id = l.inst_id
and l.object_id = o.object_id
and o.owner = '&owner_name'
and o.object_name = '&object_name'
/
Q: 如何查询那个进程锁死了用户对象(library cache pin and lock)？
A: 执行以下查询
select /*+ ordered */ 
       hs.sid||','||hs.serial#||',@'||hs.inst_id  holding_session,
       ws.sid||','||ws.serial#||',@'||ws.inst_id  waiting_session,
       wl.kgllktype lock_or_pin,
       decode(hl.kgllkmod,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
            'Unknown') mode_held, 
       decode(wl.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
            'Unknown') mode_requested
  from dba_kgllock wl, dba_kgllock hl, gv$session ws, gv$session hs
  where hl.kgllkmod not in (0,1) and hl.kgllkreq in (0,1)
   and wl.kgllkmod in (0,1) and wl.kgllkreq not in (0,1)
   and wl.kgllktype = hl.kgllktype
   and wl.kgllkhdl = hl.kgllkhdl
   and wl.kgllkuse = ws.saddr
   and hl.kgllkuse = hs.saddr
/