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
------------------------------ -------- ------------------------------
P3 DISABLED</code>

We can use the new feature on partition p3:

alter table test move partition p3

Or, we can use the traditional means to compress the partition p2:

alter table test move partition p2

But as soon as we do this move “online”, we are required to purchase the Advanced Compression Option:

alter table test move partition p2

And, even sneakier:

alter table test move partition p1

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
------------------------------ -------- ------------------------------

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

select name, currently_used from dba_feature_usage_statistics where lower(name) like '%compress%';</code>
---------------------------------------------------------------- -----
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.



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

About the Author

Stefan is a passionate Oracle database researcher and has been focusing on understanding the Oracle database at its core for more than 10 years. His primary focus lies in the performance of the database, its security aspects, and using its various features to solve problems and architect efficient database designs. Stefan is also an avid technical writer and has written large documentation sets from scratch for massive Oracle projects.

5 Comments. Leave new


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:
commit; — commit is important

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



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!

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?


First of all it is likely that this article is baded on a misunderstanding. Here is the quote from the Advanced Compression white paper:
Online Move Table/Partition/Subpartition (to a compressed format)
subpartitions to be moved as an online operation without blocking any concurrent DML operations. The
partition/subpartition move operation supports automatic index maintenance as part of the move, so no index
rebuild is required. The “online” feature of this MOVE operation is included with Advanced Compression.

To me this sounds like customers are being assured that they can use ONLINE with their compressed segments and not that using ONLINE with uncompressed segments requires additional licensing.

Second, what some employee writes into a white paper is not contractually binding to any Oracle customers. If the contract underlying a license purchase does not mention that online move partition requires purchase of Advanced Compression then just say no to Oracle sales/license audit staff.

Third, Oracle License Audit looks at DBA_FEATURE_USAGE_STATISTICS. If indeed nothing is recorded there then no customer will be asked to pay extra.


Hi Norbert,
Thanks for your reply, to clarify: the wording is not precise in the white paper referenced. The title suggests only that an ONLINE partition move that results in a COMPRESSED segment falls under the Advanced Compression License. Online partition moves that do not result in a compressed segment are not included.
With regards to the feature usage, it may be that for some reason the feature usage was not recorded for this version but in future versions or patch releases this may not be the case and it IS recorded.
With regards to the licensing in the latest 19c documentation -> – it does state that “Online Move Partition (to any compressed format)” is indeed included in the Advanced Compression Licence



Leave a Reply

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