Advanced compression option caveat in Oracle 12c

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.

Understanding the Online Partition Move Capability

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 

The Licensing Trap: Basic vs. Advanced Compression

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.

Why dba_feature_usage_statistics Might Not Protect You

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.

Documentation Gaps and Compliance Risks

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.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?