Online migration of table partition or sub-partition

blessed DBA

Migration of a table partition or sub-partition to a different tablespace no longer requires a complex procedure in Oracle 12c R1.
In a similar way to how a heap (non-partition) table online migration was achieved in the previous releases, a table partition or sub-partition can be moved to a different tablespace online or offline.

When an ONLINE clause is specified, all DML operations can be performed without any interruption on the partition|sub-partition which is involved in the procedure. In contrast, no DML operations are allowed if the partition|sub-partition is moved offline.

Here are some working examples:

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 used to move a table partition|sub-partition to a new tablespace offline. The second example moves a table partition/sub-partitioning online maintaining any local/global indexes on the table. Additionally, no DML operation will get interrupted when ONLINE clause is mentioned.

Important notes:

• The UPDATE INDEXES clause will avoid any local/global indexes going unusable on the table.
• Table online migration restriction applies here too.
• There will be locking mechanism involved to complete the procedure, also it might leads to performance degradation and can generate huge redo, depending upon the size of the partition, sub-partition.

Timo Hahn

***Moderator note (Timo):

This is not an idea. You are just telling about features of the product.

This idea will be deleted in the near future!

***