使用DBMS_ROWID獲取被阻塞行的rowid

在使用v$session視圖在查詢會話的行鎖的等待事件時，視圖中提供了會話等待的對象號(ROW_WAIT_OBJ#)、文件號(ROW_WAIT_FILE#)、塊號(ROW_WAIT_BLOCK#)和行號(ROW_WAIT_ROW#)但是如何使用這些信息定位出會話等待的是哪一行呢？答案就是使用DBMS_ROWID

打開兩個會話同時更新同一條數據

#session 1
zx@ORCL>select distinct sid from v$mystat;

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

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

1 row updated.

#session 2
zx@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 a40
zx@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 a10
zx@ORCL>col object_name for a10
zx@ORCL>col rowid for a30
zx@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等待的行
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;