查询用户所持有的锁

查询用户所持有的TX锁

select username,
       v$lock.sid,
       trunc(id1 / power(2, 16)) rbs,
       bitand(id1, to_number('ffff', 'xxxx')) + 0 slot,
       id2 seq,
       lmode,
       request
  from v$lock, v$session
 where v$lock.type = 'TX'
   and v$lock.sid = v$session.sid
   and v$session.username = 'TEST';
   
 一个会话阻塞另一个会话
 select (select username from v$session where sid = a.sid) blocker,
        a.sid,
        ' is blocking ',
        (select username from v$session where sid = b.sid) blockee,
        b.sid
   from v$lock a, v$lock b
  where a.block = 1
    and b.request > 0
    and a.id1 = b.id1
    and a.id2 = b.id2;

 

查询用户所持有的TM锁

 select (select username from v$session where sid = v$lock.sid) username,
        sid,
        id1,
        id2,
        lmode,
        request,
        block,
        v$lock.type
   from v$lock
  where sid = (select sid from v$mystat where rownum = 1)

 

查询会话所持有的对象parse lock

 

sys用户安装DBA_DDL_LOCKS视图，Install this and other locking views by running the catblock.sql script found in the directory [ORACLE_HOME]/rdbms/admin

 

select session_id     sid,
       owner,
       name,
       type,
       mode_held      held,
       mode_requested request
  from dba_ddl_locks;