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