ALTER TABLE … MOVE PARTITION ONLINE license trap

The new features of Oracle 12c I had looked closer at possibility to move partitions online. You can move the partition while it is being accessed by DMLs and queries, and what more – indexes are being properly maintained at the same time.

Together with moving partition you can compress it. In 11.2 you could do the following:

1
ALTER TABLE t1 MOVE PARTITION part_2015 TABLESPACE users COMPRESS UPDATE INDEXES;

Partition was not accessible during the move . Basic compression was used to compress data in the partiton so no Advanced Compression was required.

In 12c you can use new great feature ONLINE partition / subpartition move which has no impact on ongoing DML.

1
ALTER TABLE t1 MOVE PARTITION part_2015 ONLINE TABLESPACE users COMPRESS UPDATE INDEXES;

But be aware that it requires ACO option if you combine ONLINE MOVE with any compression including BASIC!!! Accordingly to the white paper: http://www.oracle.com/technetwork/database/options/compression/advanced-compression-wp-12c-1896128.pdf

“ALTER TABLE … MOVE PARTITION ONLINE allows DML operations to continue to run uninterrupted on the partition that is being moved. Global indexes are maintained during the move partition operation, so a manual index rebuild is no longer required. Certain uses of Online Move Partition require Advanced Compression: specifically, if the user uses the feature to move a partition to a compressed format (any form of compression, including Basic, Advanced Row, or HCC), then an Oracle Advanced Compression option license is required.”

Leave a Reply

Your email address will not be published.