How to Move Partition Online in Oracle 12c

Starting 12c, Oracle has introduced a new feature for online movement of partitions. In this post we will discuss this new feature with the help of an eaxmple.

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.

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

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.