合 Oracle新建或重建索引过程中的锁信息

2022年8月13日

0 426 2

👉 本文共约4364个字，系统预计阅读时间或需17分钟。

简介
实验
创建或重建索引会阻塞DML操作
Oracle 11g下ONLINE选项不会堵塞DML操作
Oracle 10g下ONLINE选项会堵塞DML操作
实验10.2.0.1.0
实验11.2.0.3.0
利用10704和10046跟踪锁
10g
create index
alter index ... rebuild
create index ... online
alter index ... rebuild online
11g
create index
alter index ... rebuild
create index ... online
alter index ... rebuild online
实验SQL
查询锁用到的SQL语句
扩展

简介

可以利用10704和10046事件跟踪新建或重建索引过程中的锁信息，命令为：

alter session set events '10704 trace name context forever,level 10';
alter session set events '10046 trace name context forever,level 12';

1 2	alter session set events '10704 trace name context forever,level 10'; alter session set events '10046 trace name context forever,level 12';

新建或重建索引的锁信息如下图所示：

不带ONLINE的新建或重建索引的SQL语句获取的是4级TM锁，它会阻塞任何DML操作。

在Oracle 10g中，带ONLINE的新建或重建索引的SQL语句在开始和结束的时候获取的是4级TM锁，而在读取表数据的过程中获取的是2级TM锁，所以，在Oracle 10g中，即使加上ONLINE也会阻塞其它会话的DML操作。

在Oracle 11g中，带ONLINE的新建或重建索引的SQL语句在整个执行过程中获取的是2级TM锁，并不会阻塞其它会话的DML操作，但是在创建或重建索引的过程中，其它的会话产生的事务会阻塞索引的创建或重建操作，所以必须结束其它会话的事务才能让创建或重建索引的操作完成。所以应该避免在业务高峰期创建索引。

在Oracle 11g带ONLINE的新建或重建索引的情况下：

①　过程中会持有OD（ONLINE DDL）、DL（Direct Loader Index Creation）两种类型的锁，在Oracle 10g下只有DL锁没有OD锁。

②　表级锁TM的持有模式为2级RS（Row Share）与3级RX（Row Exclusive）类型的锁互相兼容，因此不会在表级发生阻塞。

③　阻塞发生在行级锁申请阶段，即请求的4级S（Share）类型的锁与执行DML的会话已经持有的6级X（Exclusive）锁之间存在不兼容的情况；相比非ONLINE方式的表级锁，锁的粒度上更加细化，副作用更小。

④　新增以“SYSJOURNAL”为前缀的IOT表，记录与索引创建动作同时进行的其它DML操作修改过的记录，等到索引创建完成前将IOT表里的记录合并至索引中并删除IOT表。

实验

创建或重建索引会阻塞DML操作

版本：11.2.0.3

首先建表T_INDEX_161113并插入很多数据
SYS@oratest S1> CREATE TABLE T_INDEX_161113 AS SELECT * FROM DBA_OBJECTS;

Table created.

SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;

75349 rows created.

SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;

150698 rows created.

SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;

301396 rows created.

SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;

602792 rows created.

SYS@oratest S1> COMMIT;

Commit complete.
接着再在该表上创建一个索引
SYS@oratest S1> CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME);

在创建索引的同时，在会话2上插入一条记录：
SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;

====>>>>> 产生了阻塞
在创建索引的同时，查询相关锁的信息：
SQL> SELECT SID,
  2         A.BLOCKING_SESSION,
  3         EVENT,
  4         A.P1,
  5         A.P2,
  6         A.P3,
  7         CHR(BITAND(P1, -16777216) / 16777215) ||
  8         CHR(BITAND(P1, 16711680) / 65535) "LOCK",
  9         BITAND(P1, 65535) "MODE",
 10         (SELECT OBJECT_NAME FROM DBA_OBJECTS D WHERE D.OBJECT_ID = A.P2) OBJECT_NAME
 11    FROM GV$SESSION A
 12   WHERE A.SID=141;

       SID BLOCKING_SESSION EVENT                           P1         P2         P3 LOCK       MODE OBJECT_NAME   
---------- ---------------- ----------------------- ---------- ---------- ---------- ---- ---------- ----------------
       142               21 enq: TM - contention    1414332419      77629          0 TM            3 T_INDEX_161113

SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (21,142) AND A.TYPE IN ('TX','TM');
ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007F44001842E0 00007F4400184340        142 TM      77629          0          0          3          2          0
00007F44001842E0 00007F4400184340         21 TM      77629          0          4          0          3          1
00007F44001842E0 00007F4400184340         21 TM         18          0          3          0          3          0
0000000076273C58 0000000076273CD0         21 TX      65567        846          6          0          3          0

SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (21, 142);
SESSION_ID OWNER     NAME                 MODE_HELD     MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- --------- -------------------- ------------- ------------- ------------ --------------------
       142 SYS       T_INDEX_161113       None          Row-X (SX)               2 Not Blocking
        21 SYS       T_INDEX_161113       Share         None                     3 Blocking
        21 SYS       OBJ$                 Row-X (SX)    None                     3 Not Blocking

SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE
  2    FROM DBA_OBJECTS D
  3   WHERE D.OBJECT_ID IN (18, 77629);

OWNER      OBJECT_NAME             OBJECT_ID OBJECT_TYPE
---------- ---------------------- ---------- -------------------
SYS        T_INDEX_161113              77629 TABLE
SYS        OBJ$                           18 TABLE
SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (21, 142) AND D.name NOT IN ('STANDARD','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','UTL_RAW','DBMS_APPLICATION_INFO','SDO_GEOR_DEF','SQL_TXT','DBMS_ASSERT','SDO_GEOR_DEF','TRACE_PUT_LINE','PLITBLM','DICTIONARY_OBJ_TYPE','DDLREPLICATION','DBMS_STANDARD','DBMS_APPLICATION_INFO','UTL_FILE','DDLAUX','DBMS_ASSERT','STANDARD','UTL_RAW','DDLREPLICATION','UTL_FILE','DDLAUX','GGS_MARKER_SEQ','DATABASE','LOGIN_USER','FILTERDDL','DBMS_UTILITY','GGS_DDL_SEQ','SYSEVENT','DBMS_UTILITY','LOGIN_USER','UTL_FILE','DATABASE','SDO_GEOR_DEF','UTL_RAW','GGS_DDL_SEQ','SDO_GEOR_DEF','DICTIONARY_OBJ_TYPE','UTL_RAW','DDLREPLICATION','DBMS_UTILITY','SYSEVENT','IS_VPD_ENABLED','DBMS_APPLICATION_INFO','FILTERDDL','DDLREPLICATION','STANDARD','DDLAUX','GGS_MARKER_SEQ','DDLAUX','SQL_TXT','PLITBLM','AW_DROP_PROC','DBMS_APPLICATION_INFO','DBMS_UTILITY','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','STANDARD','DBMS_STANDARD','TRACE_PUT_LINE','UTL_FILE');

SESSION_ID OWNER     NAME              TYPE        MODE_HELD MODE_REQU
---------- --------- ----------------- ----------- --------- ---------
        21           SYS               73          Share     None
        21 SYS       IDX_TEST_LHR      Index       Exclusive None

首先建表T_INDEX_161113并插入很多数据

SYS@oratest S1> CREATE TABLE T_INDEX_161113 AS SELECT * FROM DBA_OBJECTS;

Table created.

SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;

75349 rows created.

SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;

150698 rows created.

SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;

301396 rows created.

SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;

602792 rows created.

SYS@oratest S1> COMMIT;

Commit complete.

接着再在该表上创建一个索引

SYS@oratest S1> CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME);

在创建索引的同时，在会话2上插入一条记录：

SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;

====>>>>> 产生了阻塞

在创建索引的同时，查询相关锁的信息：

SQL> SELECT SID,

2 A.BLOCKING_SESSION,

3 EVENT,

4 A.P1,

5 A.P2,

6 A.P3,

7 CHR(BITAND(P1, -16777216) / 16777215) ||

8 CHR(BITAND(P1, 16711680) / 65535) "LOCK",

9 BITAND(P1, 65535) "MODE",

10 (SELECT OBJECT_NAME FROM DBA_OBJECTS D WHERE D.OBJECT_ID = A.P2) OBJECT_NAME

11 FROM GV$SESSION A

12 WHERE A.SID=141;

SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE OBJECT_NAME

---------- ---------------- ----------------------- ---------- ---------- ---------- ---- ---------- ----------------

142 21 enq: TM - contention 1414332419 77629 0 TM 3 T_INDEX_161113

SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (21,142) AND A.TYPE IN ('TX','TM');

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

00007F44001842E0 00007F4400184340 142 TM 77629 0 0 3 2 0

00007F44001842E0 00007F4400184340 21 TM 77629 0 4 0 3 1

00007F44001842E0 00007F4400184340 21 TM 18 0 3 0 3 0

0000000076273C58 0000000076273CD0 21 TX 65567 846 6 0 3 0

SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (21, 142);

SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS

---------- --------- -------------------- ------------- ------------- ------------ --------------------

142 SYS T_INDEX_161113 None Row-X (SX) 2 Not Blocking

21 SYS T_INDEX_161113 Share None 3 Blocking

21 SYS OBJ$ Row-X (SX) None 3 Not Blocking

SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE

2 FROM DBA_OBJECTS D

3 WHERE D.OBJECT_ID IN (18, 77629);

OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE

---------- ---------------------- ---------- -------------------

SYS T_INDEX_161113 77629 TABLE

SYS OBJ$ 18 TABLE

SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (21, 142) AND D.name NOT IN ('STANDARD','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','UTL_RAW','DBMS_APPLICATION_INFO','SDO_GEOR_DEF','SQL_TXT','DBMS_ASSERT','SDO_GEOR_DEF','TRACE_PUT_LINE','PLITBLM','DICTIONARY_OBJ_TYPE','DDLREPLICATION','DBMS_STANDARD','DBMS_APPLICATION_INFO','UTL_FILE','DDLAUX','DBMS_ASSERT','STANDARD','UTL_RAW','DDLREPLICATION','UTL_FILE','DDLAUX','GGS_MARKER_SEQ','DATABASE','LOGIN_USER','FILTERDDL','DBMS_UTILITY','GGS_DDL_SEQ','SYSEVENT','DBMS_UTILITY','LOGIN_USER','UTL_FILE','DATABASE','SDO_GEOR_DEF','UTL_RAW','GGS_DDL_SEQ','SDO_GEOR_DEF','DICTIONARY_OBJ_TYPE','UTL_RAW','DDLREPLICATION','DBMS_UTILITY','SYSEVENT','IS_VPD_ENABLED','DBMS_APPLICATION_INFO','FILTERDDL','DDLREPLICATION','STANDARD','DDLAUX','GGS_MARKER_SEQ','DDLAUX','SQL_TXT','PLITBLM','AW_DROP_PROC','DBMS_APPLICATION_INFO','DBMS_UTILITY','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','STANDARD','DBMS_STANDARD','TRACE_PUT_LINE','UTL_FILE');

SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU

---------- --------- ----------------- ----------- --------- ---------

21 SYS 73 Share None

21 SYS IDX_TEST_LHR Index Exclusive None

可以发现在会话1中，在创建索引的过程中会生成2个TM锁，锁类别分别为4和3，根据查询结果发现lmode=4的object_id为77629的对象对应的是T_INDEX_161113这个表，对应的是TM的S锁。另一个lmode=3的锁对象是系统基表OBJ$表，允许其它会话对该表执行DML操作。可以得出这样一个结论:当对表进行创建索引操作时，会伴随出现LMODE=4的S锁。根据锁的兼容模式可以发现S锁和任何DML操作都是冲突的！所以，尤其是在生产上，当在一个很大的表上进行索引创建的时候，任何对该表的DML操作都会被夯住!!!

从DBA_DDL_LOCKS视图可以看到，建索引的同时有6级排它DDL锁。

Oracle 11g下ONLINE选项不会堵塞DML操作

版本：11.2.0.3

接着上面的实验，重建索引的时候加上ONLINE，由于会话断开了，重新开2个会话，会话1为22，会话2为142：

SYS@oratest S1> ALTER INDEX IDX_TEST_LHR REBUILD ONLINE;

在创建索引的同时，在会话2上插入一条记录：
SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;

1 row created.

====>>>>> 加上ONLINE后无阻塞产生
在创建索引的同时，查询相关锁的信息：
SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (22,141) AND A.TYPE IN ('TX','TM');

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000774D9C08 00000000774D9C60         22 TX     327688       1122          0          4        761          0
00007FD883B38350 00007FD883B383B0         22 TM      77629          0          2          0        768          0
00007FD883B38350 00007FD883B383B0         22 TM      77643          0          4          0        767          0
0000000076274668 00000000762746E0         22 TX     196612       1119          6          0        768          0
0000000076236E38 0000000076236EB0        141 TX     327688       1122          6          0        763          1
00007FD883B38350 00007FD883B383B0        141 TM      77629          0          3          0        763          0

6 rows selected.

SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (22,141);

SESSION_ID OWNER       NAME                 MODE_HELD     MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ----------- -------------------- ------------- ------------- ------------ ----------------------------------------
       141 SYS         T_INDEX_161113       Row-X (SX)    None                   625 Not Blocking
        22 SYS         T_INDEX_161113       Row-S (SS)    None                   630 Not Blocking
        22 SYS         SYS_JOURNAL_77631    Share         None                   629 Not Blocking
SQL>  SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE
  2         FROM DBA_OBJECTS D
  3       WHERE D.OBJECT_ID IN (77629, 77643);

OWNER      OBJECT_NAME                OBJECT_ID OBJECT_TYPE
---------- ------------------------- ---------- -------------------
SYS        SYS_JOURNAL_77631              77643 TABLE
SYS        T_INDEX_161113                 77629 TABLE

SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (22,141) AND D.name NOT IN ('STANDARD','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','UTL_RAW','DBMS_APPLICATION_INFO','SDO_GEOR_DEF','SQL_TXT','DBMS_ASSERT','SDO_GEOR_DEF','TRACE_PUT_LINE','PLITBLM','DICTIONARY_OBJ_TYPE','DDLREPLICATION','DBMS_STANDARD','DBMS_APPLICATION_INFO','UTL_FILE','DDLAUX','DBMS_ASSERT','STANDARD','UTL_RAW','DDLREPLICATION','UTL_FILE','DDLAUX','GGS_MARKER_SEQ','DATABASE','LOGIN_USER','FILTERDDL','DBMS_UTILITY','GGS_DDL_SEQ','SYSEVENT','DBMS_UTILITY','LOGIN_USER','UTL_FILE','DATABASE','SDO_GEOR_DEF','UTL_RAW','GGS_DDL_SEQ','SDO_GEOR_DEF','DICTIONARY_OBJ_TYPE','UTL_RAW','DDLREPLICATION','DBMS_UTILITY','SYSEVENT','IS_VPD_ENABLED','DBMS_APPLICATION_INFO','FILTERDDL','DDLREPLICATION','STANDARD','DDLAUX','GGS_MARKER_SEQ','DDLAUX','SQL_TXT','PLITBLM','AW_DROP_PROC','DBMS_APPLICATION_INFO','DBMS_UTILITY','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','STANDARD','DBMS_STANDARD','TRACE_PUT_LINE','UTL_FILE');
no rows selected

SQL> SELECT SID,
  2         A.BLOCKING_SESSION,
  3         EVENT,
  4         A.P1,
  5         A.P2,
  6         A.P3,
  7         CHR(BITAND(P1, -16777216) / 16777215) ||
  8         CHR(BITAND(P1, 16711680) / 65535) "LOCK",
  9         BITAND(P1, 65535) "MODE",
 10   (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT
 11    FROM GV$SESSION  A
 12   WHERE A.SID IN (141,22); 

       SID BLOCKING_SESSION EVENT                                      P1         P2         P3 LOCK       MODE SQL_TEXT
---------- ---------------- ---------------------------------- ---------- ---------- ---------- ---- ---------- -----------------------------------------------------------------------
        22              141 enq: TX - row lock contention      1415053316     327688       1122 TX            4 ALTER INDEX IDX_TEST_LHR REBUILD ONLINE
       141                  SQL*Net message from client        1650815232          1          0 be        28928 INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1

SYS@oratest S1> ALTER INDEX IDX_TEST_LHR REBUILD ONLINE;

在创建索引的同时，在会话2上插入一条记录：

SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;

1 row created.

====>>>>> 加上ONLINE后无阻塞产生

在创建索引的同时，查询相关锁的信息：

SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (22,141) AND A.TYPE IN ('TX','TM');

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

00000000774D9C08 00000000774D9C60 22 TX 327688 1122 0 4 761 0

00007FD883B38350 00007FD883B383B0 22 TM 77629 0 2 0 768 0

00007FD883B38350 00007FD883B383B0 22 TM 77643 0 4 0 767 0

0000000076274668 00000000762746E0 22 TX 196612 1119 6 0 768 0

0000000076236E38 0000000076236EB0 141 TX 327688 1122 6 0 763 1

00007FD883B38350 00007FD883B383B0 141 TM 77629 0 3 0 763 0

6 rows selected.

SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (22,141);

SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS

---------- ----------- -------------------- ------------- ------------- ------------ ----------------------------------------

141 SYS T_INDEX_161113 Row-X (SX) None 625 Not Blocking

22 SYS T_INDEX_161113 Row-S (SS) None 630 Not Blocking

22 SYS SYS_JOURNAL_77631 Share None 629 Not Blocking

SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE

2 FROM DBA_OBJECTS D

3 WHERE D.OBJECT_ID IN (77629, 77643);

OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE

---------- ------------------------- ---------- -------------------

SYS SYS_JOURNAL_77631 77643 TABLE

SYS T_INDEX_161113 77629 TABLE

SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (22,141) AND D.name NOT IN ('STANDARD','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','UTL_RAW','DBMS_APPLICATION_INFO','SDO_GEOR_DEF','SQL_TXT','DBMS_ASSERT','SDO_GEOR_DEF','TRACE_PUT_LINE','PLITBLM','DICTIONARY_OBJ_TYPE','DDLREPLICATION','DBMS_STANDARD','DBMS_APPLICATION_INFO','UTL_FILE','DDLAUX','DBMS_ASSERT','STANDARD','UTL_RAW','DDLREPLICATION','UTL_FILE','DDLAUX','GGS_MARKER_SEQ','DATABASE','LOGIN_USER','FILTERDDL','DBMS_UTILITY','GGS_DDL_SEQ','SYSEVENT','DBMS_UTILITY','LOGIN_USER','UTL_FILE','DATABASE','SDO_GEOR_DEF','UTL_RAW','GGS_DDL_SEQ','SDO_GEOR_DEF','DICTIONARY_OBJ_TYPE','UTL_RAW','DDLREPLICATION','DBMS_UTILITY','SYSEVENT','IS_VPD_ENABLED','DBMS_APPLICATION_INFO','FILTERDDL','DDLREPLICATION','STANDARD','DDLAUX','GGS_MARKER_SEQ','DDLAUX','SQL_TXT','PLITBLM','AW_DROP_PROC','DBMS_APPLICATION_INFO','DBMS_UTILITY','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','STANDARD','DBMS_STANDARD','TRACE_PUT_LINE','UTL_FILE');

no rows selected

SQL> SELECT SID,

2 A.BLOCKING_SESSION,

3 EVENT,

4 A.P1,

5 A.P2,

6 A.P3,

7 CHR(BITAND(P1, -16777216) / 16777215) ||

8 CHR(BITAND(P1, 16711680) / 65535) "LOCK",

9 BITAND(P1, 65535) "MODE",

10 (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT

11 FROM GV$SESSION A

12 WHERE A.SID IN (141,22);

SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT

---------- ---------------- ---------------------------------- ---------- ---------- ---------- ---- ---------- -----------------------------------------------------------------------

22 141 enq: TX - row lock contention 1415053316 327688 1122 TX 4 ALTER INDEX IDX_TEST_LHR REBUILD ONLINE

141 SQL*Net message from client 1650815232 1 0 be 28928 INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1

可以发现在会话1中，在加上ONLINE重建索引的过程中会生成2个TM锁，锁类别分别为2和4，根据查询结果发现lmode=2的object_id为77629的对象对应的是T_INDEX_161113这个表，对应的是TM的Row-S (SS)锁即行级共享锁，该锁允许其它会话对该表执行DML操作。另一个lmode=4的锁对象是SYS_JOURNAL_77631，应该为系统临时创建的对象，对应的是TM的S锁。
在会话2中，TX为6的锁，阻塞了其它会话，在这里其实是阻塞了会话1的重建索引的操作。
可以得出这样一个结论:当对表进行创建或重建索引操作时，可以加上ONLINE选项，不阻塞其它会话的DML操作，但是在创建或重建索引的过程中，其它的会话产生的事务会阻塞索引的创建或重建操作，所以必须结束其它会话的事务才能让创建或重建索引的操作完成。
注意：在加上ONLINE选项创建索引的过程中，若手动CTRL+C取消后，可能导致索引被锁，出现ORA-08104: this index object 77645 is being online built or rebuilt的错误，这个时候可以利用如下的脚本清理对象，77645为对象的OBJECT_ID：

 DECLARE
   DONE BOOLEAN;
 BEGIN
   DONE := DBMS_REPAIR.ONLINE_INDEX_CLEAN(77645);
 END;

DECLARE

DONE BOOLEAN;

BEGIN

DONE := DBMS_REPAIR.ONLINE_INDEX_CLEAN(77645);

END;

Oracle 10g下ONLINE选项会堵塞DML操作

版本为：10.2.0.1.0

重新开3个会话，会话1为143，会话2为152，会话3为158：

SYS@lhrdb S1> alter index IDX_TEST1_LHR rebuild online;

在创建索引的同时，在会话2上插入一条记录：
SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;

1 row created.

====>>>>> 加上ONLINE后仍然会阻塞DML语句，若无阻塞可以重新连接会话2再执行插入操作
在创建索引的同时，在会话3上插入一条记录：
SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;

1 row created.

====>>>>> 加上ONLINE后仍然会阻塞DML语句，若无阻塞可以重新连接会话3再执行插入操作

在创建索引的同时，查询相关锁的信息：
SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158)  ORDER BY a.SID,a.TYPE;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000704A7850 00000000704A7870        143 DL      53121          0          3          0        144          0
00000000704A7980 00000000704A79A0        143 DL      53121          0          3          0        144          0
00000000703B8630 00000000703B8658        143 TM      53121          0          2          4        161          0
00000000703B8730 00000000703B8758        143 TM      53156          0          4          0        161          0
000000006F49F268 000000006F49F3F0        143 TX     196651        452          6          0        159          0
00000000703B8930 00000000703B8958        152 TM      53121          0          0          3        141          0
00000000703B8830 00000000703B8858        158 TM      53121          0          3          0        153          1
000000006F45DC78 000000006F45DE00        158 TX     262170        423          6          0        153          0

8 rows selected.
SQL> SELECT * FROM V$lock_Type d WHERE d.TYPE='DL';

TYPE     NAME                               ID1_TAG      DESCRIPTION
-------- ---------------------------------- -----------  ------------
DL       Direct Loader Index Creation       object #      Lock to prevent index DDL during direct load

SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000703B8630 00000000703B8658        143 TM      53121          0          2          4        161          0
00000000703B8730 00000000703B8758        143 TM      53156          0          4          0        161          0
000000006F49F268 000000006F49F3F0        143 TX     196651        452          6          0        159          0
00000000703B8930 00000000703B8958        152 TM      53121          0          0          3        141          0
00000000703B8830 00000000703B8858        158 TM      53121          0          3          0        153          1
000000006F45DC78 000000006F45DE00        158 TX     262170        423          6          0        153          0

6 rows selected.
SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (143,152,158) ORDER BY d.SESSION_ID;

SESSION_ID OWNER                          NAME                           MODE_HELD     MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ------------------------------ ------------------------------ ------------- ------------- ------------ ----------------------------------------
       143 SYS                            T_INDEX_161113                 Row-S (SS)    Share                  335 Not Blocking
       143 SYS                            SYS_JOURNAL_53122              Share         None                   335 Not Blocking
       152 SYS                            T_INDEX_161113                 None          Row-X (SX)             315 Blocking
       158 SYS                            T_INDEX_161113                 Row-X (SX)    None                   327 Blocking
SQL>  SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE
  2         FROM DBA_OBJECTS D
  3       WHERE D.OBJECT_ID IN (53121, 53156);

OWNER     OBJECT_NAME             OBJECT_ID OBJECT_TYPE
--------- ---------------------- ---------- -------------------
SYS       T_INDEX_161113              53121 TABLE
SYS       SYS_JOURNAL_53122           53156 TABLE

SQL> SELECT d.owner,d.table_name,d.iot_type  FROM dba_tables d WHERE d.table_name='SYS_JOURNAL_53122';

OWNER                          TABLE_NAME                     IOT_TYPE
------------------------------ ------------------------------ ------------
SYS                            SYS_JOURNAL_53122              IOT

SQL> 
SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (143,152,158) AND D.name NOT IN ('STANDARD','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','UTL_RAW','DBMS_APPLICATION_INFO','SDO_GEOR_DEF','SQL_TXT','DBMS_ASSERT','SDO_GEOR_DEF','TRACE_PUT_LINE','PLITBLM','DICTIONARY_OBJ_TYPE','DDLREPLICATION','DBMS_STANDARD','DBMS_APPLICATION_INFO','UTL_FILE','DDLAUX','DBMS_ASSERT','STANDARD','UTL_RAW','DDLREPLICATION','UTL_FILE','DDLAUX','GGS_MARKER_SEQ','DATABASE','LOGIN_USER','FILTERDDL','DBMS_UTILITY','GGS_DDL_SEQ','SYSEVENT','DBMS_UTILITY','LOGIN_USER','UTL_FILE','DATABASE','SDO_GEOR_DEF','UTL_RAW','GGS_DDL_SEQ','SDO_GEOR_DEF','DICTIONARY_OBJ_TYPE','UTL_RAW','DDLREPLICATION','DBMS_UTILITY','SYSEVENT','IS_VPD_ENABLED','DBMS_APPLICATION_INFO','FILTERDDL','DDLREPLICATION','STANDARD','DDLAUX','GGS_MARKER_SEQ','DDLAUX','SQL_TXT','PLITBLM','AW_DROP_PROC','DBMS_APPLICATION_INFO','DBMS_UTILITY','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','STANDARD','DBMS_STANDARD','TRACE_PUT_LINE','UTL_FILE','DBMS_SYS_SQL','DBMS_XDBZ0','DBMS_SYS_SQL','DBMS_SQL','DBMS_SQL','DBMS_XDBZ0'); 

no rows selected
SQL> SELECT SID,
  2         A.BLOCKING_SESSION,
  3         EVENT,
  4         A.P1,
  5         A.P2,
  6         A.P3,
  7         CHR(BITAND(P1, -16777216) / 16777215) ||
  8         CHR(BITAND(P1, 16711680) / 65535) "LOCK",
  9         BITAND(P1, 65535) "MODE",
 10                      (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT
 11    FROM GV$SESSION  A
 12   WHERE A.SID IN (143,152,158);  

       SID BLOCKING_SESSION EVENT                                P1         P2         P3 LOCK       MODE SQL_TEXT
---------- ---------------- ---------------------------- ---------- ---------- ---------- ---- ---------- -----------------------------------------------
       143              158 enq: TM - contention         1414332420      53121          0 TM            4 alter index IDX_TEST1_LHR rebuild online
       152              143 enq: TM - contention         1414332419      53121          0 TM            3  INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1
       158                  SQL*Net message from client  1650815232          1          0 be        28928  INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1

SYS@lhrdb S1> alter index IDX_TEST1_LHR rebuild online;

在创建索引的同时，在会话2上插入一条记录：

SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;

1 row created.

====>>>>> 加上ONLINE后仍然会阻塞DML语句，若无阻塞可以重新连接会话2再执行插入操作

在创建索引的同时，在会话3上插入一条记录：

SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;

1 row created.

====>>>>> 加上ONLINE后仍然会阻塞DML语句，若无阻塞可以重新连接会话3再执行插入操作

在创建索引的同时，查询相关锁的信息：

SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158) ORDER BY a.SID,a.TYPE;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

00000000704A7850 00000000704A7870 143 DL 53121 0 3 0 144 0

00000000704A7980 00000000704A79A0 143 DL 53121 0 3 0 144 0

00000000703B8630 00000000703B8658 143 TM 53121 0 2 4 161 0

00000000703B8730 00000000703B8758 143 TM 53156 0 4 0 161 0

000000006F49F268 000000006F49F3F0 143 TX 196651 452 6 0 159 0

00000000703B8930 00000000703B8958 152 TM 53121 0 0 3 141 0

00000000703B8830 00000000703B8858 158 TM 53121 0 3 0 153 1

000000006F45DC78 000000006F45DE00 158 TX 262170 423 6 0 153 0

8 rows selected.

SQL> SELECT * FROM V$lock_Type d WHERE d.TYPE='DL';

TYPE NAME ID1_TAG DESCRIPTION

-------- ---------------------------------- ----------- ------------

DL Direct Loader Index Creation object # Lock to prevent index DDL during direct load

SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

00000000703B8630 00000000703B8658 143 TM 53121 0 2 4 161 0

00000000703B8730 00000000703B8758 143 TM 53156 0 4 0 161 0

000000006F49F268 000000006F49F3F0 143 TX 196651 452 6 0 159 0

00000000703B8930 00000000703B8958 152 TM 53121 0 0 3 141 0

00000000703B8830 00000000703B8858 158 TM 53121 0 3 0 153 1

000000006F45DC78 000000006F45DE00 158 TX 262170 423 6 0 153 0

6 rows selected.

SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (143,152,158) ORDER BY d.SESSION_ID;

SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS

---------- ------------------------------ ------------------------------ ------------- ------------- ------------ ----------------------------------------

143 SYS T_INDEX_161113 Row-S (SS) Share 335 Not Blocking

143 SYS SYS_JOURNAL_53122 Share None 335 Not Blocking

152 SYS T_INDEX_161113 None Row-X (SX) 315 Blocking

158 SYS T_INDEX_161113 Row-X (SX) None 327 Blocking

SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE

2 FROM DBA_OBJECTS D

3 WHERE D.OBJECT_ID IN (53121, 53156);

OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE

--------- ---------------------- ---------- -------------------

SYS T_INDEX_161113 53121 TABLE

SYS SYS_JOURNAL_53122 53156 TABLE

SQL> SELECT d.owner,d.table_name,d.iot_type FROM dba_tables d WHERE d.table_name='SYS_JOURNAL_53122';

OWNER TABLE_NAME IOT_TYPE

------------------------------ ------------------------------ ------------

SYS SYS_JOURNAL_53122 IOT

SQL>

SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (143,152,158) AND D.name NOT IN ('STANDARD','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','UTL_RAW','DBMS_APPLICATION_INFO','SDO_GEOR_DEF','SQL_TXT','DBMS_ASSERT','SDO_GEOR_DEF','TRACE_PUT_LINE','PLITBLM','DICTIONARY_OBJ_TYPE','DDLREPLICATION','DBMS_STANDARD','DBMS_APPLICATION_INFO','UTL_FILE','DDLAUX','DBMS_ASSERT','STANDARD','UTL_RAW','DDLREPLICATION','UTL_FILE','DDLAUX','GGS_MARKER_SEQ','DATABASE','LOGIN_USER','FILTERDDL','DBMS_UTILITY','GGS_DDL_SEQ','SYSEVENT','DBMS_UTILITY','LOGIN_USER','UTL_FILE','DATABASE','SDO_GEOR_DEF','UTL_RAW','GGS_DDL_SEQ','SDO_GEOR_DEF','DICTIONARY_OBJ_TYPE','UTL_RAW','DDLREPLICATION','DBMS_UTILITY','SYSEVENT','IS_VPD_ENABLED','DBMS_APPLICATION_INFO','FILTERDDL','DDLREPLICATION','STANDARD','DDLAUX','GGS_MARKER_SEQ','DDLAUX','SQL_TXT','PLITBLM','AW_DROP_PROC','DBMS_APPLICATION_INFO','DBMS_UTILITY','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','STANDARD','DBMS_STANDARD','TRACE_PUT_LINE','UTL_FILE','DBMS_SYS_SQL','DBMS_XDBZ0','DBMS_SYS_SQL','DBMS_SQL','DBMS_SQL','DBMS_XDBZ0');

no rows selected

SQL> SELECT SID,

2 A.BLOCKING_SESSION,

3 EVENT,

4 A.P1,

5 A.P2,

6 A.P3,

7 CHR(BITAND(P1, -16777216) / 16777215) ||

8 CHR(BITAND(P1, 16711680) / 65535) "LOCK",

9 BITAND(P1, 65535) "MODE",

10 (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT

11 FROM GV$SESSION A

12 WHERE A.SID IN (143,152,158);

SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT

---------- ---------------- ---------------------------- ---------- ---------- ---------- ---- ---------- -----------------------------------------------

143 158 enq: TM - contention 1414332420 53121 0 TM 4 alter index IDX_TEST1_LHR rebuild online

152 143 enq: TM - contention 1414332419 53121 0 TM 3 INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1

158 SQL*Net message from client 1650815232 1 0 be 28928 INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1

可以发现在会话1中，在加上ONLINE重建索引的过程中会生成2个TM锁，锁类别分别为2和4，根据查询结果发现lmode=2的object_id为53121的对象对应的是T_INDEX_161113这个表，对应的是TM的Row-S (SS)锁即行级共享锁，该锁允许其它会话对该表执行DML操作，但是该会话在请求模式为4的S锁。另一个lmode=4的锁对象是SYS_JOURNAL_53122，为系统临时创建的索引组织表（IOT），对应的是TM的S锁。

在会话2中，请求3级TM锁。会阻塞关系可以看出，会话3阻塞了会话1，而会话1阻塞了会话2，所以提交会话3即可让索引创建完成。

实验10.2.0.1.0

版本为：10.2.0.1.0
重新开3个会话，会话1为143，会话2为152，会话3为158，会话1插入一条记录：

SYS@lhrdb S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;

1 row created.

在会话2上采用ONLINE建立索引：
SYS@lhrdb S2> alter index IDX_TEST1_LHR rebuild online;

====>>>>> 加上ONLINE后仍然会被阻塞
在创建索引的同时，查询相关锁的信息：
SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152)  ORDER BY a.SID,a.TYPE;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000703B8630 00000000703B8658        143 TM      53121          0          3          0       1119          1
000000006F495E38 000000006F495FC0        143 TX     524318        484          6          0       1119          0
00000000704A7980 00000000704A79A0        152 DL      53121          0          3          0       1113          0
00000000704A7850 00000000704A7870        152 DL      53121          0          3          0       1113          0
00000000703B8730 00000000703B8758        152 TM      53121          0          2          4       1113          0
00000000703B8830 00000000703B8858        152 TM      53162          0          4          0       1112          0

6 rows selected.
SQL> SELECT * FROM V$lock_Type d WHERE d.TYPE='DL';

TYPE     NAME                               ID1_TAG      DESCRIPTION
-------- ---------------------------------- -----------  ------------
DL       Direct Loader Index Creation       object #      Lock to prevent index DDL during direct load

SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (143,152) ORDER BY d.SESSION_ID;

SESSION_ID OWNER                          NAME                           MODE_HELD     MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ------------------------------ ------------------------------ ------------- ------------- ------------ ----------------------------------------
       143 SYS                            T_INDEX_161113                 Row-X (SX)    None                  1176 Blocking
       152 SYS                            SYS_JOURNAL_53122              Share         None                  1169 Not Blocking
       152 SYS                            T_INDEX_161113                 Row-S (SS)    Share                 1170 Not Blocking
SQL>  SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE
  2         FROM DBA_OBJECTS D
  3       WHERE D.OBJECT_ID IN (53121, 53162);

OWNER     OBJECT_NAME             OBJECT_ID OBJECT_TYPE
--------- ---------------------- ---------- -------------------
SYS       T_INDEX_161113              53121 TABLE
SYS       SYS_JOURNAL_53122           53162 TABLE

SQL> SELECT d.owner,d.table_name,d.iot_type  FROM dba_tables d WHERE d.table_name='SYS_JOURNAL_53122';

OWNER                          TABLE_NAME                     IOT_TYPE
------------------------------ ------------------------------ ------------
SYS                            SYS_JOURNAL_53122              IOT

SQL> 
SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (143,152,158) AND D.name NOT IN ('ALERT_QUE_R','AQ$_ALERT_QT_E','AW_DROP_PROC','DATABASE','DBMS_APPLICATION_INFO','DBMS_BACKUP_RESTORE','DBMS_HA_ALERTS_PRVT','DBMS_OUTPUT','DBMS_PRVT_TRACE','DBMS_RCVMAN','DBMS_SQL','DBMS_STANDARD','DBMS_SYS_SQL','DBMS_TRANSACTION','DBMS_UTILITY','DBMS_XDBZ0','DICTIONARY_OBJ_NAME','DICTIONARY_OBJ_OWNER','PLITBLM','SCHEDULER$_INSTANCE_S','STANDARD'); 

no rows selected
SQL> SELECT SID,
  2         A.BLOCKING_SESSION,
  3         EVENT,
  4         A.P1,
  5         A.P2,
  6         A.P3,
  7         CHR(BITAND(P1, -16777216) / 16777215) ||
  8         CHR(BITAND(P1, 16711680) / 65535) "LOCK",
  9         BITAND(P1, 65535) "MODE",
 10                      (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT
 11    FROM GV$SESSION  A
 12   WHERE A.SID IN (143,152);  

       SID BLOCKING_SESSION EVENT                                P1         P2         P3 LOCK       MODE SQL_TEXT
---------- ---------------- ---------------------------- ---------- ---------- ---------- ---- ---------- -----------------------------------------------
       143                  SQL*Net message from client   1650815232          1          0 be        28928
       152              143 enq: TM - contention          1414332420      53121          0 TM            4 alter index IDX_TEST1_LHR rebuild online

SYS@lhrdb S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;

1 row created.

在会话2上采用ONLINE建立索引：

SYS@lhrdb S2> alter index IDX_TEST1_LHR rebuild online;

====>>>>> 加上ONLINE后仍然会被阻塞

在创建索引的同时，查询相关锁的信息：

SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152) ORDER BY a.SID,a.TYPE;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

00000000703B8630 00000000703B8658 143 TM 53121 0 3 0 1119 1

000000006F495E38 000000006F495FC0 143 TX 524318 484 6 0 1119 0

00000000704A7980 00000000704A79A0 152 DL 53121 0 3 0 1113 0

00000000704A7850 00000000704A7870 152 DL 53121 0 3 0 1113 0

00000000703B8730 00000000703B8758 152 TM 53121 0 2 4 1113 0

00000000703B8830 00000000703B8858 152 TM 53162 0 4 0 1112 0

6 rows selected.

SQL> SELECT * FROM V$lock_Type d WHERE d.TYPE='DL';

TYPE NAME ID1_TAG DESCRIPTION

-------- ---------------------------------- ----------- ------------

DL Direct Loader Index Creation object # Lock to prevent index DDL during direct load

SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (143,152) ORDER BY d.SESSION_ID;

SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS

---------- ------------------------------ ------------------------------ ------------- ------------- ------------ ----------------------------------------

143 SYS T_INDEX_161113 Row-X (SX) None 1176 Blocking

152 SYS SYS_JOURNAL_53122 Share None 1169 Not Blocking

152 SYS T_INDEX_161113 Row-S (SS) Share 1170 Not Blocking

SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE

2 FROM DBA_OBJECTS D

3 WHERE D.OBJECT_ID IN (53121, 53162);

OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE

--------- ---------------------- ---------- -------------------

SYS T_INDEX_161113 53121 TABLE

SYS SYS_JOURNAL_53122 53162 TABLE

SQL> SELECT d.owner,d.table_name,d.iot_type FROM dba_tables d WHERE d.table_name='SYS_JOURNAL_53122';

OWNER TABLE_NAME IOT_TYPE

------------------------------ ------------------------------ ------------

SYS SYS_JOURNAL_53122 IOT

SQL>

SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (143,152,158) AND D.name NOT IN ('ALERT_QUE_R','AQ$_ALERT_QT_E','AW_DROP_PROC','DATABASE','DBMS_APPLICATION_INFO','DBMS_BACKUP_RESTORE','DBMS_HA_ALERTS_PRVT','DBMS_OUTPUT','DBMS_PRVT_TRACE','DBMS_RCVMAN','DBMS_SQL','DBMS_STANDARD','DBMS_SYS_SQL','DBMS_TRANSACTION','DBMS_UTILITY','DBMS_XDBZ0','DICTIONARY_OBJ_NAME','DICTIONARY_OBJ_OWNER','PLITBLM','SCHEDULER$_INSTANCE_S','STANDARD');

no rows selected

SQL> SELECT SID,

2 A.BLOCKING_SESSION,

3 EVENT,

4 A.P1,

5 A.P2,

6 A.P3,

7 CHR(BITAND(P1, -16777216) / 16777215) ||

8 CHR(BITAND(P1, 16711680) / 65535) "LOCK",

9 BITAND(P1, 65535) "MODE",

10 (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT

11 FROM GV$SESSION A

12 WHERE A.SID IN (143,152);

SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT

---------- ---------------- ---------------------------- ---------- ---------- ---------- ---- ---------- -----------------------------------------------

143 SQL*Net message from client 1650815232 1 0 be 28928

152 143 enq: TM - contention 1414332420 53121 0 TM 4 alter index IDX_TEST1_LHR rebuild online

从上面的结果可以知道，会话2即创建索引的会话一共出现了4个锁，两个DL锁，一个针对表T_INDEX_161113的TM锁，一个是online rebuild index时需要的一个中间表的TM锁，中间表用于记录rebuild期间的增量数据，原理类似于物化视图日志，其object_id为53162，这是一个索引组织表(IOT)，从这里我们也可以发现IOT的优点和适合的场合，这张中间表只有插入，不会有删除和修改操作，而且只有主键条件查询，正是IOT最合适的场景。

会话2在请求一个模式为4的TM锁，模式4会阻塞这个表上的所有DML操作，所以这时再往这个表上执行DML也会挂起。

会话3删除一条语句：

SYS@lhrdb S3> delete from  T_INDEX_161113 where rownum<=1;

====>>>>> 有阻塞
查询锁的资源：
SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158)  ORDER BY a.SID,a.TYPE;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000703B8630 00000000703B8658        143 TM      53121          0          3          0       7573          1
000000006F495E38 000000006F495FC0        143 TX     524318        484          6          0       7573          0
00000000704A7850 00000000704A7870        152 DL      53121          0          3          0       7567          0
00000000704A7980 00000000704A79A0        152 DL      53121          0          3          0       7567          0
00000000703B8830 00000000703B8858        152 TM      53162          0          4          0       7566          0
00000000703B8730 00000000703B8758        152 TM      53121          0          2          4       7567          0
00000000703B8930 00000000703B8958        158 TM      53121          0          0          3        165          0

7 rows selected.

SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (143,152,158) ORDER BY d.SESSION_ID;

SESSION_ID OWNER      NAME                           MODE_HELD     MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ---------- ------------------------------ ------------- ------------- ------------ ---------------
       143 SYS        T_INDEX_161113                 Row-X (SX)    None                  7582 Blocking
       152 SYS        T_INDEX_161113                 Row-S (SS)    Share                 7576 Not Blocking
       152 SYS        SYS_JOURNAL_53122              Share         None                  7575 Not Blocking
       158 SYS        T_INDEX_161113                 None          Row-X (SX)             174 Blocking

SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (143,152,158) AND D.name NOT IN ('ALERT_QUE_R','AQ$_ALERT_QT_E','AW_DROP_PROC','DATABASE','DBMS_APPLICATION_INFO','DBMS_BACKUP_RESTORE','DBMS_HA_ALERTS_PRVT','DBMS_OUTPUT','DBMS_PRVT_TRACE','DBMS_RCVMAN','DBMS_SQL','DBMS_STANDARD','DBMS_SYS_SQL','DBMS_TRANSACTION','DBMS_UTILITY','DBMS_XDBZ0','DICTIONARY_OBJ_NAME','DICTIONARY_OBJ_OWNER','PLITBLM','SCHEDULER$_INSTANCE_S','STANDARD'); 

no rows selected

SQL> SELECT SID,
  2         A.BLOCKING_SESSION,
  3         EVENT,
  4         A.P1,
  5         A.P2,
  6         A.P3,
  7         CHR(BITAND(P1, -16777216) / 16777215) ||
  8         CHR(BITAND(P1, 16711680) / 65535) "LOCK",
  9         BITAND(P1, 65535) "MODE",
 10         (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT
 11    FROM GV$SESSION  A
 12   WHERE A.SID IN (143,152,158);

       SID BLOCKING_SESSION EVENT                                  P1         P2         P3 LOCK       MODE SQL_TEXT
---------- ---------------- ------------------------------ ---------- ---------- ---------- ---- ---------- ----------------------------------------------
       143                  SQL*Net message from client    1650815232          1          0 be        28928
       152              143 enq: TM - contention           1414332420      53121          0 TM            4 alter index IDX_TEST1_LHR rebuild online
       158              152 enq: TM - contention           1414332419      53121          0 TM            3 delete from  T_INDEX_161113 where rownum<=1

SQL>

SYS@lhrdb S3> delete from T_INDEX_161113 where rownum<=1;

====>>>>> 有阻塞

查询锁的资源：

SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158) ORDER BY a.SID,a.TYPE;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

00000000703B8630 00000000703B8658 143 TM 53121 0 3 0 7573 1

000000006F495E38 000000006F495FC0 143 TX 524318 484 6 0 7573 0

00000000704A7850 00000000704A7870 152 DL 53121 0 3 0 7567 0

00000000704A7980 00000000704A79A0 152 DL 53121 0 3 0 7567 0

00000000703B8830 00000000703B8858 152 TM 53162 0 4 0 7566 0

00000000703B8730 00000000703B8758 152 TM 53121 0 2 4 7567 0

00000000703B8930 00000000703B8958 158 TM 53121 0 0 3 165 0

7 rows selected.

SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (143,152,158) ORDER BY d.SESSION_ID;

SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS

---------- ---------- ------------------------------ ------------- ------------- ------------ ---------------

143 SYS T_INDEX_161113 Row-X (SX) None 7582 Blocking

152 SYS T_INDEX_161113 Row-S (SS) Share 7576 Not Blocking

152 SYS SYS_JOURNAL_53122 Share None 7575 Not Blocking

158 SYS T_INDEX_161113 None Row-X (SX) 174 Blocking

no rows selected

SQL> SELECT SID,

2 A.BLOCKING_SESSION,

3 EVENT,

4 A.P1,

5 A.P2,

6 A.P3,

7 CHR(BITAND(P1, -16777216) / 16777215) ||

8 CHR(BITAND(P1, 16711680) / 65535) "LOCK",

9 BITAND(P1, 65535) "MODE",

10 (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT

11 FROM GV$SESSION A

12 WHERE A.SID IN (143,152,158);

SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT

---------- ---------------- ------------------------------ ---------- ---------- ---------- ---- ---------- ----------------------------------------------

143 SQL*Net message from client 1650815232 1 0 be 28928

152 143 enq: TM - contention 1414332420 53121 0 TM 4 alter index IDX_TEST1_LHR rebuild online

158 152 enq: TM - contention 1414332419 53121 0 TM 3 delete from T_INDEX_161113 where rownum<=1

SQL>

会话3请求模式为3的TM锁无法获得，会话被阻塞。这是因为锁请求是需要排队的，即使会话3和会话1是可以并发的，但由于会话2先请求锁并进入等待队列，后来的会话3也只好进入队列等待。所以，如果在执行rebuild index online前有长事务，并且并发量比较大，则一旦执行alter index rebuild online，可能因为长事务阻塞，可能导致系统瞬间出现大量的锁，对于压力比较大的系统，这是一个不小的风险。这是需要迅速找出导致阻塞的会话kill掉，rebuild index online一旦执行，不可轻易中断，否则可能遇到ORA-08104。

从会话级别可以看出，会话1阻塞了会话2，会话2阻塞了会话3，在会话1执行rollback，可以发现很短时间内会话3也正常执行完毕，说明会话2持有模式4的TM锁的时间很短，然后在rebuild online的进行过程中，对表加的是模式为2的TM锁，所以这段时间不会阻塞DML操作：

回滚会话1，然后观察锁的情况：

SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158)  ORDER BY a.SID,a.TYPE;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000704A7850 00000000704A7870        152 DL      53121          0          3          0       8219          0
00000000704A7980 00000000704A79A0        152 DL      53121          0          3          0       8219          0
00000000703B8730 00000000703B8758        152 TM      53121          0          2          4        238          0
00000000703B8830 00000000703B8858        152 TM      53162          0          4          0       8218          0
000000006FFFDEB8 000000006FFFDF18        152 TS          0    4257321          6          0        237          0
000000006F4A7558 000000006F4A76E0        152 TX     262184        426          6          0        237          0
00000000703B8930 00000000703B8958        158 TM      53121          0          3          0        238          1
000000006F45DC78 000000006F45DE00        158 TX     589824        470          6          0        238          0

8 rows selected.

SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158) ORDER BY a.SID,a.TYPE;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

00000000704A7850 00000000704A7870 152 DL 53121 0 3 0 8219 0

00000000704A7980 00000000704A79A0 152 DL 53121 0 3 0 8219 0

00000000703B8730 00000000703B8758 152 TM 53121 0 2 4 238 0

00000000703B8830 00000000703B8858 152 TM 53162 0 4 0 8218 0

000000006FFFDEB8 000000006FFFDF18 152 TS 0 4257321 6 0 237 0

000000006F4A7558 000000006F4A76E0 152 TX 262184 426 6 0 237 0

00000000703B8930 00000000703B8958 158 TM 53121 0 3 0 238 1

000000006F45DC78 000000006F45DE00 158 TX 589824 470 6 0 238 0

8 rows selected.

会话2又开始在请求模式4的TM锁，被会话3阻塞!这时在会话1再执行DML操作，同样会被会话2阻塞，进入锁等待队列。

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(2)

标签： Oracle 锁重建索引 online在线

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

合 Oracle新建或重建索引过程中的锁信息

简介

实验

创建或重建索引会阻塞DML操作

Oracle 11g下ONLINE选项不会堵塞DML操作

Oracle 10g下ONLINE选项会堵塞DML操作

实验10.2.0.1.0

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 10 月
一	二	三	四	五	六	日
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

合 Oracle新建或重建索引过程中的锁信息

简介

实验

创建或重建索引会阻塞DML操作

Oracle 11g下ONLINE选项不会堵塞DML操作

Oracle 10g下ONLINE选项会堵塞DML操作

实验10.2.0.1.0

相关文章

您可能还喜欢...

【DB宝11】在Docker中只需2步即可拥有Oracle 11g企业版环境（11.2.0.3）

Oracle统计信息之动态采样（Dynamic Sampling）

Oracle中的统计信息介绍

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复