查询用户所持有的锁

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