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

由小麦苗 · 2022年8月13日

0 275 2

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

简介

可以利用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表。

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

实验

创建或重建索引会阻塞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阻塞，进入锁等待队列。

在会话3执行rollback或者commit以后，会话2和会话3都很快执行完毕。

会话3：
SYS@lhrdb S3> rollback;

Rollback complete.

会话2：
SYS@lhrdb S2> alter index IDX_TEST1_LHR rebuild online;

Index altered.

SYS@lhrdb S2> SYS@lhrdb S2> SYS@lhrdb S2>

会话3：

SYS@lhrdb S3> rollback;

Rollback complete.

会话2：

SYS@lhrdb S2> alter index IDX_TEST1_LHR rebuild online;

Index altered.

SYS@lhrdb S2> SYS@lhrdb S2> SYS@lhrdb S2>

从上面的试验可以发现，虽然rebuild index online在执行期间只持有模式2的TM锁，不会阻塞DML操作，但在操作的开始和结束阶段，是需要短暂的持有模式为4的TM锁的，这段会阻塞表上的所有DML操作。我们在做rebuild index online的时候，一定要在开始和结束阶段观察系统中是否有长事务的存储，对于并发量较大的系统，最严重的后果，可能在这两个关键点导致数据库产生大量锁等待，系统负载飙升，甚至宕机。

实验11.2.0.3.0

版本为：11.2.0.3.0

开3个会话，会话1为16，会话2为27，会话3为150，会话1删除一条记录：

SYS@oratest S1> delete from  T_INDEX_161113 where rownum<=1;

1 row deleted.

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

====>>>>> 会话2挂起
在创建索引的同时，查询相关锁的信息：
SYS@oratest S3> SELECT * FROM V$LOCK A WHERE A.SID IN (16,27)  ORDER BY a.SID,a.TYPE;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000774DA148 00000000774DA1A0         16 AE        100          0          4          0      17039          0
00007F95B6CC6C88 00007F95B6CC6CE8         16 TM      77629          0          3          0       4034          0
000000007620A7C0 000000007620A838         16 TX     131076       1126          6          0       4034          1
00000000774D9410 00000000774D9468         27 AE        100          0          4          0      18569          0
00000000774D9250 00000000774D92A8         27 DL      77629          0          3          0        115          0
00000000774DA4C8 00000000774DA520         27 DL      77629          0          3          0        115          0
00000000774DA5A8 00000000774DA600         27 OD      77631          0          6          0        115          0
00000000774D9A30 00000000774D9A88         27 OD      77629          0          4          0        115          0
00007F95B6CC6C88 00007F95B6CC6CE8         27 TM      77629          0          2          0        115          0
00007F95B6CC6C88 00007F95B6CC6CE8         27 TM      77665          0          4          0        115          0
00000000774D9090 00000000774D90E8         27 TO      68064          1          3          0      16833          0
0000000076218728 00000000762187A0         27 TX     196627       1131          6          0        115          0
00000000774D9B10 00000000774D9B68         27 TX     131076       1126          0          4        115          0

13 rows selected.

SYS@oratest S3> SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007F95B6CC5588 00007F95B6CC55E8         16 TM      77629          0          3          0       4071          0
000000007620A7C0 000000007620A838         16 TX     131076       1126          6          0       4071          1
00007F95B6CC5588 00007F95B6CC55E8         27 TM      77629          0          2          0        152          0
00007F95B6CC5588 00007F95B6CC55E8         27 TM      77665          0          4          0        152          0
00000000774D9B10 00000000774D9B68         27 TX     131076       1126          0          4        152          0
0000000076218728 00000000762187A0         27 TX     196627       1131          6          0        152          0

6 rows selected.

SYS@oratest S3> SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE IN ('AE','DL','OD','TO');

TYPE    NAME                           ID1_TAG         DESCRIPTION
------- ------------------------------ --------------- ------------------------------------------ --- -----------
DL      Direct Loader Index Creation   object #         Lock to prevent index DDL during direct load
AE      Edition Lock                   edition obj#     Prevent Dropping an edition in use
OD      Online DDLs                    object #         Lock to prevent concurrent online DDLs
TO      Temp Object                    object #         Synchronizes DDL and DML operations on a temp object

SYS@oratest S3>   SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE
  2         FROM DBA_OBJECTS D
  3       WHERE D.OBJECT_ID IN (77665, 77629);

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

SYS@oratest S3> 

SYS@oratest S3> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (16,27) ORDER BY d.SESSION_ID;

SESSION_ID OWNER     NAME               MODE_HELD     MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- --------- ------------------ ------------- ------------- ------------ ---------------
        16 SYS       T_INDEX_161113     Row-X (SX)    None                  4093 Not Blocking
        27 SYS       SYS_JOURNAL_77631  Share         None                   174 Not Blocking
        27 SYS       T_INDEX_161113     Row-S (SS)    None                   174 Not Blocking

SYS@oratest S3> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (16,27) 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','SDO_GEOR_DEF','SQL_TXT'); 

no rows selected

SYS@oratest S3> 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 (16,27);

       SID BLOCKING_SESSION EVENT                                      P1         P2         P3 LOCK       MODE SQL_TEXT
---------- ---------------- ---------------------------------- ---------- ---------- ---------- ---- ---------- ---------------------------------------------
        16                  SQL*Net message from client        1650815232          1          0 be        28928 delete from  T_INDEX_161113 where rownum<=1
        27               16 enq: TX - row lock contention      1415053316     131076       1126 TX            4 alter index IDX_TEST_LHR rebuild online

SYS@oratest S1> delete from T_INDEX_161113 where rownum<=1;

1 row deleted.

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

SYS@lhrdb S2> alter index IDX_TEST_LHR rebuild online;

====>>>>> 会话2挂起

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

SYS@oratest S3> SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) ORDER BY a.SID,a.TYPE;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

00000000774DA148 00000000774DA1A0 16 AE 100 0 4 0 17039 0

00007F95B6CC6C88 00007F95B6CC6CE8 16 TM 77629 0 3 0 4034 0

000000007620A7C0 000000007620A838 16 TX 131076 1126 6 0 4034 1

00000000774D9410 00000000774D9468 27 AE 100 0 4 0 18569 0

00000000774D9250 00000000774D92A8 27 DL 77629 0 3 0 115 0

00000000774DA4C8 00000000774DA520 27 DL 77629 0 3 0 115 0

00000000774DA5A8 00000000774DA600 27 OD 77631 0 6 0 115 0

00000000774D9A30 00000000774D9A88 27 OD 77629 0 4 0 115 0

00007F95B6CC6C88 00007F95B6CC6CE8 27 TM 77629 0 2 0 115 0

00007F95B6CC6C88 00007F95B6CC6CE8 27 TM 77665 0 4 0 115 0

00000000774D9090 00000000774D90E8 27 TO 68064 1 3 0 16833 0

0000000076218728 00000000762187A0 27 TX 196627 1131 6 0 115 0

00000000774D9B10 00000000774D9B68 27 TX 131076 1126 0 4 115 0

13 rows selected.

SYS@oratest S3> SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

00007F95B6CC5588 00007F95B6CC55E8 16 TM 77629 0 3 0 4071 0

000000007620A7C0 000000007620A838 16 TX 131076 1126 6 0 4071 1

00007F95B6CC5588 00007F95B6CC55E8 27 TM 77629 0 2 0 152 0

00007F95B6CC5588 00007F95B6CC55E8 27 TM 77665 0 4 0 152 0

00000000774D9B10 00000000774D9B68 27 TX 131076 1126 0 4 152 0

0000000076218728 00000000762187A0 27 TX 196627 1131 6 0 152 0

6 rows selected.

SYS@oratest S3> SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE IN ('AE','DL','OD','TO');

TYPE NAME ID1_TAG DESCRIPTION

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

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

AE Edition Lock edition obj# Prevent Dropping an edition in use

OD Online DDLs object # Lock to prevent concurrent online DDLs

TO Temp Object object # Synchronizes DDL and DML operations on a temp object

SYS@oratest S3> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE

2 FROM DBA_OBJECTS D

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

OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE

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

SYS SYS_JOURNAL_77631 77665 TABLE

SYS T_INDEX_161113 77629 TABLE

SYS@oratest S3>

SYS@oratest S3> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (16,27) ORDER BY d.SESSION_ID;

SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS

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

16 SYS T_INDEX_161113 Row-X (SX) None 4093 Not Blocking

27 SYS SYS_JOURNAL_77631 Share None 174 Not Blocking

27 SYS T_INDEX_161113 Row-S (SS) None 174 Not Blocking

SYS@oratest S3> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (16,27) 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','SDO_GEOR_DEF','SQL_TXT');

no rows selected

SYS@oratest S3> 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 (16,27);

SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT

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

16 SQL*Net message from client 1650815232 1 0 be 28928 delete from T_INDEX_161113 where rownum<=1

27 16 enq: TX - row lock contention 1415053316 131076 1126 TX 4 alter index IDX_TEST_LHR rebuild online

可以看到会话2正在请求一个模式为4的TX锁，注意和Oracle 10g请求的TM锁是不一样的，而且在我们以前的概念中，TX锁的模式都是6，这里出现了模式4的TX锁请求，应该是Oracle 11g中新引入的。那么模式4的TX锁和TM锁有什么不同呢？我们继续前面的实验步骤：

SYS@oratest S3> delete from  T_INDEX_161113 where object_id=2;

16 rows deleted.

SYS@oratest S3> delete from T_INDEX_161113 where object_id=2;

16 rows deleted.

会话3的DML操作顺利完成，没有被阻塞。而在10g当中，会话3是会被会话2请求的TM锁所阻塞的，这一点改进是非常有意思的，这样即使rebuid online操作被会话1的长事务阻塞，其他会话的DML操作，只要不和会话1冲突，都可以继续操作，在Oracle 10g及以前版本中的执行rebuild index online而造成锁等待的风险被大大的降低了。

依次提交会话1和会话3，则会话2成功完成。

Oracle 11g在很多细节方面确实做了不少的优化，而且像这样的优化，对于提高系统的高可用性的好处是不言而喻的，在Oracle 11g中，执行rebuild index online的风险将比10g以及更老版本中小得多，因为从头至尾都不再阻塞DML操作了，终于可以算得上名副其实的online操作了。

利用10704和10046跟踪锁

使用10704事件跟踪以下四类操作并对比跟踪结果：

create index
alter index rebuild
create index online
alter index rebuild online

1、create index与alter index rebuild所获取的TM锁完全一致
2、create index online与alter index rebuild online所获取的TM锁、临时表完全一致

10g

版本：10.2.0.1

create index

SQL> drop index IDX_TEST1_LHR;

Index dropped.

SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> CREATE INDEX IDX_TEST1_LHR ON T_INDEX_161113(OBJECT_NAME);

Index created.

SQL>  ALTER SESSION SET EVENTS '10704 trace name context off'; 

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> CREATE OR REPLACE VIEW VW_SQL_TRACE_NAME_LHR AS 
  2  SELECT D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
  3         P.SPID || '.trc' TRACE_FILE_NAME
  4    FROM (SELECT P.SPID
  5            FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
  6           WHERE M.STATISTIC# = '1'
  7             AND S.SID = M.SID
  8             AND P.ADDR = S.PADDR) P,
  9         (SELECT T.INSTANCE
 10            FROM V$THREAD T, V$PARAMETER V
 11           WHERE V.NAME = 'thread'
 12             AND (V.VALUE = '0' OR TO_CHAR(T.THREAD#) = V.VALUE)) I,
 13         (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') D;

View created.

SQL> 
SQL> 
SQL> 
SQL> CREATE OR REPLACE PUBLIC SYNONYM SYN_TRACENAME_LHR FOR VW_SQL_TRACE_NAME_LHR;

Synonym created.

SQL> 
SQL> select * from VW_SQL_TRACE_NAME_LHR;

TRACE_FILE_NAME
-----------------------------
/u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_516.trc
SQL> SELECT OBJECT_NAME,
  2         OBJECT_ID,
  3         DATA_OBJECT_ID,
  4         TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,
  5         TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID
  6    FROM DBA_OBJECTS
  7   WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST1_LHR');

OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID  HEX_DOBJECTID
--------------- ---------- -------------- ------------- -------------
IDX_TEST1_LHR        53239          53239          cff7          cff7
T_INDEX_161113       53121          53121          cf81          cf81

SQL> drop index IDX_TEST1_LHR;

Index dropped.

SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> CREATE INDEX IDX_TEST1_LHR ON T_INDEX_161113(OBJECT_NAME);

Index created.

SQL> ALTER SESSION SET EVENTS '10704 trace name context off';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> CREATE OR REPLACE VIEW VW_SQL_TRACE_NAME_LHR AS

2 SELECT D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||

3 P.SPID || '.trc' TRACE_FILE_NAME

4 FROM (SELECT P.SPID

5 FROM V$MYSTAT M, V$SESSION S, V$PROCESS P

6 WHERE M.STATISTIC# = '1'

7 AND S.SID = M.SID

8 AND P.ADDR = S.PADDR) P,

9 (SELECT T.INSTANCE

10 FROM V$THREAD T, V$PARAMETER V

11 WHERE V.NAME = 'thread'

12 AND (V.VALUE = '0' OR TO_CHAR(T.THREAD#) = V.VALUE)) I,

13 (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') D;

View created.

SQL>

SQL> CREATE OR REPLACE PUBLIC SYNONYM SYN_TRACENAME_LHR FOR VW_SQL_TRACE_NAME_LHR;

Synonym created.

SQL>

SQL> select * from VW_SQL_TRACE_NAME_LHR;

TRACE_FILE_NAME

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

/u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_516.trc

SQL> SELECT OBJECT_NAME,

2 OBJECT_ID,

3 DATA_OBJECT_ID,

4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,

5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID

6 FROM DBA_OBJECTS

7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST1_LHR');

OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID

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

IDX_TEST1_LHR 53239 53239 cff7 cff7

T_INDEX_161113 53121 53121 cf81 cf81

trace文件，搜字符串“cf81”：

1、获取T_INDEX_161113表mode=3 DL锁
*** 2016-11-21 16:23:57.846
ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 ***
ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78
    ktcipt(topxcb)=0x0

2、获取T_INDEX_161113表mode=4 TM锁
*** 2016-11-21 16:23:57.847
ksqgtl *** TM-0000cf81-00000000 mode=4 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78
    ktcipt(topxcb)=0x0

3、释放T_INDEX_161113表DL锁
*** 2016-11-21 16:24:06.899
ksqrcl: DL,cf81,0
ksqrcl: returns 0

4、释放T_INDEX_161113表TM锁
*** 2016-11-21 16:24:06.902
ksqrcl: TM,cf81,0
ksqrcl: returns 0

1、获取T_INDEX_161113表mode=3 DL锁

*** 2016-11-21 16:23:57.846

ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 ***

ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78

ktcipt(topxcb)=0x0

2、获取T_INDEX_161113表mode=4 TM锁

*** 2016-11-21 16:23:57.847

ksqgtl *** TM-0000cf81-00000000 mode=4 flags=0x401 timeout=0 ***

ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78

ktcipt(topxcb)=0x0

3、释放T_INDEX_161113表DL锁

*** 2016-11-21 16:24:06.899

ksqrcl: DL,cf81,0

ksqrcl: returns 0

4、释放T_INDEX_161113表TM锁

*** 2016-11-21 16:24:06.902

ksqrcl: TM,cf81,0

ksqrcl: returns 0

alter index ... rebuild

SQL> CONN / AS SYSDBA
Connected.
SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10'; 

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12'; 

Session altered.

SQL> ALTER INDEX IDX_TEST1_LHR REBUILD;

Index altered.

SQL> ALTER SESSION SET EVENTS '10704 trace name context off'; 

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> select * from VW_SQL_TRACE_NAME_LHR;

TRACE_FILE_NAME
-----------------------------
/u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_1383.trc

SQL> SELECT OBJECT_NAME,
  2         OBJECT_ID,
  3         DATA_OBJECT_ID,
  4         TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,
  5         TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID
  6    FROM DBA_OBJECTS
  7   WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST1_LHR');
OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID  HEX_DOBJECTID
--------------- ---------- -------------- ------------- -------------
IDX_TEST1_LHR        53239          53242          cff7          cffa
T_INDEX_161113       53121          53121          cf81          cf81

SQL> CONN / AS SYSDBA

Connected.

SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> ALTER INDEX IDX_TEST1_LHR REBUILD;

Index altered.

SQL> ALTER SESSION SET EVENTS '10704 trace name context off';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> select * from VW_SQL_TRACE_NAME_LHR;

TRACE_FILE_NAME

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

/u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_1383.trc

SQL> SELECT OBJECT_NAME,

2 OBJECT_ID,

3 DATA_OBJECT_ID,

4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,

5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID

6 FROM DBA_OBJECTS

7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST1_LHR');

OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID

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

IDX_TEST1_LHR 53239 53242 cff7 cffa

T_INDEX_161113 53121 53121 cf81 cf81

trace文件，搜字符串“cf81”：

1、获取T_INDEX_161113表mode=3 DL锁
*** 2016-11-21 16:37:04.615
ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 ***
ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78
    ktcipt(topxcb)=0x0

2、获取T_INDEX_161113表mode=4 TM锁
*** 2016-11-21 16:37:04.616
ksqgtl *** TM-0000cf81-00000000 mode=4 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78
    ktcipt(topxcb)=0x0

3、释放T_INDEX_161113表DL锁
*** 2016-11-21 16:37:09.948
ksqrcl: DL,cf81,0
ksqrcl: returns 0

4、释放T_INDEX_161113表TM锁
*** 2016-11-21 16:37:10.003
ksqrcl: TM,cf81,0
ksqrcl: returns 0

1、获取T_INDEX_161113表mode=3 DL锁

*** 2016-11-21 16:37:04.615

ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 ***

ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78

ktcipt(topxcb)=0x0

2、获取T_INDEX_161113表mode=4 TM锁

*** 2016-11-21 16:37:04.616

ksqgtl *** TM-0000cf81-00000000 mode=4 flags=0x401 timeout=0 ***

ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78

ktcipt(topxcb)=0x0

3、释放T_INDEX_161113表DL锁

*** 2016-11-21 16:37:09.948

ksqrcl: DL,cf81,0

ksqrcl: returns 0

4、释放T_INDEX_161113表TM锁

*** 2016-11-21 16:37:10.003

ksqrcl: TM,cf81,0

ksqrcl: returns 0

create index ... online

SQL> conn / as sysdba
Connected.

SQL> drop index IDX_TEST1_LHR;

Index dropped.

SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> CREATE INDEX IDX_TEST1_LHR ON T_INDEX_161113(OBJECT_NAME) ONLINE;

Index created.

SQL>  ALTER SESSION SET EVENTS '10704 trace name context off'; 

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> select * from VW_SQL_TRACE_NAME_LHR;

TRACE_FILE_NAME
-----------------------------
/u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_1915.trc

SQL> col object_name format a15
SQL> SELECT OBJECT_NAME,
  2         OBJECT_ID,
  3         DATA_OBJECT_ID,
  4         TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,
  5         TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID
  6    FROM DBA_OBJECTS
  7   WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');
OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID  HEX_DOBJECTID
--------------- ---------- -------------- ------------- -------------
IDX_TEST1_LHR        53243          53243          cffb          cffb
T_INDEX_161113       53121          53121          cf81          cf81

SQL> conn / as sysdba

Connected.

SQL> drop index IDX_TEST1_LHR;

Index dropped.

SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> CREATE INDEX IDX_TEST1_LHR ON T_INDEX_161113(OBJECT_NAME) ONLINE;

Index created.

SQL> ALTER SESSION SET EVENTS '10704 trace name context off';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> select * from VW_SQL_TRACE_NAME_LHR;

TRACE_FILE_NAME

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

/u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_1915.trc

SQL> col object_name format a15

SQL> SELECT OBJECT_NAME,

2 OBJECT_ID,

3 DATA_OBJECT_ID,

4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,

5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID

6 FROM DBA_OBJECTS

7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');

OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID

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

IDX_TEST1_LHR 53243 53243 cffb cffb

T_INDEX_161113 53121 53121 cf81 cf81

trace文件，搜字符串“cf81”：

1、获取T_INDEX_161113表mode=3 DL锁
*** 2016-11-21 16:45:14.381
ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 ***
ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78
    ktcipt(topxcb)=0x0

2、获取T_INDEX_161113表mode=2 TM锁
*** 2016-11-21 16:45:14.383
ksqgtl *** TM-0000cf81-00000000 mode=2 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78
    ktcipt(topxcb)=0x0

3、2级TM锁转换为4级TM锁，4级TM锁转换为2级TM锁
*** 2016-11-21 16:45:14.659
ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836
*** 2016-11-21 16:45:14.659
ksqcmi: TM,cf81,0 mode=4 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
*** 2016-11-21 16:45:14.659
ksqcnv: TM-0000cf81,00000000 mode=2 timeout=21474836
*** 2016-11-21 16:45:14.659
ksqcmi: TM,cf81,0 mode=2 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
WAIT #1: nam='db file sequential read' ela= 14264 file#=1 block#=62781 blocks=1 obj#=53121 tim=1445037026096411
WAIT #1: nam='db file scattered read' ela= 19094 file#=1 block#=62913 blocks=3 obj#=53121 tim=1445037026118946
WAIT #1: nam='db file scattered read' ela= 4712 file#=1 block#=62980 blocks=5 obj#=53121 tim=1445037026125569
。。。。。。。。。。。

4、2级TM锁转换为4级TM锁
*** 2016-11-21 16:45:26.192
ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836
*** 2016-11-21 16:45:26.192
ksqcmi: TM,cf81,0 mode=4 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0

5、释放T_INDEX_161113表DL锁
*** 2016-11-21 16:45:27.274
ksqrcl: DL,cf81,0
ksqrcl: returns 0

6、释放T_INDEX_161113表TM锁
*** 2016-11-21 16:45:27.393
ksqrcl: TM,cf81,0
ksqrcl: returns 0

1、获取T_INDEX_161113表mode=3 DL锁

*** 2016-11-21 16:45:14.381

ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 ***

ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78

ktcipt(topxcb)=0x0

2、获取T_INDEX_161113表mode=2 TM锁

*** 2016-11-21 16:45:14.383

ksqgtl *** TM-0000cf81-00000000 mode=2 flags=0x401 timeout=21474836 ***

ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78

ktcipt(topxcb)=0x0

3、2级TM锁转换为4级TM锁，4级TM锁转换为2级TM锁

*** 2016-11-21 16:45:14.659

ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836

*** 2016-11-21 16:45:14.659

ksqcmi: TM,cf81,0 mode=4 timeout=21474836

ksqcmi: returns 0

ksqcnv: RETURNS 0

*** 2016-11-21 16:45:14.659

ksqcnv: TM-0000cf81,00000000 mode=2 timeout=21474836

*** 2016-11-21 16:45:14.659

ksqcmi: TM,cf81,0 mode=2 timeout=21474836

ksqcmi: returns 0

ksqcnv: RETURNS 0

WAIT #1: nam='db file sequential read' ela= 14264 file#=1 block#=62781 blocks=1 obj#=53121 tim=1445037026096411

WAIT #1: nam='db file scattered read' ela= 19094 file#=1 block#=62913 blocks=3 obj#=53121 tim=1445037026118946

WAIT #1: nam='db file scattered read' ela= 4712 file#=1 block#=62980 blocks=5 obj#=53121 tim=1445037026125569

。。。。。。。。。。。

4、2级TM锁转换为4级TM锁

*** 2016-11-21 16:45:26.192

ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836

*** 2016-11-21 16:45:26.192

ksqcmi: TM,cf81,0 mode=4 timeout=21474836

ksqcmi: returns 0

ksqcnv: RETURNS 0

5、释放T_INDEX_161113表DL锁

*** 2016-11-21 16:45:27.274

ksqrcl: DL,cf81,0

ksqrcl: returns 0

6、释放T_INDEX_161113表TM锁

*** 2016-11-21 16:45:27.393

ksqrcl: TM,cf81,0

ksqrcl: returns 0

alter index ... rebuild online

SQL> conn / as sysdba
Connected.

SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> ALTER INDEX IDX_TEST1_LHR REBUILD ONLINE;

Index created.

SQL>  ALTER SESSION SET EVENTS '10704 trace name context off'; 

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> select * from VW_SQL_TRACE_NAME_LHR;

TRACE_FILE_NAME
-----------------------------
/u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_3347.trc

SQL> col object_name format a15

SQL> SELECT OBJECT_NAME,
  2         OBJECT_ID,
  3         DATA_OBJECT_ID,
  4         TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,
  5         TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID
  6    FROM DBA_OBJECTS
  7   WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');
OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID  HEX_DOBJECTID
--------------- ---------- -------------- ------------- -------------
IDX_TEST1_LHR        53243          53247          cffb          cfff
T_INDEX_161113       53121          53121          cf81          cf81

SQL> conn / as sysdba

Connected.

SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> ALTER INDEX IDX_TEST1_LHR REBUILD ONLINE;

Index created.

SQL> ALTER SESSION SET EVENTS '10704 trace name context off';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> select * from VW_SQL_TRACE_NAME_LHR;

TRACE_FILE_NAME

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

/u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_3347.trc

SQL> col object_name format a15

SQL> SELECT OBJECT_NAME,

2 OBJECT_ID,

3 DATA_OBJECT_ID,

4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,

5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID

6 FROM DBA_OBJECTS

7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');

OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID

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

IDX_TEST1_LHR 53243 53247 cffb cfff

T_INDEX_161113 53121 53121 cf81 cf81

trace文件，搜字符串“cf81”：

1、获取T_INDEX_161113表mode=3 DL锁
*** 2016-11-21 17:06:23.837
ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 ***
ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78
    ktcipt(topxcb)=0x0

2、获取T_INDEX_161113表mode=2 TM锁
PARSING IN CURSOR #1 len=40 dep=0 uid=0 oct=9 lid=0 tim=1445038265466869 hv=1374438854 ad='6c6dc948'
ALTER INDEX IDX_TEST1_LHR REBUILD ONLINE
END OF STMT
PARSE #1:c=6999,e=7057,p=0,cr=12,cu=0,mis=1,r=0,dep=0,og=1,tim=1445038265466867
*** 2016-11-21 17:06:23.838
ksqgtl *** TM-0000cf81-00000000 mode=2 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78
    ktcipt(topxcb)=0x0

3、2级TM锁转换为4级TM锁，4级TM锁转换为2级TM锁
*** 2016-11-21 17:06:23.937
ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836
*** 2016-11-21 17:06:23.937
ksqcmi: TM,cf81,0 mode=4 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
*** 2016-11-21 17:06:23.937
ksqcnv: TM-0000cf81,00000000 mode=2 timeout=21474836
*** 2016-11-21 17:06:23.937
ksqcmi: TM,cf81,0 mode=2 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
WAIT #1: nam='db file sequential read' ela= 17434 file#=1 block#=62781 blocks=1 obj#=53121 tim=1445038265592696
WAIT #1: nam='db file scattered read' ela= 25149 file#=1 block#=62913 blocks=3 obj#=53121 tim=1445038265625891
WAIT #1: nam='db file scattered read' ela= 22659 file#=1 block#=62980 blocks=5 obj#=53121 tim=1445038265654375
WAIT #1: nam='db file sequential read' ela= 19 file#=1 block#=62984 blocks=1 obj#=53121 tim=1445038265654750
WAIT #1: nam='db file scattered read' ela= 23256 file#=1 block#=63142 blocks=2 obj#=53121 tim=1445038265680595
。。。。。。。。。。。。。。

4、2级TM锁转换为4级TM锁
*** 2016-11-21 17:06:31.754
ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836
*** 2016-11-21 17:06:31.754
ksqcmi: TM,cf81,0 mode=4 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0

5、释放T_INDEX_161113表DL锁
*** 2016-11-21 17:06:32.806
ksqrcl: DL,cf81,0
ksqrcl: returns 0

6、释放T_INDEX_161113表TM锁
*** 2016-11-21 17:06:32.976
ksqrcl: TM,cf81,0
ksqrcl: returns 0

1、获取T_INDEX_161113表mode=3 DL锁

*** 2016-11-21 17:06:23.837

ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 ***

ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78

ktcipt(topxcb)=0x0

2、获取T_INDEX_161113表mode=2 TM锁

PARSING IN CURSOR #1 len=40 dep=0 uid=0 oct=9 lid=0 tim=1445038265466869 hv=1374438854 ad='6c6dc948'

ALTER INDEX IDX_TEST1_LHR REBUILD ONLINE

END OF STMT

PARSE #1:c=6999,e=7057,p=0,cr=12,cu=0,mis=1,r=0,dep=0,og=1,tim=1445038265466867

*** 2016-11-21 17:06:23.838

ksqgtl *** TM-0000cf81-00000000 mode=2 flags=0x401 timeout=21474836 ***

ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78

ktcipt(topxcb)=0x0

3、2级TM锁转换为4级TM锁，4级TM锁转换为2级TM锁

*** 2016-11-21 17:06:23.937

ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836

*** 2016-11-21 17:06:23.937

ksqcmi: TM,cf81,0 mode=4 timeout=21474836

ksqcmi: returns 0

ksqcnv: RETURNS 0

*** 2016-11-21 17:06:23.937

ksqcnv: TM-0000cf81,00000000 mode=2 timeout=21474836

*** 2016-11-21 17:06:23.937

ksqcmi: TM,cf81,0 mode=2 timeout=21474836

ksqcmi: returns 0

ksqcnv: RETURNS 0

WAIT #1: nam='db file sequential read' ela= 17434 file#=1 block#=62781 blocks=1 obj#=53121 tim=1445038265592696

WAIT #1: nam='db file scattered read' ela= 25149 file#=1 block#=62913 blocks=3 obj#=53121 tim=1445038265625891

WAIT #1: nam='db file scattered read' ela= 22659 file#=1 block#=62980 blocks=5 obj#=53121 tim=1445038265654375

WAIT #1: nam='db file sequential read' ela= 19 file#=1 block#=62984 blocks=1 obj#=53121 tim=1445038265654750

WAIT #1: nam='db file scattered read' ela= 23256 file#=1 block#=63142 blocks=2 obj#=53121 tim=1445038265680595

。。。。。。。。。。。。。。

4、2级TM锁转换为4级TM锁

*** 2016-11-21 17:06:31.754

ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836

*** 2016-11-21 17:06:31.754

ksqcmi: TM,cf81,0 mode=4 timeout=21474836

ksqcmi: returns 0

ksqcnv: RETURNS 0

5、释放T_INDEX_161113表DL锁

*** 2016-11-21 17:06:32.806

ksqrcl: DL,cf81,0

ksqrcl: returns 0

6、释放T_INDEX_161113表TM锁

*** 2016-11-21 17:06:32.976

ksqrcl: TM,cf81,0

ksqrcl: returns 0

11g

版本：11.2.0.3

create index

SQL> drop index IDX_TEST_LHR;

Index dropped.

SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME);

Index created.

SQL>  ALTER SESSION SET EVENTS '10704 trace name context off'; 

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> select value from v$diag_info where name like '%File%';

VALUE
--------------------------------------------------------------------------------
/u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23527.trc

SQL> col object_name format a15
SQL> SELECT OBJECT_NAME,
  2         OBJECT_ID,
  3         DATA_OBJECT_ID,
  4         TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID
  5    FROM DBA_OBJECTS
  6   WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');
OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID
--------------- ---------- -------------- -------------
T_INDEX_161113       77629          77629         12f3d
IDX_TEST_LHR         77884          77884         1303c

SQL> drop index IDX_TEST_LHR;

Index dropped.

SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME);

Index created.

SQL> ALTER SESSION SET EVENTS '10704 trace name context off';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> select value from v$diag_info where name like '%File%';

VALUE

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

/u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23527.trc

SQL> col object_name format a15

SQL> SELECT OBJECT_NAME,

2 OBJECT_ID,

3 DATA_OBJECT_ID,

4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID

5 FROM DBA_OBJECTS

6 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');

OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID

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

T_INDEX_161113 77629 77629 12f3d

IDX_TEST_LHR 77884 77884 1303c

trace文件，搜字符串“12f3d”：

1、获取T_INDEX_161113表mode=4 TM锁
PARSING IN CURSOR #140411478315224 len=50 dep=1 uid=0 oct=26 lid=0 tim=1479709305055527 hv=3478035675 ad='716d5f28' sqlid='b3p9ubr7nx76v'
LOCK TABLE "T_INDEX_161113" IN SHARE MODE  NOWAIT 
END OF STMT
PARSE #140411478315224:c=2000,e=3081,p=0,cr=19,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479709305055527

*** 2016-11-21 14:21:45.055
ksqgtl *** TM-00012f3d-00000000 mode=4 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x76273c58, ktcdix=2147483647, topxcb=0x76273c58
    ktcipt(topxcb)=0x0

2、获取T_INDEX_161113表mode=3 DL锁
*** 2016-11-21 14:21:45.056
ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 ***
ksqgtl: xcb=0x76273c58, ktcdix=2147483647, topxcb=0x76273c58
    ktcipt(topxcb)=0x0

3、释放T_INDEX_161113表DL锁
*** 2016-11-21 14:21:50.392
ksqrcl: DL,12f3d,0
ksqrcl: returns 0

4、释放T_INDEX_161113表TM锁
*** 2016-11-21 14:21:50.395
ksqrcl: TM,12f3d,0
ksqrcl: returns 0

1、获取T_INDEX_161113表mode=4 TM锁

PARSING IN CURSOR #140411478315224 len=50 dep=1 uid=0 oct=26 lid=0 tim=1479709305055527 hv=3478035675 ad='716d5f28' sqlid='b3p9ubr7nx76v'

LOCK TABLE "T_INDEX_161113" IN SHARE MODE NOWAIT

END OF STMT

PARSE #140411478315224:c=2000,e=3081,p=0,cr=19,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479709305055527

*** 2016-11-21 14:21:45.055

ksqgtl *** TM-00012f3d-00000000 mode=4 flags=0x401 timeout=0 ***

ksqgtl: xcb=0x76273c58, ktcdix=2147483647, topxcb=0x76273c58

ktcipt(topxcb)=0x0

2、获取T_INDEX_161113表mode=3 DL锁

*** 2016-11-21 14:21:45.056

ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 ***

ksqgtl: xcb=0x76273c58, ktcdix=2147483647, topxcb=0x76273c58

ktcipt(topxcb)=0x0

3、释放T_INDEX_161113表DL锁

*** 2016-11-21 14:21:50.392

ksqrcl: DL,12f3d,0

ksqrcl: returns 0

4、释放T_INDEX_161113表TM锁

*** 2016-11-21 14:21:50.395

ksqrcl: TM,12f3d,0

ksqrcl: returns 0

alter index ... rebuild

SQL> CONN / AS SYSDBA
Connected.
SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10'; 

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12'; 

Session altered.

SQL> ALTER INDEX IDX_TEST_LHR REBUILD;

Index altered.

SQL> ALTER SESSION SET EVENTS '10704 trace name context off'; 

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> select value from v$diag_info where name like '%File%';

VALUE
--------------------------------------------------------------------------------
/u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23540.trc

SQL> col object_name format a15
SQL> SELECT OBJECT_NAME,
  2         OBJECT_ID,
  3         DATA_OBJECT_ID,
  4         TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID
  5    FROM DBA_OBJECTS
  6   WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');

SQL> SELECT OBJECT_NAME,
  2         OBJECT_ID,
  3         DATA_OBJECT_ID,
  4         TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,
  5         TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID
  6    FROM DBA_OBJECTS
  7   WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');

OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID  HEX_DOBJECTID
--------------- ---------- -------------- ------------- -------------
T_INDEX_161113       77629          77629         12f3d         12f3d
IDX_TEST_LHR         77885          77886         1303d         1303e

SQL> CONN / AS SYSDBA

Connected.

SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> ALTER INDEX IDX_TEST_LHR REBUILD;

Index altered.

SQL> ALTER SESSION SET EVENTS '10704 trace name context off';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> select value from v$diag_info where name like '%File%';

VALUE

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

/u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23540.trc

SQL> col object_name format a15

SQL> SELECT OBJECT_NAME,

2 OBJECT_ID,

3 DATA_OBJECT_ID,

4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID

5 FROM DBA_OBJECTS

6 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');

SQL> SELECT OBJECT_NAME,

2 OBJECT_ID,

3 DATA_OBJECT_ID,

4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,

5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID

6 FROM DBA_OBJECTS

7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');

OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID

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

T_INDEX_161113 77629 77629 12f3d 12f3d

IDX_TEST_LHR 77885 77886 1303d 1303e

trace文件，搜字符串“12f3d”：

1、获取T_INDEX_161113表mode=4 TM锁
PARSING IN CURSOR #140719831671200 len=59 dep=1 uid=0 oct=26 lid=0 tim=1479709686366785 hv=3620741631 ad='7176cbc8' sqlid='chctu03bx08gz'
LOCK TABLE  FOR INDEX "IDX_TEST_LHR" IN SHARE MODE  NOWAIT 
END OF STMT
PARSE #140719831671200:c=10999,e=29442,p=2,cr=80,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479709686366785

*** 2016-11-21 14:28:06.366
ksqgtl *** TM-00012f3d-00000000 mode=4 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x76209db0, ktcdix=2147483647, topxcb=0x76209db0
    ktcipt(topxcb)=0x0

2、获取T_INDEX_161113表mode=3 DL锁
*** 2016-11-21 14:28:06.370
ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 ***
ksqgtl: xcb=0x76209db0, ktcdix=2147483647, topxcb=0x76209db0
    ktcipt(topxcb)=0x0

3、释放T_INDEX_161113表DL锁
*** 2016-11-21 14:28:10.938
ksqrcl: DL,12f3d,0
ksqrcl: returns 0

4、释放T_INDEX_161113表TM锁
*** 2016-11-21 14:28:10.947
ksqrcl: TM,12f3d,0
ksqrcl: returns 0

1、获取T_INDEX_161113表mode=4 TM锁

PARSING IN CURSOR #140719831671200 len=59 dep=1 uid=0 oct=26 lid=0 tim=1479709686366785 hv=3620741631 ad='7176cbc8' sqlid='chctu03bx08gz'

LOCK TABLE FOR INDEX "IDX_TEST_LHR" IN SHARE MODE NOWAIT

END OF STMT

PARSE #140719831671200:c=10999,e=29442,p=2,cr=80,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479709686366785

*** 2016-11-21 14:28:06.366

ksqgtl *** TM-00012f3d-00000000 mode=4 flags=0x401 timeout=0 ***

ksqgtl: xcb=0x76209db0, ktcdix=2147483647, topxcb=0x76209db0

ktcipt(topxcb)=0x0

2、获取T_INDEX_161113表mode=3 DL锁

*** 2016-11-21 14:28:06.370

ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 ***

ksqgtl: xcb=0x76209db0, ktcdix=2147483647, topxcb=0x76209db0

ktcipt(topxcb)=0x0

3、释放T_INDEX_161113表DL锁

*** 2016-11-21 14:28:10.938

ksqrcl: DL,12f3d,0

ksqrcl: returns 0

4、释放T_INDEX_161113表TM锁

*** 2016-11-21 14:28:10.947

ksqrcl: TM,12f3d,0

ksqrcl: returns 0

create index ... online

SQL> conn / as sysdba
Connected.

SQL> drop index IDX_TEST_LHR;

Index dropped.

SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME) ONLINE;

Index created.

SQL>  ALTER SESSION SET EVENTS '10704 trace name context off'; 

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> select value from v$diag_info where name like '%File%';

VALUE
--------------------------------------------------------------------------------
/u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23672.trc

SQL> col object_name format a15
SQL> SELECT OBJECT_NAME,
  2         OBJECT_ID,
  3         DATA_OBJECT_ID,
  4         TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,
  5         TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID
  6    FROM DBA_OBJECTS
  7   WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');
OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID  HEX_DOBJECTID
--------------- ---------- -------------- ------------- -------------
T_INDEX_161113       77629          77629         12f3d         12f3d
IDX_TEST_LHR         77887          77887         1303f         1303f

SQL> conn / as sysdba

Connected.

SQL> drop index IDX_TEST_LHR;

Index dropped.

SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME) ONLINE;

Index created.

SQL> ALTER SESSION SET EVENTS '10704 trace name context off';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> select value from v$diag_info where name like '%File%';

VALUE

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

/u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23672.trc

SQL> col object_name format a15

SQL> SELECT OBJECT_NAME,

2 OBJECT_ID,

3 DATA_OBJECT_ID,

4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,

5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID

6 FROM DBA_OBJECTS

7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');

OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID

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

T_INDEX_161113 77629 77629 12f3d 12f3d

IDX_TEST_LHR 77887 77887 1303f 1303f

trace文件，搜字符串“12f3d”：

1、获取T_INDEX_161113表mode=2 TM锁
*** 2016-11-21 15:14:44.397
ksqrcl: CU,717dfd90,0
ksqrcl: returns 0
=====================
PARSING IN CURSOR #140118279700704 len=46 dep=1 uid=0 oct=26 lid=0 tim=1479712484397029 hv=3395312659 ad='729e1628' sqlid='g95cs0g560r0m'
LOCK TABLE "T_INDEX_161113" IN ROW SHARE MODE 
END OF STMT
PARSE #140118279700704:c=1999,e=1893,p=0,cr=19,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479712484397029

*** 2016-11-21 15:14:44.397
ksqgtl *** TM-00012f3d-00000000 mode=2 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x761eac90, ktcdix=2147483647, topxcb=0x761eac90
    ktcipt(topxcb)=0x0
*** 2016-11-21 14:21:45.055

2、获取T_INDEX_161113表mode=3 DL锁
*** 2016-11-21 15:14:44.398
ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 ***
ksqgtl: xcb=0x761eac90, ktcdix=2147483647, topxcb=0x761eac90
    ktcipt(topxcb)=0x0

3、获取T_INDEX_161113表mode=4 OD锁
*** 2016-11-21 15:14:44.454
ksqgtl *** OD-00012f3d-00000000 mode=4 flags=0x10401 timeout=0 ***
ksqgtl: xcb=0x761eac90, ktcdix=2147483647, topxcb=0x761eac90
    ktcipt(topxcb)=0x0

4、释放T_INDEX_161113表DL锁
*** 2016-11-21 15:14:53.066
ksqrcl: DL,12f3d,0
ksqrcl: returns 0

5、释放T_INDEX_161113表OD、TM锁
*** 2016-11-21 15:14:55.327
ksqrcl: OD,12f3d,0
ksqrcl: returns 0

*** 2016-11-21 15:14:55.327
ksqrcl: TM,12f3d,0
ksqrcl: returns 0

1、获取T_INDEX_161113表mode=2 TM锁

*** 2016-11-21 15:14:44.397

ksqrcl: CU,717dfd90,0

ksqrcl: returns 0

=====================

PARSING IN CURSOR #140118279700704 len=46 dep=1 uid=0 oct=26 lid=0 tim=1479712484397029 hv=3395312659 ad='729e1628' sqlid='g95cs0g560r0m'

LOCK TABLE "T_INDEX_161113" IN ROW SHARE MODE

END OF STMT

PARSE #140118279700704:c=1999,e=1893,p=0,cr=19,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479712484397029

*** 2016-11-21 15:14:44.397

ksqgtl *** TM-00012f3d-00000000 mode=2 flags=0x401 timeout=21474836 ***

ksqgtl: xcb=0x761eac90, ktcdix=2147483647, topxcb=0x761eac90

ktcipt(topxcb)=0x0

*** 2016-11-21 14:21:45.055

2、获取T_INDEX_161113表mode=3 DL锁

*** 2016-11-21 15:14:44.398

ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 ***

ksqgtl: xcb=0x761eac90, ktcdix=2147483647, topxcb=0x761eac90

ktcipt(topxcb)=0x0

3、获取T_INDEX_161113表mode=4 OD锁

*** 2016-11-21 15:14:44.454

ksqgtl *** OD-00012f3d-00000000 mode=4 flags=0x10401 timeout=0 ***

ksqgtl: xcb=0x761eac90, ktcdix=2147483647, topxcb=0x761eac90

ktcipt(topxcb)=0x0

4、释放T_INDEX_161113表DL锁

*** 2016-11-21 15:14:53.066

ksqrcl: DL,12f3d,0

ksqrcl: returns 0

5、释放T_INDEX_161113表OD、TM锁

*** 2016-11-21 15:14:55.327

ksqrcl: OD,12f3d,0

ksqrcl: returns 0

*** 2016-11-21 15:14:55.327

ksqrcl: TM,12f3d,0

ksqrcl: returns 0

alter index ... rebuild online

SQL> conn / as sysdba
Connected.

SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> ALTER INDEX IDX_TEST_LHR REBUILD ONLINE;

Index created.

SQL>  ALTER SESSION SET EVENTS '10704 trace name context off'; 

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> select value from v$diag_info where name like '%File%';

VALUE
--------------------------------------------------------------------------------
/u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23792.trc

SQL> col object_name format a15

SQL> SELECT OBJECT_NAME,
  2         OBJECT_ID,
  3         DATA_OBJECT_ID,
  4         TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,
  5         TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID
  6    FROM DBA_OBJECTS
  7   WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');

OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID  HEX_DOBJECTID
--------------- ---------- -------------- ------------- -------------
T_INDEX_161113       77629          77629         12f3d         12f3d
IDX_TEST_LHR         77887          77890         1303f         13042

SQL> conn / as sysdba

Connected.

SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> ALTER INDEX IDX_TEST_LHR REBUILD ONLINE;

Index created.

SQL> ALTER SESSION SET EVENTS '10704 trace name context off';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

SQL> select value from v$diag_info where name like '%File%';

VALUE

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

/u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23792.trc

SQL> col object_name format a15

SQL> SELECT OBJECT_NAME,

2 OBJECT_ID,

3 DATA_OBJECT_ID,

4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,

5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID

6 FROM DBA_OBJECTS

7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');

OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID

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

T_INDEX_161113 77629 77629 12f3d 12f3d

IDX_TEST_LHR 77887 77890 1303f 13042

trace文件，搜字符串“12f3d”：

1、获取T_INDEX_161113表mode=2 TM锁
PARSING IN CURSOR #139909890400672 len=55 dep=1 uid=0 oct=26 lid=0 tim=1479715165881556 hv=1263262788 ad='7167d4f8' sqlid='6dh4ubt5nrr24'
LOCK TABLE  FOR INDEX "IDX_TEST_LHR" IN ROW SHARE MODE 
END OF STMT
PARSE #139909890400672:c=1000,e=1599,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479715165881555

*** 2016-11-21 15:59:25.881
ksqgtl *** TM-00012f3d-00000000 mode=2 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x76228ed0, ktcdix=2147483647, topxcb=0x76228ed0
    ktcipt(topxcb)=0x0

2、获取T_INDEX_161113表mode=3 DL锁
*** 2016-11-21 15:59:25.883
ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 ***
ksqgtl: xcb=0x76228ed0, ktcdix=2147483647, topxcb=0x76228ed0
    ktcipt(topxcb)=0x0

3、获取T_INDEX_161113表mode=4 OD锁
*** 2016-11-21 15:59:25.884
ksqgtl *** OD-00012f3d-00000000 mode=4 flags=0x10401 timeout=0 ***
ksqgtl: xcb=0x76228ed0, ktcdix=2147483647, topxcb=0x76228ed0
    ktcipt(topxcb)=0x0

4、释放T_INDEX_161113表DL锁
*** 2016-11-21 15:59:30.334
ksqrcl: DL,12f3d,0
ksqrcl: returns 0

5、释放T_INDEX_161113表OD、TM锁
*** 2016-11-21 15:59:30.363
ksqrcl: OD,12f3d,0
ksqrcl: returns 0

*** 2016-11-21 15:59:30.363
ksqrcl: OD,1303f,0
ksqrcl: returns 0

*** 2016-11-21 15:59:30.363
ksqrcl: TM,12f3d,0
ksqrcl: returns 0

1、获取T_INDEX_161113表mode=2 TM锁

PARSING IN CURSOR #139909890400672 len=55 dep=1 uid=0 oct=26 lid=0 tim=1479715165881556 hv=1263262788 ad='7167d4f8' sqlid='6dh4ubt5nrr24'

LOCK TABLE FOR INDEX "IDX_TEST_LHR" IN ROW SHARE MODE

END OF STMT

PARSE #139909890400672:c=1000,e=1599,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479715165881555

*** 2016-11-21 15:59:25.881

ksqgtl *** TM-00012f3d-00000000 mode=2 flags=0x401 timeout=21474836 ***

ksqgtl: xcb=0x76228ed0, ktcdix=2147483647, topxcb=0x76228ed0

ktcipt(topxcb)=0x0

2、获取T_INDEX_161113表mode=3 DL锁

*** 2016-11-21 15:59:25.883

ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 ***

ksqgtl: xcb=0x76228ed0, ktcdix=2147483647, topxcb=0x76228ed0

ktcipt(topxcb)=0x0

3、获取T_INDEX_161113表mode=4 OD锁

*** 2016-11-21 15:59:25.884

ksqgtl *** OD-00012f3d-00000000 mode=4 flags=0x10401 timeout=0 ***

ksqgtl: xcb=0x76228ed0, ktcdix=2147483647, topxcb=0x76228ed0

ktcipt(topxcb)=0x0

4、释放T_INDEX_161113表DL锁

*** 2016-11-21 15:59:30.334

ksqrcl: DL,12f3d,0

ksqrcl: returns 0

5、释放T_INDEX_161113表OD、TM锁

*** 2016-11-21 15:59:30.363

ksqrcl: OD,12f3d,0

ksqrcl: returns 0

*** 2016-11-21 15:59:30.363

ksqrcl: OD,1303f,0

ksqrcl: returns 0

*** 2016-11-21 15:59:30.363

ksqrcl: TM,12f3d,0

ksqrcl: returns 0

实验SQL

ALTER SESSION SET EVENTS '10704 trace name context forever,level 10'; 
ALTER SESSION SET EVENTS '10046 trace name context forever,level 12'; 
--CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME) ;
--ALTER INDEX IDX_TEST_LHR REBUILD;
--CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME) ONLINE;
ALTER INDEX IDX_TEST_LHR REBUILD;
ALTER SESSION SET EVENTS '10704 trace name context off'; 
ALTER SESSION SET EVENTS '10046 trace name context off';

SELECT OBJECT_NAME,
       OBJECT_ID,
       DATA_OBJECT_ID,
       TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,
       TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID
  FROM DBA_OBJECTS
 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST1_LHR');

select value from v$diag_info where name like '%File%';   
运行如下SQL来创建视图：
CREATE OR REPLACE VIEW VW_SQL_TRACE_NAME_LHR AS 
SELECT D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
       P.SPID || '.trc' TRACE_FILE_NAME
  FROM (SELECT P.SPID
          FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
         WHERE M.STATISTIC# = '1'
           AND S.SID = M.SID
           AND P.ADDR = S.PADDR) P,
       (SELECT T.INSTANCE
          FROM V$THREAD T, V$PARAMETER V
         WHERE V.NAME = 'thread'
           AND (V.VALUE = '0' OR TO_CHAR(T.THREAD#) = V.VALUE)) I,
       (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') D;
创建公共同义词：
CREATE OR REPLACE PUBLIC SYNONYM SYN_TRACENAME_LHR FOR VW_SQL_TRACE_NAME_LHR;

ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

--CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME) ;

--ALTER INDEX IDX_TEST_LHR REBUILD;

--CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME) ONLINE;

ALTER INDEX IDX_TEST_LHR REBUILD;

ALTER SESSION SET EVENTS '10704 trace name context off';

ALTER SESSION SET EVENTS '10046 trace name context off';

SELECT OBJECT_NAME,

OBJECT_ID,

DATA_OBJECT_ID,

TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,

TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID

FROM DBA_OBJECTS

WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST1_LHR');

select value from v$diag_info where name like '%File%';

运行如下SQL来创建视图：

CREATE OR REPLACE VIEW VW_SQL_TRACE_NAME_LHR AS

SELECT D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||

P.SPID || '.trc' TRACE_FILE_NAME

FROM (SELECT P.SPID

FROM V$MYSTAT M, V$SESSION S, V$PROCESS P

WHERE M.STATISTIC# = '1'

AND S.SID = M.SID

AND P.ADDR = S.PADDR) P,

(SELECT T.INSTANCE

FROM V$THREAD T, V$PARAMETER V

WHERE V.NAME = 'thread'

AND (V.VALUE = '0' OR TO_CHAR(T.THREAD#) = V.VALUE)) I,

(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') D;

创建公共同义词：

CREATE OR REPLACE PUBLIC SYNONYM SYN_TRACENAME_LHR FOR VW_SQL_TRACE_NAME_LHR;

查询锁用到的SQL语句

SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;
SELECT * FROM V$LOCK A WHERE A.SID IN (16,27)  ORDER BY a.SID,a.TYPE;
SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (16,27) ORDER BY d.SESSION_ID;
SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (16,27) 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','SDO_GEOR_DEF','SQL_TXT'); 
SELECT A.TADDR,
       A.LOCKWAIT,
       A.ROW_WAIT_OBJ#,
       A.ROW_WAIT_FILE#,
       A.ROW_WAIT_BLOCK#,
       A.ROW_WAIT_ROW#,
       (SELECT D.OWNER || '|' || D.OBJECT_NAME || '|' || D.OBJECT_TYPE
          FROM DBA_OBJECTS D
         WHERE D.OBJECT_ID = A.ROW_WAIT_OBJ#) OBJECT_NAME,
       A.EVENT,
       A.P1,
       A.P2,
             A.P3,
       CHR(BITAND(P1, -16777216) / 16777215) ||
       CHR(BITAND(P1, 16711680) / 65535) "LOCK",
       BITAND(P1, 65535) "MODE",
       TRUNC(P2 / POWER(2, 16)) AS XIDUSN,
       BITAND(P2, TO_NUMBER('FFFF', 'XXXX')) + 0 AS XIDSLOT,
       P3 XIDSQN,
       A.SID,
       A.BLOCKING_SESSION,
       A.SADDR,
       DBMS_ROWID.ROWID_CREATE(1, 77669, 8, 2799, 0) REQUEST_ROWID,
       (SELECT B.SQL_TEXT
          FROM V$SQL B
         WHERE B.SQL_ID = NVL(A.SQL_ID, A.PREV_SQL_ID)) SQL_TEXT
  FROM V$SESSION A
 WHERE A.SID IN (143); 

SELECT * FROM v$lock a WHERE a.KADDR='000000007620A7C0'; 
SELECT * FROM v$transaction a WHERE a.ADDR='000000007620A7C0';  

SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE IN ('AE','DL','OD','TO','TX');
SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE
         FROM DBA_OBJECTS D
     WHERE D.OBJECT_ID IN (77665, 77629);    

SELECT D.PARAMETER1,D.PARAMETER2,D.PARAMETER3 FROM V$EVENT_NAME  D WHERE D.NAME='enq: TX - row lock contention';

SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;

SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) ORDER BY a.SID,a.TYPE;

SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (16,27) ORDER BY d.SESSION_ID;

SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (16,27) 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','SDO_GEOR_DEF','SQL_TXT');

SELECT A.TADDR,

A.LOCKWAIT,

A.ROW_WAIT_OBJ#,

A.ROW_WAIT_FILE#,

A.ROW_WAIT_BLOCK#,

A.ROW_WAIT_ROW#,

(SELECT D.OWNER || '|' || D.OBJECT_NAME || '|' || D.OBJECT_TYPE

FROM DBA_OBJECTS D

WHERE D.OBJECT_ID = A.ROW_WAIT_OBJ#) OBJECT_NAME,

A.EVENT,

A.P1,

A.P2,

A.P3,

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

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

BITAND(P1, 65535) "MODE",

TRUNC(P2 / POWER(2, 16)) AS XIDUSN,

BITAND(P2, TO_NUMBER('FFFF', 'XXXX')) + 0 AS XIDSLOT,

P3 XIDSQN,

A.SID,

A.BLOCKING_SESSION,

A.SADDR,

DBMS_ROWID.ROWID_CREATE(1, 77669, 8, 2799, 0) REQUEST_ROWID,

(SELECT B.SQL_TEXT

FROM V$SQL B

WHERE B.SQL_ID = NVL(A.SQL_ID, A.PREV_SQL_ID)) SQL_TEXT

FROM V$SESSION A

WHERE A.SID IN (143);

SELECT * FROM v$lock a WHERE a.KADDR='000000007620A7C0';

SELECT * FROM v$transaction a WHERE a.ADDR='000000007620A7C0';

SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE IN ('AE','DL','OD','TO','TX');

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

FROM DBA_OBJECTS D

WHERE D.OBJECT_ID IN (77665, 77629);

SELECT D.PARAMETER1,D.PARAMETER2,D.PARAMETER3 FROM V$EVENT_NAME D WHERE D.NAME='enq: TX - row lock contention';

扩展

有关Oracle数据库锁的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2128896/

打赏赞(2)

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

小麦苗

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

发表回复取消回复

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

2023年 3月
一	二	三	四	五	六	日
	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

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部

小麦苗

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

简介

实验

创建或重建索引会阻塞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语句

扩展

相关文章

您可能还喜欢...

发表回复取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

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

简介

实验

创建或重建索引会阻塞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语句

扩展

相关文章

您可能还喜欢...

[INS-32104] Specified Oracle Home user is not the owner of the specified Oracle Base

如果oracle用户下的$ORACLE_HOME/bin/oracle文件的属主或权限出了问题，那么该如何修复呢？

采用DUPLICATE 把asm数据库复制到文件系统

发表回复 取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

发表回复取消回复