Oracle12c新特性 – 在线移动分区

1. 概述

在Oracle 12c中支持在线移动表分区,这在12c之前是不支持的

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> CREATE TABLE test1 (product NUMBER(16) PRIMARY KEY,customer VARCHAR2(4000),time_id DATE)
  2  PARTITION BY RANGE (time_id)
  3  (PARTITION test1_PART1 VALUES LESS THAN (TO_DATE('01-JUL-2013','DD-MON-YYYY')),
  4  PARTITION test1_PART2 VALUES LESS THAN (TO_DATE('01-AUG-2013','DD-MON-YYYY')),
  5  PARTITION test1_PART3 VALUES LESS THAN (TO_DATE('01-SEP-2013','DD-MON-YYYY')),
  6  PARTITION test1_PART4 VALUES LESS THAN (TO_DATE('01-OCT-2013','DD-MON-YYYY')),
  7  PARTITION test1_PART5 VALUES LESS THAN (TO_DATE('01-NOV-2013','DD-MON-YYYY')),
  8  PARTITION test1_PART6 VALUES LESS THAN (TO_DATE('01-DEC-2013','DD-MON-YYYY')),
  9  PARTITION test1_PART7 VALUES LESS THAN (TO_DATE('01-JAN-2014','DD-MON-YYYY')),
 10  PARTITION test1_PART_MAX VALUES LESS THAN (MAXVALUE));

Table created.

SQL> ALTER TABLE test1 MOVE PARTITION test1_PART1 ONLINE;
ALTER TABLE test1 MOVE PARTITION test1_PART1 ONLINE
                                             *
ERROR at line 1:
ORA-14020: this physical attribute may not be specified for a table partition

在线移动分区变成了非阻塞DML的在线DDL,全局索引和本地索引可以在移动时一起维护。

2. 测试过程

SQL> set linesize 160
SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL> CREATE TABLE test1 (product NUMBER(16) PRIMARY KEY,customer VARCHAR2(4000),time_id DATE)
  2  PARTITION BY RANGE (time_id)
  3  (PARTITION test1_PART1 VALUES LESS THAN (TO_DATE('01-JUL-2013','DD-MON-YYYY')),
  4  PARTITION test1_PART2 VALUES LESS THAN (TO_DATE('01-AUG-2013','DD-MON-YYYY')),
  5  PARTITION test1_PART3 VALUES LESS THAN (TO_DATE('01-SEP-2013','DD-MON-YYYY')),
  6  PARTITION test1_PART4 VALUES LESS THAN (TO_DATE('01-OCT-2013','DD-MON-YYYY')),
  7  PARTITION test1_PART5 VALUES LESS THAN (TO_DATE('01-NOV-2013','DD-MON-YYYY')),
  8  PARTITION test1_PART6 VALUES LESS THAN (TO_DATE('01-DEC-2013','DD-MON-YYYY')),
  9  PARTITION test1_PART7 VALUES LESS THAN (TO_DATE('01-JAN-2014','DD-MON-YYYY')),
 10  PARTITION test1_PART_MAX VALUES LESS THAN (MAXVALUE));

Table created.

SQL> ALTER TABLE test1 MOVE PARTITION test1_PART1 ONLINE;

Table altered.

SQL> ALTER TABLE test1 MOVE PARTITION
  2  test1_PART1 ONLINE UPDATE INDEXES; 

Table altered.

SQL> ALTER TABLE test1 MOVE PARTITION
  2  test1_PART1 ONLINE UPDATE GLOBAL INDEXES; 

Table altered.

在线移动分区不支持索引组织表,并且也不支持在CDB$ROOT中用SYS测试

关于紫砂壶

感悟技术人生
此条目发表在Oracle基础知识分类目录,贴了标签。将固定链接加入收藏夹。