SQL statement to show the holder and waiter session information

SELECT    s.SID SID, s.username uname,
          DECODE (l.TYPE, 'TX', 'TX', 'UL', 'USR', 'SYS') ltype,
          DECODE (l.lmode,
                  1, 'Null',
                  2, 'Row-S',
                  3, 'Row-X',
                  4, 'Share',
                  5, 'S/Row-X',
                  6, 'Exclusive'
                 ) lmode,
           DECODE (l.request, 0, 'No', 'Yes') blocked,
           DECODE (l.request,
                  0, NULL,
                  'Waiting on session '
                  || TO_CHAR (b.SID)
                  || ' (' || s3.program || ')'
                 ) details,
           s.paddr, s2.sql_text, (b.ctime / 60) waiting
    FROM v$session s, v$lock l, v$lock b, v$sql s2, v$session s3, v$process p
   WHERE s.username = 'APPS'
     AND s.SID = l.SID
     AND l.TYPE != 'TM'
     AND l.id1 = b.id1(+)
     AND b.request(+) = 0
     AND l.request = 6
     AND b.SID = s3.SID(+)
     AND p.addr(+) = s3.paddr
     AND s2.hash_value = s.sql_hash_value(+)
ORDER BY waiting DESC