Script to detect TX blocking locks

Following script detects TX blocking looks:

set linesize 160
set trimspool on
column name format A28
column type format A18
column sid format 9999
column block format 99
column ctime format 99999
column serial format 99999
column id1 format 99999999
column id2 format 99999999
column LMODE format A12
column request format A12
spool log.lock

select /*+ ordered use_merge(X$KSUSE X$KSQEQ) */
    l.ctime as ctime,
    l.sid,
    s.SERIAL# as serial,
    substr(s.USERNAME,1,8) as  username,
    substr(s.osuser,1,8) as osuser,
    substr(s.PROGRAM,1,15) as program,
    l.id1,
    l.id2,
    o.name,
    decode(l.type,
    'MR', 'MR, Media Recovery',
    'RT','RT, Redo Thread',
    'UN', 'UN, User Name',
    'TX', 'TX, Transaction',
    'TM', 'TM, DML',
    'UL', 'UL, PL/SQL User Lock',
    'DX', 'DX, Distributed Xaction',
    'CF', 'CF, Control File',
    'IS', 'IS, Instance State',
    'FS', 'FS, File Set',
    'IR', 'IR, Instance Recovery',
    'ST', 'ST, Disk Space Transaction',
    'TS', 'TS, Temp Segment',
    'IV', 'IV, Library Cache Invalidation',
    'LS', 'LS, Log Start or Switch',
    'RW', 'RW, Row Wait',
    'SQ', 'SQ, Sequence Number',
    'TE', 'TE, Extend Table',
    'TT', 'TT, Temp Table',
    l.type) as type,
    decode(lmode,0,'None',
        1,'Null',
        2,'Row-S(SS)',
        3,'Row-X(SX)',
        4,'Share',
        5,'S/Row-X(SSX)',
        6,'Exclusive',
        'Unkwon') as Lmode,
    decode(request,0,'None',
        1,'Null',
        2,'Row-S(SS)',
        3,'Row-X(SX)',
        4,'Share',
        5,'S/Row-X(SSX)',
        6,'Exclusive',
        'Unkwon') as request,
    l.block
from v$lock l, v$session s, sys.obj$ o
where l.sid = s.sid
and l.id1 = o.obj# (+)
and ( request <> 0 OR l.block =1)
order by l.sid,o.name
/


--
-- or run this to see who is blocking who as well as the object being blocked and sql
--
select o.name AS object, s1.username || '@' || s1.machine
  || ' ( SID=' || s1.sid || ' )  is blocking '
  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status, 
  sq1.sql_fulltext AS "Blocking SQL", sq2.sql_fulltext AS "Blocked SQL",
  l1.ctime AS "Blocking Time", l2.ctime AS "Blocked Time"
from v$lock l1, v$session s1, v$lock l2, v$session s2, v$sql sq1, v$sql sq2, sys.obj$ o
where s1.sid=l1.sid and s2.sid=l2.sid
  and l1.BLOCK=1 and l2.request > 0
  and l1.id1 = l2.id1
  and l2.id2 = l2.id2   
  and sq1.sql_id = s1.sql_id
  and sq2.sql_id = s2.sql_id
  and l1.id1 = o.obj# (+);