Oracle中V$LOCK 视图的使用小结

select * from v$lock order by ctime desc;

select 
  l1.sid as "阻塞会话id",
  s1.username as "阻塞用户",
  s1.osuser as "阻塞os用户",
  s1.machine as "阻塞机器",
  l2.sid as "被阻塞会话id",
  s2.username as "被阻塞用户",
  l1.type as "锁类型",
  decode(l1.type,
    'tx', '事务锁',
    'tm', '表锁',
    'ul', '用户锁',
    l1.type) as "锁描述",
  l1.ctime as "持有时间(秒)"
from v$lock l1, v$lock l2, v$session s1, v$session s2
where l1.block = 1 
and l2.request > 0
and l1.id1 = l2.id1
and l1.id2 = l2.id2
and l1.sid = s1.sid
and l2.sid = s2.sid;

select l.sid, s.username, s.status, l.type, l.lmode, l.ctime, o.object_name
from v$lock l, dba_objects o, v$session s
where l.id1 = o.object_id(+)
and l.type = 'tm'
and l.sid = s.sid
and o.object_name = 'employees';

-- 1. 查找阻塞会话
select sid, serial#, username from v$session 
where sid in (select blocking_session from v$session where blocking_session is not null);

-- 2. 终止会话
alter system kill session 'sid,serial#' immediate;