--

-- Show Historical TM Contention

--

SET PAUSE ON

SET PAUSE 'Press Return to Continue'

SET PAGESIZE 60

SET LINESIZE 300

SELECT

    sw.SESSION_ID,

    sw.SESSION_SERIAL#,

    sw.user_id,

    sw.program,

    sw.event,

    sw.wait_time,

    sw.time_waited,

    sw.p1,

    sw.p2 AS object_id,

    sw.p3,

    -- Decode lock mode from P1

    DECODE(

        BITAND(sw.p1, 255),

        1, 'Null',

        2, 'RS (Row Share)',

        3, 'RX (Row Exclusive)',

        4, 'S (Share)',

        5, 'SRX (Share Row Exclusive)',

        6, 'X (Exclusive)',

        'Unknown'

    ) AS lock_mode_requested,

    o.owner,

    o.object_name,

    o.object_type

FROM

    dba_hist_active_sess_history sw

    JOIN dba_objects o ON sw.p2 = o.object_id

WHERE

    sw.event = 'enq: TM - contention'

AND

    sw.sample_time

BETWEEN

    to_date('2026-06-15 23:00:00','yyyy-mm-dd hh24:mi:ss')

AND

    to_date('2026-06-16 04:00:00', 'yyyy-mm-dd hh24:mi:ss')

ORDER BY

        sw.time_waited;

/