今天就跟大家聊聊有关CREATE INDEX ……ONLINE的示例分析，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。
这里我们讨论一下CREATE INDEX……ONLINE在线创建索引的情况：
数据库版本：
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi
PL/SQL Release 10.2.0.4.0 – Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – Productio
NLSRTL Version 10.2.0.4.0 – Production
创建模拟用户和数据表：
SQL> create user xiaoyang identified by xiaoyang default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource to xiaoyang;
Grant succeeded.
SQL> grant select on "SYS"."V_$MYSTAT" to xiaoyang;
Grant succeeded.
SQL> connect xiaoyang/xiaoyang
Connected.
SQL> create table test(id number primary key,
2 name varchar2(20));
Table created.
SQL> insert into test values (111,'aaa');
1 row created.
SQL> commit;
Commit complete.
会话1：
SQL> connect xiaoyang/xiaoyang
Connected.
SQL> select sid from v$mystat where rownum=1;
SID
———-
136
SQL>
SQL> insert into test values (222,'bbb');
1 row created.
会话1的SID为136，向XIAOYANG.TEST表插入一条数据，但未提交。
会话2：
sqlplus xiaoyang/xiaoyang
……
SQL> select sid from v$mystat where rownum=1;
SID
———-
147
SQL> create index idx_test_name on test(name);
create index idx_test_name on test(name)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
不加ONLINE关键字直接报错。
SQL> create index idx_test_name on test(name) online;
加上ONLINE关键字之后发现执行该语句卡住了。
会话3：
sqlplus / as sysdba
……
SQL> SELECT SID,BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;
SID BLOCKING_SESSION
———- —————-
147 136
发现是136会话阻塞了147会话。
SQL> SELECT sid,type,id1,id2,lmode,request,block FROM V$LOCK WHERE SID IN(147,136) ORDER BY SID;
SID TY ID1 ID2 LMODE REQUEST BLOCK
———- — ———- ———- ———- ———- ———-
136 TM 115640 0 3 0 1
136 TX 393262 17362 6 0 0
147 TM 115643 0 4 0 0
147 TM 115640 0 2 4 0
147 DL 115640 0 3 0 0
147 DL 115640 0 3 0 0
6 rows selected.
查询锁发现，147会话对应的TM锁有两条记录，在请求模式为4的锁时一条是成功的，另外一条并未成功，只获得了模式为2的锁。因为是136会话阻塞了147会话，所以说136获得的模式为3的锁和模式为4的锁并不兼容。
-
0 – none
-
1 – null (NULL)
-
2 – row-S (SS)
-
3 – row-X (SX)
-
4 – share (S)
-
5 – S/Row-X (SSX)
-
6 – exclusive (X)
SQL> SELECT owner,object_id,object_name,object_type FROM DBA_OBJECTS WHERE OBJECT_ID in (115640,115643);
OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE
———- ———- ———————————————— ——————-
XIAOYANG 115640 TEST TABLE
XIAOYANG 115643 SYS_JOURNAL_115642 TABLE
115640是TEST表，而获取模式为4的锁成功的表为 SYS_JOURNAL_115642，它应该是执行ONLINE创建索引的中间表。
如果这个时候又有新的DML操作产生：
会话4：
sqlplus xiaoyang/xiaoyang
……
SQL> select sid from v$mystat where rownum=1;
SID
———-
148
SQL> insert into test values(333,'ccc');
该事务同样被阻塞。
会话3：
SQL> SELECT SID,BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;
SID BLOCKING_SESSION
———- —————-
147 136
148 147
发现是会话147阻塞了会话148。
SQL> SELECT sid,type,id1,id2,lmode,request,block FROM V$LOCK WHERE SID IN(147,136,148) ORDER BY SID;
SID TY ID1 ID2 LMODE REQUEST BLOCK
———- — ———- ———- ———- ———- ———-
136 TM 115640 0 3 0 1
136 TX 393262 17362 6 0 0
147 TM 115643 0 4 0 0
147 DL 115640 0 3 0 0
147 DL 115640 0 3 0 0
147 TM 115640 0 2 4 0
148 TM 115640 0 0 3 0
会话148在执行DML语句前请求表模式为3的锁失败。这个应该是连锁反应造成的。
将会话1提交。
会话4执行成功，但是会话3依然被阻塞。
SQL> SELECT SID,BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;
SID BLOCKING_SESSION
———- —————-
147 148
这个时候显示会话2(sid=147)是被会话4(sid=148)阻塞。
提交会话4，会话2 online创建索引成功！所有的锁消失。
SQL> SELECT owner,object_id,object_name,object_type FROM DBA_OBJECTS WHERE OBJECT_ID in (115640,115643);
OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE
———- ———- —————————— ——————-
XIAOYANG 115640 TEST TABLE
ONLINE创建索引时产生的中间表也消失了。
请注意：
执行ALTER INDEX ….. REBUILD ONLINE;同样会出现类似CREATE INDEX…… ONLINE的问题。
SQL> alter index idx_test_name rebuild;
alter index idx_test_name rebuild
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> alter index idx_test_name rebuild online;
卡住……
看完上述内容，你们对CREATE INDEX ……ONLINE的示例分析有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。
原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/199698.html