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. 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 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</code>

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%';</code>
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.