Below are the few queries to find out session which are blocking other sessions. First one is the general query which gives information regarding which session is blocking another session.
SELECT s1.username , s1.machine, ' ( SID=' || s1.sid || ' ) is blocking ',s2.username , s2.machine , ' ( SID=' || s2.sid || ' ) ' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.sid=l1.sid AND s2.sid=l2.sid
AND l1.BLOCK=1 AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2 ;
Query to find out the text of blocking session
Next query gives you the sql which is blocking your session. Replace <query_sid> with the session id of blocking session to get the sql.
select sql_text from v$sqltext where hash_value=( select prev_hash_value from v$session where sid=<query_sid>);
Query to find out the machine name and username of blocking session
This is the query which gives you the username and machine which is blocking your session. Replace <query_sid> with the session id of blocking session to get the details.
select sid||' - '||serial#||' - '||osuser||' - '||username||' - '||machine||' - '||status||' - '||logon_time
from v$session where sid=<query_sid>;
Query to find out holder and waiter session
This one tells you all the information related to holder and waiter sessions. It tells you the mode in which the session is being holded/waiting.
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;