Show Blocked Sessions With Lock Info

For an overview of all blocked sessions in the database with the blockers and the database objects the locks are hold on, use the query below. It only shows detailed information about locks on database objects though.

select lpad(' ', (level-1)*2)||to_char(s.sid) sess
,      s.osuser
,      s.machine
,      ow.object_name "Wait Object"
,      oh.object_name "Hold Object"
,      s.lockwait
,      w.lock_type, w.mode_held, w.mode_requested, w.lock_id1, w.lock_id2
from   v$session s
  left outer join dba_waiters w
               on s.sid = w.holding_session
  left outer join dba_objects ow
               on s.row_wait_obj# = ow.object_id
  left outer join dba_objects oh
               on w.lock_id1 = oh.object_id
connect by prior sid = blocking_session
start with sid in (select holding_session from dba_waiters)
;