Using Dbms_rowid to get the rowid of a blocked row

When you use the V$session view to query a wait event for a row lock on a session, the view provides the object number (row_wait_obj#), file Number (row_wait_file#), block number (row_wait_block#), and line number (row_wait) that the session waits for _row#) But how do you use this information to locate which line the session is waiting for? The answer is to use Dbms_rowid.

Open two sessions and update the same piece of data at the same time

#session 1[email protected]>select distinct SID from V$mystat; SID----------22[email protected]>[email protected]>update ZX set name= ' ZX ' where id=1;1 row updated. #session 2[       Email protected]>select distinct SID from V$mystat; SID----------145 [email protected]>update ZX Set name= ' ZX ' where id=1;

At this time Session2 will be blocked by Session1, query v$session session 145 is waiting for Enq:tx-row lock contention

[Email protected]>col event for A40[email Protected]>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

Query V$lock confirm session 145 in Request session 22 for TX lock

[Email protected]>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    022 tm        99754      0  3    022  tx     4390915    581  6    0        145 TM       99754       0  3    0        145 TX     4390915    581  0    6        145 AE 100      0  4     0

Use the following statement to query which row of the table the session 145 waits for

[Email protected]>col owner for A10[email protected]>col object_name for A10[email protected]>col rowid for A30[e Mail Protected]>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;O Wner object_nam rowid--------------------------------------------------ZX ZX aaaywqaasaaadztaah--use the ROWID search above Look at the data, which is the line waiting for Session2 [email protected]>select * from zx.zx where rowid= ' Aaaywqaasaaadztaah '; ID NAME------------------ ----------------------1 ZX

Official Document: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_rowid.htm#ARPLS053

Use the following statement to find the blocking relationship between sessions

select  (' node '  | |  a.inst_id | |   '  session  '  | |  a.sid | |   ', '  | |  a_s.serial# | |         ' blocked node '  | |  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,         ' Post-blocked information '  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       of blocked data  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 --blocked others    AND b.request > 0 --AND  (a.inst_id =B.INST_ID&NBSP;AND&NBSP;A.SID&LT;&GT;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 &NBSP;&NBSP;&NBSP;AND&NBSP;B_S.ROW_WAIT_OBJ#&NBSP;=&NBSP;OBJ.OBJECT_ID (+)  order by a.inst_id,  a.sid;

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1896136

Using Dbms_rowid to get the rowid of a blocked row