1）查看当前的用户会话和对应的锁信息 
select s.sid,s.SERIAL#,s.USERNAME,s.STATUS,l.ID1,l.LMODE,l.REQUEST
 from v$session s,v$lock l
 where s.SID=l.SID and s.USERNAME is not null;
说明：
a.  username字段为空-------后台进程会话
b.  request字段-----------会话申请的锁类型
  • 0 - none
  • 1 - null (NULL)
  • 2 - row-S (SS)
  • 3 - row-X (SX)
  • 4 - share (S)
  • 5 - S/Row-X (SSX)
  • 6 - exclusive (X)
c.  lmode字段-----------会话持有锁类型
  • 0 - none
  • 1 - null (NULL)
  • 2 - row-S (SS)
  • 3 - row-X (SX)
  • 4 - share (S)
  • 5 - S/Row-X (SSX)
  • 6 - exclusive (X)
2）查看造成锁等待的锁的信息
 
select l.ID1,l.LMODE,l.REQUEST
 from v$session s,v$lock l
 where s.LOCKWAIT=l.KADDR;
3）查找造成锁等待的会话信息
select s.SID,s.SERIAL#
 from v$session s,v$lock l
 where l.ID1 in (select distinct l2.ID1 from v$session s2,v$lock l2 where s2.LOCKWAIT=l2.KADDR)
       and s.SID=l.SID and l.LMODE<>0;
4）kill掉不释放锁的会话
alter system kill session 'sid,serial#';   --sid,serial# 为3）中查询