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

2022年8月13日

0 308 2

Tags： Oracle 锁重建索引 online在线

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

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

本页目录 隐藏

1) 简介

2) 实验

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

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

3) 相关文章

简介

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

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

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

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

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

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

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

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

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

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

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

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

实验

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

版本：11.2.0.3

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

Table created.

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

75349 rows created.

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

150698 rows created.

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

301396 rows created.

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

602792 rows created.

SYS@oratest S1> COMMIT;

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

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

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

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

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

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

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

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

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

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

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

Table created.

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

75349 rows created.

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

150698 rows created.

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

301396 rows created.

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

602792 rows created.

SYS@oratest S1> COMMIT;

Commit complete.

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

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

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

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

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

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

SQL> SELECT SID,

2 A.BLOCKING_SESSION,

3 EVENT,

4 A.P1,

5 A.P2,

6 A.P3,

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

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

9 BITAND(P1, 65535) "MODE",

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

11 FROM GV$SESSION A

12 WHERE A.SID=141;

SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE OBJECT_NAME

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

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

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

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

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

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

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

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

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

SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS

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

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

21 SYS T_INDEX_161113 Share None 3 Blocking

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

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

2 FROM DBA_OBJECTS D

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

OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE

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

SYS T_INDEX_161113 77629 TABLE

SYS OBJ$ 18 TABLE

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

SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU

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

21 SYS 73 Share None

21 SYS IDX_TEST_LHR Index Exclusive None

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

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

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

版本：11.2.0.3

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

SYS@oratest S1> ALTER INDEX IDX_TEST_LHR REBUILD ONLINE;

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

1 row created.

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

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

6 rows selected.

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

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

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

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

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

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

SYS@oratest S1> ALTER INDEX IDX_TEST_LHR REBUILD ONLINE;

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

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

1 row created.

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

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

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

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

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

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

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

0000000076274668 00000000762746E0 22 TX 196612 1119 6 0 768 0

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

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

6 rows selected.

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

SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS

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

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

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

22 SYS SYS_JOURNAL_77631 Share None 629 Not Blocking

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

2 FROM DBA_OBJECTS D

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

OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE

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

SYS SYS_JOURNAL_77631 77643 TABLE

SYS T_INDEX_161113 77629 TABLE

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

no rows selected

SQL> SELECT SID,

2 A.BLOCKING_SESSION,

3 EVENT,

4 A.P1,

5 A.P2,

6 A.P3,

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

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

9 BITAND(P1, 65535) "MODE",

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

11 FROM GV$SESSION A

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

SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT

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

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

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

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

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

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

打赏赞(2)

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

小麦苗

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

发表回复取消回复

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

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

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

18509239930
个人微信
DB宝
个人邮箱
点击加入QQ群
个人微店
回到顶部

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

简介

实验

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

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

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

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

简介

实验

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

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

相关文章

您可能还喜欢...

在Oracle中，如何移动或重命名数据文件？

Oracle之dbms_backup_restore包的使用

使用XTTS异机迁移并升级11.2.0.4到12.2.0.1

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复