检查锁情况的三种方法

 

1.The v$locked_object View

The columns of this view are:

•XIDUSN: Rollback segment number

•OBJECT_ID: ID of the object being modified

•SESSION_ID: ID of the session locking the object

•ORACLE_USERNAME

•LOCKED_MODE

Example

To find the table name that corresponds to a particular object ID in the v$locked_object view:

SQL> SELECT xidusn, object_id, session_id, locked_mode

 2 FROM v$locked_object;

   XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE

--------- --------- ---------- -----------
        3      2711          9           3
        0      2711          7           3

SQL> SELECT object_name FROM dba_objects

 2 WHERE object_id = 2711;

OBJECT_NAME
-------------
EMPLOYEES

If the value of xidusn is 0, then the session with the corresponding session ID is requesting and waiting for the lock being held by the session, for which xidusn value is different from 0.

2.The utllockt.sql Script

You can also use the utllockt.sql script to display lock wait-for in a hierarchy. The script prints the sessions that are waiting for locks and the sessions that are blocking.

You must run the catblock.sql script (found in $ORACLE_HOME/rdbms/admin folder) as a sysdba user before using utllockt.sql. The catblock.sql script creates the dba_locksand dba_blockers views along with others that will be used by utllockt.sql.

For example, in the following output session 9 is waiting for session 8, sessions 7 and 10 are waiting for 9.

WAITING TYPE MODE           MODE            LOCK LOCK
SESSION      REQUESTED      HELD            ID1   ID2

------- ---- ------------- -------------   ----- -----
      8 NONE None           None                  0   0
      9 TX   Share (S)      Exclusive (X)     65547 16

      7 RW   Exclusive (X) S/Row-X (SSX)   33554440 2

     10 RW   Exclusive (X) S/Row-X (SSX)   33554440 2

 

3.The v$lock View

Two of the columns in this view are type and id1. These columns have the values:

Lock typeID1

TXRollback segment number and slot number

TMObject ID of the table being modified

Any process that is blocking others is likely to be holding a lock obtained by a user application. The locks acquired by user applications are:

•Table locks (TM)

•Row-level locks (TX)

To find the table name that corresponds to a particular resource ID 1 of the v$lock view:

SQL> SELECT object_name

 2 FROM dba_objects, v$lock

 3 WHERE object_id=id1 AND type='TM';