Digging a little in to 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:
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.
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.”
About the author
An Oracle technology geek and crazy long distance runner, DBA24 Owner
Senior Oracle DBA / Consultant / [OCP10g, OCP12c, OCE RAC 10g] / [experience: 9y+]
Currently working for Bluegarden (Oslo Norway) by Miratech Group
Past: Mastercard / Trevica by Britenet, Citi International PLC, PZU