查询Oracle锁信息-优快云博客

本文提供查询Oracle中TX锁、TM锁及DDL锁的方法，通过SQL语句展示如何获取用户所持有的锁信息，包括会话阻塞情况及锁定的对象详情。

查询用户所持有的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;