Find Block Session

select l1.sid, ' IS BLOCKING ', l2.sid  from v$lock l1, v$lock l2
 where l1.block =1 and l2.request > 0  and l1.id1=l2.id1  and l1.id2=l2.id2;


SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess , id1, id2, lmode,request, type
FROM v$lock
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM v$lock WHERE request>0)
ORDER BY id1, request;