查看oracle的锁的相关信息-优快云博客

本文介绍如何使用SQL查询来检查Oracle数据库中的锁定情况，包括锁定的用户、等待锁定的用户及锁定原因等信息，并提供了一个实用的SQL脚本来帮助快速定位问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查看oracle的锁的相关信息[@more@]

查看锁的用户和等待锁的用户相关信息

select 'blockers('||sb.sid||':'||sb.serial#||'-'||sb.username||')-'||qb.sql_text blockers,
'waiters('||sw.sid||':'||sw.serial#||'-'||sw.username||')-'||qw.sql_text waiters
from v$lock lb,v$lock lw,v$session sb,v$session sw,v$sql qb,v$sql qw
where lb.sid=sb.sid
and lw.sid=sw.sid
and sb.prev_sql_addr=qb.address
and sw.sql_address=qw.address
and lb.id1=lw.id1
and sb.lockwait is null
and sw.lockwait is not null
and lb.block=1;

BLOCKERS WAITERS
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
blockers(275:12-SCOTT)-begin :id := sys.dbms_transaction.local_transaction_id; e waiters(279:109-SCOTT)-update emp set comm = 2 where empno = 7937

查看引起锁的机器
select s.username,s.sid,s.serial#,
decode(lo.locked_mode,
0,'None',
1,'Null',
2,'Row-S(ss)',
3,'Row-X(sx)',
4,'share',
5,'s/row-x(ssx)',
6,'Exclusive',
to_char(lo.locked_mode)) mode_locked,
lo.os_user_name,
do.object_name,do.object_type
from v$session s,v$locked_object lo,dba_objects do
where s.sid=lo.session_id
and lo.object_id=do.object_id;

USERNAME SID SERIAL# MODE_LOCKED OS_USER_NAME OBJECT_NAME OBJECT_TYPE
------------------------------ ---------- ---------- ---------------------------------------- ------------------------------ -------------------------------------------------------------------------------- -------------------
SCOTT 281 2340 Row-X(sx) IBM EMP TABLE
SCOTT 279 109 Row-X(sx) oracle EMP TABLE

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/190276/viewspace-903005/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/190276/viewspace-903005/