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;
卡住......

--end--