.
\nWhen you have sessions blocked on locks, you probably have all information about the waiters (they call you and anyway their waiting session is visible in v$session our ASH). But you usually need to get enough information that help to identify the blocker.<\/p>\n
Here is a query I use to get that quickly, based on V$WAIT_CHAINS<\/p>\n
Here is the result I want to get:<\/p>\n
session wait event minutes USER PRO\n----------------------- ----------------------------------------------- ------- ---- ---\n ABCLBP1 '831,54109@1' SQL*Net message from client 13.5 SYS sql\n ABCLBP4 '395,21891@4' enq: TX - row lock contention on TABLE 13.2 SYS SQL\n \"SYS\".\"TEST_FRANCK\" on rowid AAC0aCAAnAAABSCAAA\n\n<\/pre>\nI have information about blocking session, waiting session, the type of lock (here TX – row lock) and because it is a row lock I want to know the ROWID of the locked row.<\/p>\n
Here is the query I used to get it:<\/p>\n
column \"wait event\" format a50 word_wrap\ncolumn \"session\" format a25\ncolumn \"minutes\" format 9999D9\ncolumn CHAIN_ID noprint\ncolumn N noprint\ncolumn l noprint\nwith w as (\nselect\n chain_id,rownum n,level l\n ,lpad(' ',level,' ')||(select instance_name from gv$instance where inst_id=w.instance)||' '''||w.sid||','||w.sess_serial#||'@'||w.instance||'''' \"session\"\n ,lpad(' ',level,' ')||w.wait_event_text ||\n case\n when w.wait_event_text like 'enq: TM%' then\n ' mode '||decode(w.p1 ,1414332418,'Row-S' ,1414332419,'Row-X' ,1414332420,'Share' ,1414332421,'Share RX' ,1414332422,'eXclusive')\n ||( select ' on '||object_type||' \"'||owner||'\".\"'||object_name||'\" ' from all_objects where object_id=w.p2 )\n when w.wait_event_text like 'enq: TX%' then\n (\n select ' on '||object_type||' \"'||owner||'\".\"'||object_name||'\" on rowid '\n ||dbms_rowid.rowid_create(1,data_object_id,relative_fno,w.row_wait_block#,w.row_wait_row#)\n from all_objects ,dba_data_files where object_id=w.row_wait_obj# and w.row_wait_file#=file_id\n )\n end \"wait event\"\n , w.in_wait_secs\/60 \"minutes\"\n , s.username , s.program\n from v$wait_chains w join gv$session s on (s.sid=w.sid and s.serial#=w.sess_serial# and s.inst_id=w.instance)\n connect by prior w.sid=w.blocker_sid and prior w.sess_serial#=w.blocker_sess_serial# and prior w.instance = w.blocker_instance\n start with w.blocker_sid is null\n)\nselect * from w where chain_id in (select chain_id from w group by chain_id having max(\"minutes\") >= 1 and max(l)>1 )\norder by n\n\/\n<\/pre>\nThis query retrieves the wait chains where a session is waiting for more than one minute on a table lock (TM) or row lock (TX) .<\/p>\n
When it is a table lock (TM), I get the locked object_id from the P2 parameter, in order to know the table name.<\/p>\n
When it is a row lock, I get the table and rowid from V$SESSION. Note that I have to join with dba_data_files in order to convert the absolute file_id to a relative one, and to join to dba_objects in order to convert the object_id to the data_object_id one – in order to built the ROWID.<\/p>\n