使用DBMS_ROWID获取被阻塞行的rowid

在使用v$session视图在查询会话的行锁的等待事件时，视图中提供了会话等待的对象号（ROW_WAIT_OBJ#）、文件号（ROW_WAIT_FILE#）、块号（ROW_WAIT_BLOCK#）和行号（ROW_WAIT_ROW#）但是如何使用这些信息定位出会话等待的是哪一行呢？答案就是使用DBMS_ROWID打开两个会话同时更新同一条数据#session 1
linuxidc@ORCL>select distinct sid from v$mystat;

SID
----------
22

linuxidc@ORCL>
zx@ORCL>update zx set name="zx" where id=1;

1 row updated.

#session 2
linuxidc@ORCL>select distinct sid from v$mystat;

SID
----------
145

linuxidc@ORCL>update zx set name="zx" where id=1;此时session2会被session1阻塞，查询v$session会话145在等待enq: TX - row lock contentionlinuxidc@ORCL>col event for a40
linuxidc@ORCL>select SID,EVENT,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where sid=145;

SID EVENT ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ---------------------------------------- ------------- -------------- --------------- -------------
145 enq: TX - row lock contention 99754 18 15571 7查询v$lock确认会话145在请求会话22的TX锁linuxidc@ORCL>select sid,type,id1,id2,lmode,request from v$lock where sid=145 or sid=22 order by 1;

SID TYPE ID1 ID2 LMODE REQUEST
---------- ------ ---------- ---------- ---------- ----------
22 AE 100 0 4 0
22 TM 99754 0 3 0
22 TX 4390915 581 6 0
145 TM 99754 0 3 0
145 TX 4390915 581 0 6
145 AE 100 0 4 0使用如下语句查询会话145等待哪个表的哪个行linuxidc@ORCL>col owner for a10
linuxidc@ORCL>col object_name for a10
linuxidc@ORCL>col rowid for a30
linuxidc@ORCL>select b.owner,b.object_name,dbms_rowid.rowid_create（1,s.ROW_WAIT_OBJ#,s.ROW_WAIT_FILE#,s.ROW_WAIT_BLOCK#,ROW_WAIT_ROW#） "rowid" from v$session s,dba_objects b where s.ROW_WAIT_OBJ#=b.object_id and s.si
d=145;
OWNER OBJECT_NAM rowid
---------- ---------- ------------------------------
ZX ZX AAAYWqAASAAADzTAAH
--使用上面查询出的rowid查看数据，即为session2等待的行
linuxidc@ORCL>select * from zx.zx where rowid="AAAYWqAASAAADzTAAH";

ID NAME
---------- ------------------------------
1 ZX官方文档：http://docs.Oracle.com/cd/E11882_01/appdev.112/e40758/d_rowid.htm#ARPLS053使用下面语句查找会话之间的阻塞关系 SELECT （"节点" || a.inst_id || " session " || a.sid || "," || a_s.serial# ||
"阻塞了节点" || b.inst_id || " session " || b.sid || "," || b_s.serial#） blockinfo,
a.inst_id,
a_s.sid,
a_s.schemaname,
a_s.module,
a_s.status,
a_s.event,
a.type lock_type,
a.id1,
a.id2,
decode（a.lmode,
0,
"none",
1,
NULL,
2,
"row-S（SS）",
3,
"row-X（SX）",
4,
"share（S）",
5,
"S/Row-X（SSX）",
6,
"exclusive（X）"） lock_mode,
a.ctime time_hold,
"后为被阻塞信息" remark_flag,
b.inst_id blocked_inst_id,
b.sid blocked_sid,
b.type blocked_lock_type,
decode（b.request,
0,
"none",
1,
NULL,
2,
"row-S（SS）",
3,
"row-X（SX）",
4,
"share（S）",
5,
"S/Row-X（SSX）",
6,
"exclusive（X）"） blocked_lock_request,
b.ctime time_wait,
b_s.schemaname blocked_schemaname,
b_s.module blocked_module,
b_s.status blocked_status,
b_s.sql_id blocked_sql_id,
b_s.event,
obj.owner blocked_owner,
obj.object_name blocked_name,
obj.object_type blocked_object_type,
CASE
WHEN b_s.row_wait_obj# <> -1 THEN
dbms_rowid.rowid_create（1,
obj.data_object_id,
b_s.row_wait_file#,
b_s.row_wait_block#,
b_s.row_wait_row#）
ELSE
"-1"
END blocked_rowid, --被阻塞数据的rowid
decode（obj.object_type,
"TABLE",
"select * from " || obj.owner || "." || obj.object_name ||
" where rowid=""" ||
dbms_rowid.rowid_create（1,
obj.data_object_id,
b_s.row_wait_file#,
b_s.row_wait_block#,
b_s.row_wait_row#） || """",
NULL） blocked_data_querysql
FROM gv$lock a,
gv$lock b,
gv$session a_s,
gv$session b_s,
dba_objects obj
WHERE a.id1 = b.id1
AND a.id2 = b.id2
AND a.block > 0 --阻塞了其他人
AND b.request > 0 --AND （（a.INST_ID=b.INST_ID AND a.SID<>b.SID） OR （a.INST_ID<>b.INST_ID ））
AND a.sid = a_s.sid
AND a.inst_id = a_s.inst_id
AND b.sid = b_s.sid
AND b.inst_id = b_s.inst_id
AND b_s.row_wait_obj# = obj.object_id（+）
ORDER BY a.inst_id, a.sid;更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址