Below is a query that list all the blocking sessions in an oracle instance
WITH sessions AS (SELECT username,sid,sql_id FROM gv$session), locks AS (SELECT * FROM gv$lock) SELECT l2.type,s1.username blocking_user, s1.sid blocking_sid, s2.username blocked_user, s2.sid blocked_sid, sq.sql_text FROM locks l1 JOIN locks l2 USING (id1, id2) JOIN sessions s1 ON (s1.sid = l1.sid) JOIN sessions s2 ON (s2.sid = l2.sid) LEFT OUTER JOIN v$sql sq ON (sq.sql_id = s2.sql_id) WHERE l1.BLOCK = 1 AND l2.request > 0;