使用DBMS_ROWID获取被阻塞行的rowid

在使用v$session视图在查询会话的行锁的等待事件时，视图中提供了会话等待的对象号(ROW_WAIT_OBJ#)、文件号(ROW_WAIT_FILE#)、块号(ROW_WAIT_BLOCK#)和行号(ROW_WAIT_ROW#)但是如何使用这些信息定位出会话等待的是哪一行呢？答案就是使用DBMS_ROWID

打开两个会话同时更新同一条数据

#session 1zx@ORCL>select distinct sid from v$mystat;       SID----------        22zx@ORCL>zx@ORCL>update zx set name='zx' where id=1;1 row updated.#session 2zx@ORCL>select distinct sid from v$mystat;       SID----------       145       zx@ORCL>update zx set name='zx' where id=1;

此时session2会被session1阻塞，查询v$session会话145在等待enq: TX - row lock contention

zx@ORCL>col event for a40zx@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锁

zx@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等待哪个表的哪个行

zx@ORCL>col owner for a10zx@ORCL>col object_name for a10zx@ORCL>col rowid for a30zx@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.sid=145;OWNER      OBJECT_NAM rowid---------- ---------- ------------------------------ZX         ZX          AAAYWqAASAAADzTAAH--使用上面查询出的rowid查看数据，即为session2等待的行zx@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;