SELECT  L.SESSION_ID                                                    AS SID
,       CASE 
            WHEN L.LOCK_TYPE = 'TRANSACTION'    THEN 'TX'
            WHEN L.LOCK_TYPE = 'DML'            THEN 'TM' 
        END                                                             AS TYPE
,       L.MODE_HELD
,       L.MODE_REQUESTED                                                AS MOD_REQD
,       CASE
            WHEN L.LOCK_TYPE = 'TRANSACTION' THEN TO_CHAR(TRUNC(LOCK_ID1/POWER(2, 16)))
            WHEN L.LOCK_TYPE = 'DML' THEN (SELECT O.OBJECT_NAME
                                            FROM DBA_OBJECTS O
                                            WHERE O.OBJECT_ID = L.LOCK_ID1)
        END                                                             AS "USN/TABLE"
,       CASE
            WHEN L.LOCK_TYPE = 'TRANSACTION' THEN BITAND(L.LOCK_ID1, TO_NUMBER('FFFF', 'XXXX')) + 0
        END                                                             AS SLOT
,       CASE
            WHEN L.LOCK_TYPE = 'TRANSACTION' THEN TO_NUMBER(L.LOCK_ID2)
        END                                                             AS SQN
,       CASE 
            WHEN L.BLOCKING_OTHERS = 'BLOCKING' THEN ' <<<<<'
        END                                                             AS BLOCKING
FROM DBA_LOCK L
WHERE L.LOCK_TYPE IN ('TRANSACTION', 'DML')
ORDER BY L.SESSION_ID, L.LOCK_TYPE, L.LOCK_ID2;