Re: enqueue wait events, locks and the meaning of id1/id2

From: Bricklen <bricklen-rem_at_yahoo.comz>
Date: Tue, 27 Jan 2004 18:33:16 GMT
Message-ID: <M9yRb.33214$oj2.19356@edtnps89>

Bjorn Augestad wrote:

> Hello,
>
> Statspack telles me that my Oracle instance (8.0.5) has lots of enqueue
> wait events and each event is quite costly, averaging 2200 ms.
>
> The enqueue lock type is TX, with lock mode 6 (exclusive).
>
> I'm trying to figure out the names of the objects locked, but cannot
> find any documentation on how to interpret v$lock.id1 and id2 when the
> lock type is TX. Can anyone please help?
>
> Thanks in advance.
> Bjørn
>
>

Maybe this will help a bit:

SELECT a.object_name,

        a.owner,
        Decode(b.locked_mode, 0, 'None',
                              1, 'Null (NULL)',
                              2, 'Row-S - Sub Share (SS)',
                              3, 'Row-X - Sub Exclusive (SX)',
                              4, 'Share (S)',
                              5, 'S/Row-X - Share/Sub Exclusive (SSX)',
                              6, 'Exclusive (X)',
                              b.locked_mode) locked_mode,
        b.session_id sid,
        b.oracle_username,
        b.os_user_name
FROM   all_objects a,
        v$locked_object b

WHERE a.object_id = b.object_id
ORDER BY 1; Received on Tue Jan 27 2004 - 12:33:16 CST