doc

    Name:    tranlock.sql

    Author:  Mark Gurry

    This script shows the transactions that are experiencing lock
    contention.

#

ttitle 'Transactions Experiencing Lock Contention'
  select decode(l1.lmode,  2, 'Row-S(SS)', 3,'Row-X(SX)', 
                           4, 'Share', 5, 'S/Row-X(SSX)',
                           6, 'Exclusive', 'Other') Mode_Held,
            decode(l1.request,  2, 'Row-S(SS)', 3,'Row-X(SX)', 
                                4, 'Share', 5, 'S/Row-X(SSX)',
                                6, 'Exclusive', 'Other') Lock_Requested,
            l1.id1, l1.id2
   from v$lock l1
 where type = 'TX'
    and   (l1.id1, l1.id2) in
              (select l2.id1, l2.id2 
                  from v$lock l2
                    and l1.id1 = l2.id1
                    and l1.id2 = l2.id2
                    and l2.request > 0);