New Features of Oracle 12C-online migration of table partitions or subpartitions

New Features of Oracle 12C-online migration of table partitions or subpartitions

Oracle 12c: new feature-online migration of table partitions or subpartitions. It is no longer necessary to migrate table partitions or subpartitions to different tablespaces in Oracle 12c R1. Similar to online migration of non-partitioned tables in earlier versions, table partitions or subpartitions can be migrated online or offline to a different tablespace. When an ONLINE statement is specified, all DML operations can be executed on the partitions or subpartitions involved in the process without any interruption. In contrast, if the partition or subpartition migration is performed offline, the DML operation is not allowed.

Example:

SQL> ALTER TABLE table_name MOVE PARTITION | SUBPARTITION partition_name TO tablespace tablespace_name;

SQL> ALTER TABLE table_name MOVE PARTITION | SUBPARTITION partition_name TO tablespace tablespace_name UPDATE INDEXES ONLINE;

The first example is to migrate a table partition or subpartition to a new tablespace offline. The second example is to migrate table partitions or subpartitions online and maintain any local or global indexes on the table. In addition, DML operations are not interrupted when an ONLINE statement is used.

Important:

The update indexes statement can avoid the problem where any local or global INDEXES in the table cannot be used.

The table's online migration restrictions also apply.

The introduction of the locking mechanism to complete this process, of course, will also lead to performance degradation and generate a large number of redo, depending on the size of the partition and sub-partition.