Oracle 12c introduced a new capability to move a partition online, without any interruptions to DML happening at the same time. But, there's a catch.
So far we've been able to use basic table compression without having to worry about any extra licensing—it was just a plain EE (Enterprise Edition) feature. If you are planning to use the online partition move functionality, carefully check if you're not using basic compression anywhere.
For example:
create tablespace data datafile '+DATA' size 1g / create user foo identified by bar default tablespace data quota unlimited on data / grant create session, create table to foo / connect foo/bar create table test (x int, y varchar2(20)) partition by range (x) ( partition p1 values less than (100) tablespace data compress, partition p2 values less than (200) tablespace data, partition p3 values less than (300) tablespace data ) / So we now have this, and our licensing is still as we know it:
select partition_name, compression, compress_for from user_tab_partitions / PARTITION_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------------------------ P1 ENABLED BASIC P2 DISABLED P3 DISABLED We can use the new feature on partition p3:
alter table test move partition p3 online / Or, we can use the traditional means to compress the partition p2:
alter table test move partition p2 compress / But as soon as we do this move "online", we are required to purchase the Advanced Compression Option:
alter table test move partition p2 compress online / And, even sneakier
alter table test move partition p1 online / Notice how partition p1—which was previously compressed—also was online moved to a compressed format:
select partition_name, compression, compress_for from user_tab_partitions / PARTITION_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------------------------ P1 ENABLED BASIC P2 ENABLED BASIC P3 DISABLED And that, therefore, required the Advanced Compression Option.
Also note that the usage of this is not caught by dba_feature_usage_statistics (tested on 12.1.0.2):
select name, currently_used from dba_feature_usage_statistics where lower(name) like '%compress%' / NAME CURRE ---------------------------------------------------------------- ----- Oracle Advanced Network Compression Service FALSE Backup ZLIB Compression FALSE Backup BZIP2 Compression FALSE Backup BASIC Compression FALSE Backup LOW Compression FALSE Backup MEDIUM Compression FALSE Backup HIGH Compression FALSE Segment Maintenance Online Compress FALSE Compression Advisor FALSE SecureFile Compression (user) FALSE SecureFile Compression (system) FALSE HeapCompression FALSE Advanced Index Compression FALSE Hybrid Columnar Compression FALSE Hybrid Columnar Compression Row Level Locking FALSE 15 rows selected. I also tried to bounce the database and the data wasn't updated in my tests. I would've expected this to show up under "Segment Maintenance Online Compress", but in my tests, it did not.
This feature restriction isn't documented anywhere in the official product documentation—at least not that I could find. The only place where I could find this information was in this Oracle document.
Discover more about our experience in the world of Oracle.
Ready to optimize your Oracle Database for the future?