column holder format a12
column waiter format a12
column object format a30

SELECT waitsess.osuser || '(' || waiter.sid || ')' waiter,
        waiter.type,
        case waiter.request
                when 0 then null
                when 1 then null
                when 2 then 'row-S'
                when 3 then 'row-X'
                when 4 then 'share'
                when 5 then 'sRowX'
                when 6 then 'excl'
        end req,
        holdsess.osuser  || '(' || holder.sid || ')' holder,
        case holder.lmode
                when 0 then null
                when 1 then null
                when 2 then 'row-S'
                when 3 then 'row-X'
                when 4 then 'share'
                when 5 then 'sRowX'
                when 6 then 'excl'
        end held,
        nvl(obj.object_name, waiter.id1 || ',' || waiter.id2) object
FROM sys.v_$lock holder
JOIN sys.v_$lock waiter
        ON (
                holder.id1 = waiter.id1
        AND     holder.id2 = waiter.id2
        AND     holder.type = waiter.type
        )
JOIN sys.v_$session holdsess
        ON (holdsess.sid = holder.sid)
JOIN sys.v_$session waitsess
        ON (waitsess.sid = waiter.sid)
LEFT OUTER JOIN sys.v_$lock tmhold
        ON (tmhold.type = 'TM' and tmhold.sid = holder.sid)
LEFT OUTER JOIN sys.v_$lock tmwait
        ON (tmwait.type = 'TM'
        AND tmwait.sid = waiter.sid)
LEFT OUTER JOIN dba_objects obj
        ON (obj.object_id = tmwait.id1)
WHERE holder.request = 0
AND holder.block = 1
AND waiter.request > 0
AND nvl(tmhold.id1,0) = nvl(tmwait.id1, 0)
/