选定被锁定的表

SQL> SELECT S.SID SESSION_ID,s.MACHINE,s.SERIAL#,
  2          S.USERNAME,
  3          DECODE(LMODE,
  4                 0,
  5                  'None',
  6                 1,
  7                 'Null',
  8                 2,
  9                 'Row-S (SS)',
 10                 3,
 11                 'Row-X (SX)',
 12                 4,
 13                 'Share',
 14                 5,
 15                 'S/Row-X (SSX)',
 16                 6,
 17                 'Exclusive',
 18                 TO_CHAR(LMODE)) MODE_HELD,
 19          DECODE(REQUEST,
 20                 0,
 21                 'None',
 22                 1,
 23                 'Null',
 24                 2,
 25                 'Row-S (SS)',
 26                 3,
 27                 'Row-X (SX)',
 28                 4,
 29                 'Share',
 30                 5,
 31                 'S/Row-X (SSX)',
 32                 6,
 33                 'Exclusive',
 34                 TO_CHAR(REQUEST)) MODE_REQUESTED,
 35          O.OWNER || '.' || O.OBJECT_NAME || ' (' || O.OBJECT_TYPE || ')' AS 被锁的表,S.TYPE LOCK_TYPE,
 36          L.ID1 LOCK_ID1,
 37          L.ID2 LOCK_ID2
 38     FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S
 39    WHERE L.SID = S.SID
 40      AND L.ID1 = O.OBJECT_ID;

SESSION_ID MACHINE                                                             SERIAL# USERNAME                       MODE_HELD                                MODE_REQUESTED                           被锁的表                                                                         LOCK_TYPE    LOCK_ID1   LOCK_ID2
---------- ---------------------------------------------------------------- ---------- ------------------------------ ---------------------------------------- ---------------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ----------
         2 KJC03                                                                     1                                Share                                    None                                     SYS.C_OBJ# (CLUSTER)                                                             BACKGROUND          2          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.I_OBJ# (INDEX)                                                               BACKGROUND          3          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.TAB$ (TABLE)                                                                 BACKGROUND          4          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.CLU$ (TABLE)                                                                 BACKGROUND          5          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.C_TS# (CLUSTER)                                                              BACKGROUND          6          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.I_TS# (INDEX)                                                                BACKGROUND          7          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.C_FILE#_BLOCK# (CLUSTER)                                                     BACKGROUND          8          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.I_FILE#_BLOCK# (INDEX)                                                       BACKGROUND          9          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.C_USER# (CLUSTER)                                                            BACKGROUND         10          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.I_USER# (INDEX)                                                              BACKGROUND         11          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.FET$ (TABLE)                                                                 BACKGROUND         12          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.UET$ (TABLE)                                                                 BACKGROUND         13          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.SEG$ (TABLE)                                                                 BACKGROUND         14          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.UNDO$ (TABLE)                                                                BACKGROUND         15          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.TS$ (TABLE)                                                                  BACKGROUND         16          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.FILE$ (TABLE)                                                                BACKGROUND         17          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.OBJ$ (TABLE)                                                                 BACKGROUND         18          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.IND$ (TABLE)                                                                 BACKGROUND         19          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.JOB$ (TABLE)                                                                 BACKGROUND        201          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.ICOL$ (TABLE)                                                                BACKGROUND         20          0

SESSION_ID MACHINE                                                             SERIAL# USERNAME                       MODE_HELD                                MODE_REQUESTED                           被锁的表                                                                         LOCK_TYPE    LOCK_ID1   LOCK_ID2
---------- ---------------------------------------------------------------- ---------- ------------------------------ ---------------------------------------- ---------------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ----------
         2 KJC03                                                                     1                                Share                                    None                                     SYS.I_JOB_JOB (INDEX)                                                            BACKGROUND        202          0
         2 KJC03                                                                     1                                Share                                    None                                     SYS.COL$ (TABLE)                                                                 BACKGROUND         21          0
         4 KJC03                                                                     1                                Null                                     None                                     SYS.TAB$ (TABLE)                                                                 BACKGROUND          4          0
         5 KJC03                                                                     1                                Row-X (SX)                               None                                     SYS.C_OBJ# (CLUSTER)                                                             BACKGROUND          2          1

24 rows selected

模拟表锁定

SQL> select * from a for update;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
         2          1         32  104857600          1 YES      INACTIVE                865718 2005-2-23 1
         3          1         33  104857600          1 NO       CURRENT                 909949 2005-2-23 1
         1                                                                                    

SQL>

查询被锁定的表

SQL> /

SESSION_ID MACHINE                 SERIAL# USERNAME   MODE_HELD  MODE_REQUE 被锁的表                       LOCK_TYPE    LOCK_ID1   LOCK_ID2
---------- -------------------- ---------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ----------
         2 KJC03                         1            Share      None       SYS.C_OBJ# (CLUSTER)           BACKGROUND          2          0
         2 KJC03                         1            Share      None       SYS.I_OBJ# (INDEX)             BACKGROUND          3          0
         2 KJC03                         1            Share      None       SYS.TAB$ (TABLE)               BACKGROUND          4          0
         2 KJC03                         1            Share      None       SYS.CLU$ (TABLE)               BACKGROUND          5          0
         2 KJC03                         1            Share      None       SYS.C_TS# (CLUSTER)            BACKGROUND          6          0
         2 KJC03                         1            Share      None       SYS.I_TS# (INDEX)              BACKGROUND          7          0
         2 KJC03                         1            Share      None       SYS.C_FILE#_BLOCK# (CLUSTER)   BACKGROUND          8          0
         2 KJC03                         1            Share      None       SYS.I_FILE#_BLOCK# (INDEX)     BACKGROUND          9          0
         2 KJC03                         1            Share      None       SYS.C_USER# (CLUSTER)          BACKGROUND         10          0
         2 KJC03                         1            Share      None       SYS.I_USER# (INDEX)            BACKGROUND         11          0
         2 KJC03                         1            Share      None       SYS.FET$ (TABLE)               BACKGROUND         12          0
         2 KJC03                         1            Share      None       SYS.UET$ (TABLE)               BACKGROUND         13          0
         2 KJC03                         1            Share      None       SYS.SEG$ (TABLE)               BACKGROUND         14          0
         2 KJC03                         1            Share      None       SYS.UNDO$ (TABLE)              BACKGROUND         15          0
         2 KJC03                         1            Share      None       SYS.TS$ (TABLE)                BACKGROUND         16          0
         2 KJC03                         1            Share      None       SYS.FILE$ (TABLE)              BACKGROUND         17          0
         2 KJC03                         1            Share      None       SYS.OBJ$ (TABLE)               BACKGROUND         18          0
         2 KJC03                         1            Share      None       SYS.IND$ (TABLE)               BACKGROUND         19          0
         2 KJC03                         1            Share      None       SYS.JOB$ (TABLE)               BACKGROUND        201          0
         2 KJC03                         1            Share      None       SYS.ICOL$ (TABLE)              BACKGROUND         20          0

SESSION_ID MACHINE                 SERIAL# USERNAME   MODE_HELD  MODE_REQUE 被锁的表                       LOCK_TYPE    LOCK_ID1   LOCK_ID2
---------- -------------------- ---------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ----------
         2 KJC03                         1            Share      None       SYS.I_JOB_JOB (INDEX)          BACKGROUND        202          0
         2 KJC03                         1            Share      None       SYS.COL$ (TABLE)               BACKGROUND         21          0
         4 KJC03                         1            Null       None       SYS.TAB$ (TABLE)               BACKGROUND          4          0
         5 KJC03                         1            Row-X (SX) None       SYS.C_OBJ# (CLUSTER)           BACKGROUND          2          1
        26 MSHOME/LUXUEZHU             163 SYS        Row-S (SS) None       SYS.A (TABLE)                  USER            35133          0

25 rows selected

SQL>