[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类似，不再测试。