[20181026]12c增強索引線上DDL操作.txt

[20181026]12c增強索引線上DDL操作.txt

--//12c增強索引線上DDL操作,加入online引數,一定程度減少阻塞.

Enhanced Online Index DDL Operations

12c also introduced enhancements to a number of index related DDL statements, removing blocking locks and making their
use online and far less intrusive. The following commands now have a new ONLINE option:

DROP INDEX ONLINE
ALTER INDEX INVISIBLE/VISIBLE ONLINE
ALTER INDEX UNUSABLE ONLINE

--//自己僅僅測試ALTER INDEX INVISIBLE/VISIBLE ONLINE.因為前一段時間遇到這個問題.
--//連結:[20180830]工作中一次失誤.txt=>http://blog.itpub.net/267265/viewspace-2213258/
--//如果當時12c也許這個問題就能避免了.

1.環境:
SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SCOTT@test01p> create table deptx as select * from dept;
Table created.

SCOTT@test01p> create unique index pk_deptx on deptx( deptno);
Index created.

2.測試:
--//session 1,不提交:
SCOTT@test01p> insert into deptx values (50,'aaaa','bbbb');
1 row created.

--//session 2;
SCOTT@test01p> alter index pk_deptx invisible online;
alter index pk_deptx invisible online
                     *
ERROR at line 1:
ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations

D:\tools\rlwrap>oerr ora 14141
14141, 00000, "ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations"
// *Cause:  ALTER INDEX statement attempted to combine a VISIBLE|INVISIBLE
//          operation with some other operation which is illegal
// *Action: Ensure that VISIBLE|INVISIBLE operation is the sole operation
//          specified in ALTER INDEX statement

--//不能加online操作。

SCOTT@test01p> alter index pk_deptx invisible;
Index altered.

SCOTT@test01p> alter index pk_deptx visible;
Index altered.

--//可以發現即使該表有事務，修改屬性invisible/visible根本沒有問題。我的測試修改這個屬性不能加online引數。
--//而這樣的操作在11g下是不行的，會報ora-00054.連結：http://blog.itpub.net/267265/viewspace-2217736/

3.繼續測試：
--//session 1,不提交:
SCOTT@test01p> insert into deptx values (50,'aaaa','bbbb');
1 row created.

--//session 2;
SCOTT@test01p> drop index pk_deptx;
drop index pk_deptx
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SCOTT@test01p> drop index pk_deptx online ;
--//掛起，等待事務提交。

--//session 3:
SCOTT@test01p> insert into deptx values (60,'aaaa','bbbb');
1 row created.

--//可以發現不影響其它會話後續執行dml的操作。

--//session 1:
SCOTT@test01p> insert into deptx values (50,'aaaa','bbbb');
1 row created.

SCOTT@test01p> commit;
Commit complete.

--//session 2:
SCOTT@test01p> drop index pk_deptx online ;
Index dropped.

--//修改索引ALTER INDEX UNUSABLE ONLINE與drop類似，不再測試。