12C New Feature: Online Move Partition (Doc ID 1584032.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

PURPOSE

 To be able to MOVE a Partition online. 为了能够在线移动分区

DETAILS

DML are allowed during Online Move Partition. ALTER TABLE MOVE PARTITION becomes non-blocking online DDL. Global indexes and local indexed are maintained during the move partition, so a manual index rebuild is no longer required. The online partitioning movement removes the read-only state for the actual MOVE PARTITION command.

在线移动分区期间允许使用DML。ALTER TABLE MOVE PARTITION 变为非阻塞在线DDL。全局索引和局部索引在移动分区期间得以维护，因此不再需要手动重建索引。联机分区移动将删除实际MOVE PARTITION命令的只读状态。

Following is an example for the same:  以下是相同的示例：

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

Table created

SQL> CREATE TABLE test2( product NUMBER(6) PRIMARY KEY,customer VARCHAR2(4000), time_id DATE)
PARTITION BY RANGE (time_id)
(PARTITION test1_PART1 VALUES LESS THAN (TO_DATE('01-JUL-2013','DD-MON-YYYY')));

Table created

SQL> CREATE TABLE test3( product NUMBER(6) PRIMARY KEY, customer VARCHAR2(4000),time_id DATE)
PARTITION BY SYSTEM
( PARTITION test3_PART1,
PARTITION test3_PART2,
PARTITION test3_PART3,
PARTITION test3_PART_MAX);

Table created

ONLINE Move Partition example

SQL> ALTER TABLE test1 MOVE PARTITION test1_PART1 ONLINE;

Table altered

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

Table altered

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

Table altered

Restrictions:  限制
- Online on IOT is not supported.

Note: Index maintenance during online move occurs as long as the UPDATE INDEXES or UPDATE GLOBAL INDEXES clause is used. These clauses exist for the non online move statement as well. So, there is no difference in this respect between online and non online move in this regard. 注意：只要使用UPDATE INDEXES或UPDATE GLOBAL INDEXES子句，就会在联机移动期间进行索引维护。这些条款也适用于非在线移动语句。因此，在线移动和非在线移动在这方面没有区别。