Detected sessions waiting for lock and the session holding the lock.

lock_waiting_sessions.sql

SQL> SELECT gvs.inst_id,DECODE (request, 0, 'Holder: ', 'waiter:') || gvl.sid     sess,
         status,
         id1,
         id2,
         lmode,
         request,
         gvl.TYPE
    FROM gv$lock gvl, gv$session gvs
   WHERE     (id1, id2, gvl.TYPE) IN (SELECT id1, id2, TYPE
                                        FROM gv$lock
                                       WHERE request > 0)
         AND gvl.sid = gvs.sid and gvl.inst_id=gvs.inst_id
ORDER BY id1, request;