概述

ALTER TABLE ... MOVE PARTITION becomes non-blocking online DDL while DML operations continue to run uninterrupted on the partition that is being moved. Global indexes 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.
ALTER TABLE……MOVE分区变成了非阻塞的在线DDL，而DML操作继续在被移动的分区上不受干扰地运行。在移动分区期间维护全局索引，因此不再需要手工索引重建。
在线分区移动删除了实际移动分区命令的只读状态。

Use the ALTER TABLE...MOVE PARTITION statement or ALTER TABLE...MOVE SUBPARTITION statement to move a table partition or subpartition, respectively.

When you use the ONLINE keyword with either of these statements, DML operations can continue to run uninterrupted on the partition or subpartition that is being moved. If you do not include the ONLINE keyword, then DML operations are not permitted on the data in the partition or subpartition until the move operation is complete.

When you include the UPDATE INDEXES clause, these statements maintain both local and global indexes during the move. Therefore, using the ONLINE keyword with these statements eliminates the time it takes to regain partition performance after the move by maintaining global indexes and manually rebuilding indexes.
使用ALTER TABLE……移动分区语句或ALTER TABLE…移动子分区语句，分别移动表分区或分区。

当您使用这些语句的在线关键字时，DML操作可以继续在被移动的分区或分区上持续运行。如果不包括在线关键字，则在分区或子分区中的数据上不允许DML操作，直到移动操作完成为止。

当包含UPDATE INDEXES子句时，这些语句在移动过程中维护本地和全局索引。因此，在这些语句中使用在线关键字消除了通过维护全局索引和手工重建索引来恢复分区性能所需的时间。

 

实验

1) online move a table partition.
SQL> DROP TABLE t1 PURGE;

Table dropped.

SQL> CREATE TABLE t1
(id            NUMBER,
 description   VARCHAR2(50),
 created_date  DATE)
PARTITION BY RANGE (created_date)
(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users);

Table created.

SQL> INSERT INTO t1
SELECT level,
       'Description for ' || level,
       CASE
         WHEN MOD(level,2) = 0 THEN TO_DATE('01/07/2014', 'DD/MM/YYYY')
         ELSE TO_DATE('01/07/2015', 'DD/MM/YYYY')
       END
FROM   dual
CONNECT BY level <= 1000; 

1000 rows created.

SQL> COMMIT;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 't1');

PL/SQL procedure successfully completed.

SQL> COLUMN table_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20 
SQL> COLUMN TABLESPACE_NAME FORMAT A20
SQL> SELECT table_name,
       partition_name,
	   TABLESPACE_NAME,
       num_rows
FROM   user_tab_partitions where table_name='T1'
ORDER BY 1,2;
  2    3    4    5    6  
TABLE_NAME	     PARTITION_NAME	  TABLESPACE_NAME	 NUM_ROWS
-------------------- -------------------- -------------------- ----------
T1		     PART_2014		  USERS 		      500
T1		     PART_2015		  USERS 		      500

SQL> ALTER TABLE t1 MOVE PARTITION part_2015 TABLESPACE test UPDATE INDEXES;

Table altered.


SQL> SELECT table_name,
       partition_name,
	   TABLESPACE_NAME,
       num_rows
FROM   user_tab_partitions where table_name='T1'
ORDER BY 1,2;

TABLE_NAME	     PARTITION_NAME	  TABLESPACE_NAME	 NUM_ROWS
-------------------- -------------------- -------------------- ----------
T1		     PART_2014		  USERS 		      500
T1		     PART_2015		  TEST			      500

2) online move a table sub-partition.


SQL> DROP TABLE t1 PURGE;

Table dropped.

SQL> CREATE TABLE t1     
(id            NUMBER,
 description   VARCHAR2(50),
 created_date  DATE)
PARTITION BY RANGE (created_date)
SUBPARTITION BY HASH (id)
SUBPARTITIONS 4
(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users);

Table created.

SQL> INSERT INTO t1
SELECT level,
       'Description for ' || level,
       CASE
         WHEN MOD(level,2) = 0 THEN TO_DATE('01/07/2014', 'DD/MM/YYYY')
         ELSE TO_DATE('01/07/2015', 'DD/MM/YYYY')
       END
FROM   dual
CONNECT BY level <= 1000;

1000 rows created.

SQL> COMMIT;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 't1', granularity => 'SUBPARTITION');

PL/SQL procedure successfully completed.

SQL> COLUMN table_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN TABLESPACE_NAME FORMAT A20
SQL> set lines 200
SQL> SELECT table_name,
       partition_name,
       subpartition_name,
	   TABLESPACE_NAME,
       num_rows
FROM   user_tab_subpartitions where table_name='T1'
ORDER BY 1,2,3;

TABLE_NAME	     PARTITION_NAME	  SUBPARTITION_NAME    TABLESPACE_NAME	      NUM_ROWS
-------------------- -------------------- -------------------- -------------------- ----------
T1		     PART_2014		  SYS_SUBP346	       USERS			   107
T1		     PART_2014		  SYS_SUBP347	       USERS			   136
T1		     PART_2014		  SYS_SUBP348	       USERS			   121
T1		     PART_2014		  SYS_SUBP349	       USERS			   136
T1		     PART_2015		  SYS_SUBP350	       USERS			   127
T1		     PART_2015		  SYS_SUBP351	       USERS			   108
T1		     PART_2015		  SYS_SUBP352	       USERS			   140
T1		     PART_2015		  SYS_SUBP353	       USERS			   125

8 rows selected.

SQL> ALTER TABLE t1 MOVE SUBPARTITION SYS_SUBP353 ONLINE TABLESPACE test UPDATE INDEXES;

Table altered.

SQL> SELECT table_name,
       partition_name,
       subpartition_name,
	   TABLESPACE_NAME,
       num_rows
FROM   user_tab_subpartitions where table_name='T1'
ORDER BY 1,2,3;

TABLE_NAME	     PARTITION_NAME	  SUBPARTITION_NAME    TABLESPACE_NAME	      NUM_ROWS
-------------------- -------------------- -------------------- -------------------- ----------
T1		     PART_2014		  SYS_SUBP346	       USERS			   107
T1		     PART_2014		  SYS_SUBP347	       USERS			   136
T1		     PART_2014		  SYS_SUBP348	       USERS			   121
T1		     PART_2014		  SYS_SUBP349	       USERS			   136
T1		     PART_2015		  SYS_SUBP350	       USERS			   127
T1		     PART_2015		  SYS_SUBP351	       USERS			   108
T1		     PART_2015		  SYS_SUBP352	       USERS			   140
T1		     PART_2015		  SYS_SUBP353	       TEST			  	   125

8 rows selected.