Advanced compression option caveat in Oracle 12c

Posted in: Oracle, Technical Track

 

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.

email

Author

Want to talk with an expert? Schedule a call with our team to get the conversation started.

3 Comments. Leave new

Hi,

You need to fill up a partition with rows up to the level that there is something to compress.
It is not enough to insert just 1 row.

Also, before selecting view dba_feature_usage_statistics, don’t forget to run:
EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);
commit; — commit is important

Also, don’t trust column “currently_used” in view dba_feature_usage_statistics.
Look at column “detected_usages” value.

Regards,

Reply

Thanks for the addition Marek – I hadn’t played with it too much as the point I was trying to make was the somewhat hidden licensing caveat. But I appreciate the correction!

Reply
Andrew Reid
March 3, 2021 6:55 am

If this wasn’t recorded in DBA_FEATURE_USAGE_STATISTICS, how can Oracle tell you have done it?

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *