下面的脚本可以查看哪些对象被哪些会话锁定，以及锁定的类型
col usernameformat a10
col lock_type format a15
col object_name format a15
select oracle_username username,session_id sid,decode(
locked_mode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',
5,'Share Row Exclusive',6,'Exlusive') lock_type,object_name,xidusn,xidslot,xidsqn
from v$locked_object,dba_objects
where v$locked_object.object_id=dba_objects.object_id;
如果是RAC:
col username format a15
col lock_type format a15
col object_name format a15
select oracle_username username,inst_id,session_id sid,decode(
locked_mode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',
5,'Share Row Exclusive',6,'Exlusive') lock_type,object_name,xidusn,xidslot,xidsqn
from gv$locked_object,dba_objects
where gv$locked_object.object_id=dba_objects.object_id;
例如:
SQL> col usernameformat a10
SQL> col lock_type format a15
SQL> col object_name format a15
SQL> select oracle_username username,session_id sid,decode(
2 locked_mode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',
3 5,'Share Row Exclusive',6,'Exlusive') lock_type,object_name,xidusn,xidslot,xidsqn
4 from v$locked_object,dba_objects
5 where v$locked_object.object_id=dba_objects.object_id;
USERNAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
------------------------------ ---- --------------- --------------- ---------- ---------- ----------
ROBINSON 148 Row Exclusive EMP 4 42 822
下面的脚本可以查看当前TX,TM锁的主要信息，并且按照锁定时间降序排列
col resource format a15
col sid format 9999
col request a15
select type||'-'||id1||'-'||id2 "resource",sid,decode(
lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',
5,'Share Row Exclusive',6,'Exlusive') lock_type,decode(request,0,'None',1,'Null',2,'Row share',
3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exlusive') request,ctime, block
from v$lock where type in ('TX','TM')
order by "resource",ctime desc;
如果是RAC
col resource format a25
col lock_type format a30
col request format a20
select inst_id,sid, type||'-'||id1||'-'||id2 "resource",decode(
lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',
5,'Share Row Exclusive',6,'Exlusive') lock_type,decode(request,0,'None',1,'Null',2,'Row share',
3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exlusive') request,ctime, block
from Gv$lock where type in ('TX','TM')
order by "resource",ctime desc;
其中也能够看到谁阻塞了谁，request表示请求锁，lock_type表示已经获得的锁类型，如果resource相同那么表示request被另外一个session阻塞了
例如：
SQL> col resource format a15
SQL> col sid format 9999
SQL> col request a15
SQL> select type||'-'||id1||'-'||id2 "resource",sid,decode(
2 lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',
3 5,'Share Row Exclusive',6,'Exlusive') lock_type,decode(request,0,'None',1,'Null',2,'Row share',
4 3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exlusive') request,ctime, block
5 from v$lock where type in ('TX','TM')
6 order by "resource",ctime desc;
resource SID LOCK_TYPE REQUEST CTIME BLOCK
--------------- ---- --------------- ------------------- ---------- ----------
TM-52556-0 148 Row Exclusive None 969 0
TX-262186-822 148 Exlusive None 969 0
下面脚本可以查看哪个会话阻塞了另外一个会话
SELECT DECODE(request,0,'Holder: ','Waiter: ')|| sid as sid, id1, id2, lmode,
request, type FROM V$LOCK WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request;
如果是RAC
col sid format a30
SELECT inst_id,DECODE(request,0,'Holder: ','Waiter: ')|| sid as sid, id1, id2, lmode,
request, type FROM GV$LOCK WHERE (inst_id, id1, id2, type) IN
(SELECT inst_id,id1, id2, type FROM GV$LOCK WHERE request>0) ORDER BY id1, request;
例如：
SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ')|| sid as sid, id1, id2, lmode,
2 request, type FROM V$LOCK WHERE (id1, id2, type) IN
3 (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request;
SID ID1 ID2 LMODE REQUEST TYPE
------------------------------------------------ ---------- ---------- ---------- ---------- ----
Holder: 152 65567 399 6 0 TX
Waiter: 142 65567 399 0 6 TX