Поиск блокировок.

Смотрим ожидания сессий:

select s.username, w.event, s.sid, s.blocking_session,s2.module,s2.action
  from v$session_wait w, v$session s, v$session s2
 where w.sid = s.sid
   and s.blocking_session is not null
   and s.blocking_session =s2.sid
 order by w.event,s.sid;

Смотрим кто-кого ждет:

SELECT /*+ rule*/ DECODE(a.request,0,'Holder: ','Waiter: ')||a.sid sess, 
a.id1, a.id2, a.lmode, a.request, a.type,b.action,b.program,b.module
FROM V$LOCK a,v$session b
WHERE (a.id1, a.id2, a.type) IN
      (SELECT id1, id2, type FROM V$LOCK WHERE request>0) and a.sid=b.sid
ORDER BY id1, request ;