Oracle Lock Order

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
  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;