Still a work in progress, but so far:
WITH block_seq (lock_id1, lock_id2, lock_type, depth, holding_session, waiting_session, session_list) AS ( SELECT lock_id1, lock_id2, lock_type, 1 AS depth, holding_session, waiting_session, holding_session || ' -> ' || waiting_session AS session_list FROM dba_waiters WHERE mode_held = mode_requested GROUP BY lock_id1, lock_id2, lock_type, holding_session, waiting_session UNION ALL SELECT a.lock_id1, a.lock_id2, a.lock_type, a.depth + 1, b.holding_session, b.waiting_session, a.session_list || ' -> ' || b.waiting_session FROM block_seq a JOIN dba_waiters b ON b.lock_id1 = a.lock_id1 AND b.lock_id2 = a.lock_id2 AND b.holding_session = a.waiting_session AND b.holding_session != b.waiting_session ), depth_discard AS ( SELECT lock_id1 AS slot, lock_id2 AS seq, lock_type, depth, holding_session, waiting_session, session_list, MAX(depth) OVER (PARTITION BY lock_id1, lock_id2, lock_type) max_depth FROM block_seq ) SELECT lock_type, slot, seq, depth, session_list FROM depth_discard WHERE depth = max_depth;