This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,555 Users
  • 2,269,776 Discussions


Online migration of table partition or sub-partition

blessed DBA
blessed DBA Member Posts: 218
edited Mar 16, 2018 4:42AM in Database Ideas - Ideas

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.
blessed DBA
1 votes

Active · Last Updated