Use DBMS_ROWID to get the rowid of a blocked row
When using the v$session view to query the wait event of a row lock for 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_ROW#) of the session wait, 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 data at the same time
# session 1zx@ORCL > select distinct sid from venermystats; SID- 22zx@ORCL > zx@ORCL > update zx set name='zx' where id=1;1 row updated.#session 2zx@ORCL > select distinct sid from vampmystats; SID- 145zx@ORCL > update zx set name='zx' where id=1
At this point, session2 will be blocked by session1. Query v$session session 145 is waiting for 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# Enq: TX-row lock contention 99754 18 15571 7
Query the TX lock of v$lock confirmation session 145 in the request session 22
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
Use the following statement to query which table which row session 145 is waiting for
Zx@ORCL > col owner for a10zx@ORCL > col object_name for a10zx@ORCL > col rowid for a30zx@ORCL > select b.ownerdirection b.objectpapernamedirector dbmsroomrowidds. Rowidgets create (1. From v$session. ROW.WAITITZOBJOBJOBJLING. OWNER OBJECT_NAM rowid- ZX ZX AAAYWqAASAAADzTAAH-- uses the rowid queried above to view the data, that is, the session2 waiting line zx@ORCL > 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 blocking relationships 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, 'followed by 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_row#) ELSE'- 1' END blocked_rowid,-- rowid decode (obj.object_type, 'TABLE') of blocked data '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_row#) | |', NULL) blocked_data_querysql FROM gv$lock a, gv$lock b, gv$session astats, gv$session bins Dba_objects obj WHERE a.id1 = b.id1 AND a.id2 = b.id2 AND a.block > 0-blocking others AND b.request > 0-- AND ((a.INST_ID=b.INST_ID AND a.SIDb.SID) OR (a.INST_IDb.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
