Oracle 12c 新特性 --- Enhanced Online DDL Capabilities

概念

Several schema maintenance DDL operations no longer require blocking locks, making these operations non-intrusive and transparent for online use. The improved schema maintenance DDL operations are:
一些schema维护DDL操作不再需要阻塞锁，使得这些操作对在线使用不具有干扰性和透明性。改进的模式维护DDL操作是:
•	DROP INDEX ONLINE
•	DROP CONSTRAINT ONLINE
•	SET UNUSED COLUMN ONLINE
•	ALTER INDEX UNUSABLE ONLINE
•	ALTER INDEX [VISIBLE | INVISIBLE]
Removing internal blocking locks enables simpler and more robust application development, especially for application migrations. It avoids application disruptions for many of the typical schema maintenance operations.
删除内部阻塞锁支持更简单、更健壮的应用程序开发，尤其是应用程序迁移。它避免了许多典型的模式维护操作的应用中断。

 

实验

1 11g 操作
1.1 创建表及索引
[leo@www.cndba.cn ~]$ sqlplus test/test

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 14 22:15:43 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test (id number, code number, name varchar2(30));

Table created.

SQL> insert into test select rownum, mod(rownum,1000), 'DAVID BOWIE' from dual connect by level <= 1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL>  create index test_code_i on test(code);

Index created.
1.2 会话1 插入一条数据
SQL> insert into test values (1001, 42, 'test');

1 row created.

1.3 会话2 执行DDL 语句都会报ORA-00054错误
[leo@www.cndba.cn ~]$ sqlplus test/test

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 14 22:15:43 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop index test_code_i;
drop index test_code_i
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> alter index test_code_i invisible;

Index altered.

SQL> alter index test_code_i unusable;
alter index test_code_i unusable
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

1.4 会话1 提交时，会话2 的DDL语句也可以成功执行
会话1 commit
SQL> commit;

Commit complete.

会话2 操作正常
SQL> alter index test_code_i unusable;

Index altered.

SQL> insert into test values (1002, 42, 'test2');

1 row created.

SQL> select segment_name, blocks, extents from dba_segments where segment_name = 'TEST_CODE_I';

no rows selected
我们不仅可以看到索引现在处于不可用状态，而且索引段已经被删除(在11g r2中)，因为与不可使用索引相关的存储不再使用。
因此，在Oracle 12c数据库之前的这些命令之前已经锁定了相关的问题。
如果我们现在在12c执行相同的设置，并且在会话中再次有一个未完成的事务:

2 12c 操作

2.1 会话1 执行
--创建表及索引参照上面操作
[leo@www.cndba.cn ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 23 22:51:51 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> conn test/test@pdbcndba
Connected.
SQL> insert into test values (1002, 42, 'test2');

1 row created.
2.2 会话2 执行 drop index 挂起
会话2 挂起
[leo@www.cndba.cn ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 23 22:51:51 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> conn test/test@pdbcndba
Connected.
SQL> drop index test_code_i online;
Drop Index命令现在无法获得ora - 00054:资源繁忙，而是挂起，直到所有先前的事务完成为止。
然而，当Drop Index命令挂起时，它不会在其他会话中锁定事务。
2.3 会话1 提交时 会话2 删除索引成功
会话1 
SQL> commit;

Commit complete.
会话2 
SQL> drop index test_code_i online;

Index dropped.
2.4 另一个与12c行为的细微差别。如果在您决定使索引不可用时存在一个现有事务:
会话1 
SQL> insert into test values (1003, 42, 'test2');

1 row created.
会话2
SQL> alter index test_code_i unusable online;

会话1 
与前一个演示一样，alter index命令将无限期地挂起直到之前的事务提交:
SQL> commit;

Commit complete.

会话2 
SQL> alter index test_code_i unusable online;

Index altered.

SQL> col index_name for a20
SQL> col status for a20
SQL> select index_name, status from dba_indexes where index_name = 'TEST_CODE_I';

INDEX_NAME	     STATUS
-------------------- --------------------
TEST_CODE_I	     UNUSABLE


SQL> col segment_name for a20
SQL> select segment_name, blocks, extents from dba_segments where segment_name = 'TEST_CODE_I';

SEGMENT_NAME		 BLOCKS    EXTENTS
-------------------- ---------- ----------
TEST_CODE_I		      8 	 1


我们注意到该索引最终无法使用，但是由于使用了在线条款，该部分现在还没有被删除(因为它是在11g R2 demo中)。
使用Oracle 12c数据库，这些新的在线选项减少了与这些索引相关的DDL命令相关的锁定影响和并发问题。

3 附录
除了上面列出的文章之外，现在还提供了在线关键字的非阻塞操作。

    ALTER TABLE table-name DROP CONSTRAINT contraint-name ONLINE;
    ALTER TABLE table-name SET UNUSED (column-list) ONLINE;
    DROP INDEX index-name ONLINE;
    ALTER INDEX index-name UNUSABLE ONLINE;
    ALTER TABLE table_name MOVE PARTITION partition-name ONLINE ...;
    ALTER TABLE table_name MOVE SUBPARTITION partition-name ONLINE ...;

下面的操作是没有网络关键字的非阻塞操作。
	
    ALTER INDEX index-name INVISIBLE;
    ALTER INDEX index-name VISIBLE