检查锁情况的三种方法

最新推荐文章于 2020-05-12 17:36:44 发布

weixin_30877181

最新推荐文章于 2020-05-12 17:36:44 发布

阅读量81

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/dongzhiquan/archive/2012/10/09/2717473.html

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';