Question

如何快速查出 oracle 数据库中的锁等待

Answer 1

用如下语句：

select a.username, a.sid, a.serial#, b.id1
  from v$session a, v$lock b
 where a.lockwait = b.kaddr;

如果存在锁等待，查询后有结果，反之则无结果：

Answer 2

被锁定的数据库表及相关的sid、serial#及spid
select object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号
from v$locked_object l , dba_objects o , v$session s , v$process p
where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;

--锁检查
select /*+ rule */
lpad('--', decode(b.block, 1, 0, 4)) || s.username user_name,
b.type,
o.owner || '.' || o.object_name object_name,
s.sid,
s.serial#,
decode(b.request, 0, 'BLOCKED', 'Waiting') status,
t.SQL_TEXT
from dba_objects o,
v$session s,
v$lock v,
v$lock b,
v$sqltext_with_newlines t
where v.id1 = o.object_id
and v.sid = s.sid
and v.sid = b.sid
and (b.block = 1 or b.request > 0)
and v.type = 'TM'
and t.ADDRESS = s.PREV_SQL_ADDR
order by status,s.sid;