Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SELECT /*+ ORDERED */
sys_s.r_object_id,
sys_s.r_object_type,
blocked_sid,
blocked_sql_text,
blocking_sid,
blocking_port,
blocking_machine,
blocking_last_sql_text,
blocking_status
FROM (SELECT s.sid AS blocked_sid,
s.serial# AS blocked_serial#,
s.username AS blocker_username,
s.module AS blocked_module,
DBMS_ROWID.rowid_create (1,
(SELECT data_object_id
FROM dba_objects
WHERE object_id = s.row_wait_obj#),
s.row_wait_file#,
s.row_wait_block#,
s.row_wait_row#)
AS blocked_rowid,
s.sql_hash_value AS blocked_has_value,
s.sql_address AS blocked_sql_address,
sq.sql_text AS blocked_sql_text,
o.sid AS blocking_sid,
o.port AS blocking_port,
o.machine AS blocking_machine,
o.status AS blocking_status,
os.sql_text AS blocking_last_sql_text
FROM v$session_wait sw,
v$session s,
v$lock l,
v$sql sq,
v$session o,
v$sql os
WHERE 1 = 1
AND sw.event = 'enq: TX - row lock contention'
AND sw.sid = s.sid
AND l.TYPE = 'TX'
AND l.request = 6
AND l.sid = s.sid
AND s.sql_hash_value = sq.hash_value
AND s.sql_address = sq.address
AND s.blocking_session = o.sid
AND os.sql_id = NVL (o.sql_id, o.prev_sql_id)) locks,
dm_sysobject_s sys_s
WHERE sys_s.ROWID = locks.blocked_rowid;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.