Technical Track

Advanced compression option caveat in Oracle 12c

2 min read

Nov 24, 2015 12:00:00 AM

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: [code lang="sql"] 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 ) / [/code] So we now have this, and our licensing is still as we know it: [code lang="sql"] select partition_name, compression, compress_for from user_tab_partitions / PARTITION_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------------------------ P1 ENABLED BASIC P2 DISABLED P3 DISABLED</code> [/code] We can use the new feature on partition p3: [code lang="sql"] alter table test move partition p3 online / [/code] Or, we can use the traditional means to compress the partition p2: [code lang="sql"]alter table test move partition p2 compress /[/code] But as soon as we do this move "online", we are required to purchase the Advanced Compression Option: [code lang="sql"]alter table test move partition p2 compress online /[/code] And, even sneakier: [code lang="sql"] alter table test move partition p1 online /[/code] Notice how partition p1 - which was previously compressed - also was online moved to a compressed format: [code lang="sql"]select partition_name, compression, compress_for from user_tab_partitions / PARTITION_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------------------------ P1 ENABLED BASIC P2 ENABLED BASIC P3 DISABLED [/code] 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): [code lang="sql"]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. [/code] 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.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Advanced compression option caveat in Oracle 12c

Share this

Share this

More resources

HCC compression on Oracle Live SQL

How To Rename Interval Partition with More Meaningful Name

12c: How to Restore/Recover a Small Table in a Large Database

Ready to unlock value from your data?