The status of buffer locks can be checked through the table X$KJBL; it is not necessary to dump the locks with
the ORADEBUG command, in order to get to this information. The table contains the client and shadow locks.
Column name Data type Description KJBLLOCKP RAW,kjbl* Lock pointer. Can be joined with X$LE.LE_KJBL. KJBLGRANT VARCHAR2 (9) Current grant level, valid values are: KJUSERNL (0), KJUSERCR (1), KJUSERCW (2), KJUSERPR (3), KJUSERPW (4), KJUSEREX (5) KJBLREQUEST VARCHAR2 (9) Request Level, valid values are: (See KJBLGRANT) KJBLROLE NUMBER Granted Role: KJBL_GRANT_N (0x00), KJBL_GRANT_S (0x01) KJBL_GRANT_X (0x02), KJBL_REQ_S (0x04), KJBL_REQ_X (0x08), KJBL_REQ_STL (0x10), KJBL_REQ_CR (0x10), KJBL_OACK (0x20), KJBL_ROLE_G (0x40), KJBL_HAS_PI (0x80) KJBLRESP RAW, kjbr* Current Resource Info (Resource pointer) KJBLNAME VARCHAR2 (30) Resource name in Hex notation ([id1], [id2], [Type]) KJBLNAME2 VARCHAR2 (30) Resource name in Decimal notation (id1, id2, Type) KJBLQUEUE NUMBER Grant / Convert queue: Grant Queue (0x00), Convert Queue 0x80 KJBLLOCKST VARCHAR2 (64) Lock state: KJUSERGRANTED (0x00), KJUSEROPENING (0x01), KJUSERCLOSING (0x02), KJUSERCANCELLING (0x04), KJUSERCVTING (0x08), KJUSERSTANDALONE (0x10), KJUSERASTDELAYED (0x20), KJUSERMSGPENDING(0x40), KJUSERFROZEN (0x80) KJBLWRITING NUMBER Writing ·0, not writing; 1, writing KJBLREQWRITE Write requested · 0, no request; 1, write requested KJBLOWNER NUMBER Owning instance – node id KJBLMASTER NUMBER Master instance – node id KJBLBLOCKED NUMBER Blocked lock? – Checks if there is a converting lock KJBLBLOCKER NUMBER Blocking lock? - Checks if there is a lock held in an incompatible mode
The columns KJBLBLOCKER and KJBLBLOCKED in particular let you determine if you are waiting on
blocked lock or if you are blocking other sessions. With this table, it is also possible to determine where the
lock is mastered and the role of the lock.
Leave a Reply