Identifying Blocking Processes

Posted 7 April, 2006 - 13:41

This one is for database administrators with access to the V$LOCK view (figure 1.0).

Figure 1.0

SELECT 
  'SID ' 
  || lock1.sid 
  || ' is blocking SID ' 
  || lock2.sid AS blockers
FROM  v$lock lock1
INNER JOIN v$lock lock2 ON lock1.id1 = lock2.id1 AND lock1.id2 = lock2.id2
WHERE lock1.block   = 1
AND   lock2.request > 0

The blocker processes are those with block = 1. Upon further inspection, the ID1 and ID2 columns in conjunction with LMODE and TYPE can help determine whether a DML lock is in effect and, if so, the object_id of the objects involved. Beyond that, it's possible to join to V$SESSION to determine the components making up a extended ROWID (row_wait_obj#, row_wait_file#, row_wait_block# and row_wait_row#) via the DBMS_ROWID package. Given the rowid and the object, it's then fairly trivial to determine the row involved.