Advanced Compression - How to prevent usage if not licensed?

Mathias Zarick's Blog

Mathias is blogging here about Oracle and High Availability.

Advanced Compression - How to prevent usage if not licensed?

Rate This
  • Comments 20

Hi there,

advanced compression option (ACO) is a new option as of Oracle 11g. See
It is only available with Enterprise Edition and includes
- OLTP Table Compression
- LOB Compression
- Data Pump Compression
- Fast ZLIB RMAN Compression
- Data Guard Log Transport Compression

license costs are 25 percent of EE list price.

I do not want to blog about usefulness here. Let's say you did not invest into this, how can you assure that you oracle users does not use it?

Easiest way: install Standard Edition, everything will not work and you save also some more license costs.
But of course - you maybe need Enterprise Edition for other reasons.

To disable those features I have summarized here some Solutions/Workarounds if available.

1. OLTP Table Compression:
that's easy: _OLTP_COMPRESSION=false

2. Lob Compression.
This is harder.
You can set Parameter DB_SECUREFILE to NEVER or IGNORE. With this setting it is not possible to create securefile LOBs, but on the other hand your users might be clever enough to reset this parameter to PERMITTED which they can do with 'alter session' privilege. An disavantage is that you would not allow (or try to) securefiles at all, which would not be necessary, as securefiles are allowed also without ACO. As this is not bullet proof, cause of the 'alter session' thing, the only save way would be not to use ASSM Tablespaces as SecureFiles are only supported on ASSM Tablespaces. You would need to create the tablespaces with 'segment space management manual' clause. But also think about performance impacts when using tablespaces with freelists.

3. Data Pump Compression
Impossible to avoid the usage without revoking right to use data pump at all. :-(
I created an Enhancement Request for this

4. Fast ZLIB RMAN Compression
okay this is a thing the DBA can control, he knows if licensed or not and which compression method to use. Old BZIP2 Compression introduced with 10g is still usable without ACO license. This feature is not enabled by default.
Here is RMAN syntax for making sure Basic BZIP2 Compression is used:
Or for both, reset to default:

5. Data Guard Log Transport Compression
Can also be controlled by the DBA who sets up a data guard environment and is also not enabled by default.
And again some syntax:
DGMGRL> edit database 'DB_SITE1' set property RedoCompression='DISABLE';
this results into a log_archive_dest_N parameter with an attribute 'compression=disable'.

In 11gR2 I came across an init.ora Parameter in documentation called ENABLE_OPTION_ADVANCED_COMPRESSION. The Parameter is mentioned when explaining the compression attribute of log_archive_dest_N.
Also there is an ORA Error which reveals there is something like this:

# oerr ora 43850
43850, 0000, "Advanced Compression Option parameter is required to be enabled"
// *Document: YES
// *Cause:    An Advanced Compression Option (ACO) feature was specified
//            without enabling the ACO parameter.
// *Action:   Set or Update the ACO parameter (enable_option_advanced_compression)
//            to TRUE.

I felt this must be the solution but did find any more about this, so I asked Oracle in a Service Request. Their answer was disillusioning  :-|  :

BUG: 8596419
Status: 80,Development to Q/A
Fixed In Ver: 11.2

That's a pitty. I would be a good idea to give the dba the control of feature enabled or not.

Preventing ACO from being used is a hard thing, you might say - not even possible. Reliable disabling also takes some EE licensed features or can raise performance problems.

Have fun

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Post
  • Mathias,

    in 11.2 some options can be disabled with the chopt tool, that Marco Gralike mentions in his Blog: - but compression seems not to be included. But at least that's some kind of progress. In 10g the only way to disable AWR was to use an AWR procedure:

  • Martin,

    thanks for your comment. some options like partitioning, data mining, olap, etc. can be disabled by a relink. chopt is only a tool that wraps this quite comfortable for you, so that you don't have to fiddle around with the relink script. See also $ORACLE_HOME/bin/chopt.ini. but as you already stated advanced compression option cannot be disabled by a relink resp. by chopt.

    Cheers Mathias

  • Some time ago I blogged about Advanced Compression Option here . I also wrote an article in german about

  • Hi Mathias,

    How to find out when and how often ACO was used?



  • Hi Florian,

    this is not that easy, at least you cannot give exact and reliable numbers.

    To start:

    You need to query dba_feature_usage_statistics and look out for Backup Compression (ZLIB, LOW, MEDIUM, HIGH, Hybrid Columnar Compression (only Exadata, ZFS Storage Appliances and Pillar Axiom Storage Systems) and for 'SecureFile Compression (user)' and 'SecureFile Deduplication (user)'.

    Then you need to query dba_tables', dba_tab_partitions and dba_tab_subpartitions and look at the COMPRESSION and COMPRESS_FOR columns (OLTP,QUERY,ARCHIVE,ALL_OPERATIONS).

    Probably you also check dba_lobs, dba_lob_partitions, dba_lob_subpartitions for COMPRESSION and DEDUPLICATION.

    Next: for Data Guard Redo Compression have a look at v$archive_dest COMPRESSION column.

    I do not have any clue how to get information about usage of Data Pump with Compression.

    This everything is not enough to give answers like:

    - It is definitely not used.

    - It was definitely not used.

    But if you see some information, which the queries on the views above, you can probably give answers like:

    - I can see that it is used at the moment.

    - I can see that it was used before.

    Hope that helps


  • By the way, Oracle changed Licensing Information Document

    Now also Flashback Data Archive (Total Recall) is part of Advanced Compression Option, the Total Recall Option has disappeared.

    Cheers Mathias

  • We have Oracle 11g EE installed, how do i know if we have the license to use the ACO features as well.

  • Hi Jonu,

    somebody in your company should be responsible for the license management. Find out who is that and just ask him or her. :-)

    Cheers Mathias

  • Thank you Mathias, would you know if there's a way to check that within Oracle itself, some sql command to query or something like that .

  • Hi Mathias

    Thanks for the details :) this is useful

    I was looking for how to enable this feature in 11gR2 and this is useful for that aspect also :)



  • Hi,

    we are started compressions(OLTP Table & LOB) on dev env, would like to know oracle internally what are all the process is doing(Technical details like disk write/reads etc), also came to know that performance impact on DML operations. also while performing compression/in compression enabled table, if there is a major dataload happens on the same table, then in that scienorio what could be the performance issue may encount.

    compression datafiles are moved into new tablespace, then dropping the old tablespace, but some time we couldn't claim the same in OS level, Why ?????

    Pls. guide me  for the above.



  • Hi Siva,

    there are several documents on this topic

    On my blog you will find

    In general I recommend to read through an article series by Jonathan Lewis:

    Cheers Mathias

  • Dear Mathias,

     Thank you so much for your valuable input.

    I required one clarification, in our envirnoment when we are doing compression over partition table,

    where oracle is creating mview & also expecting huge no of temp space , will partition table compression behavious same way ????

    Thanks & Regards,


  • Hi Siva,

    I do not really understand the question. I think that you need to provide more details.

    Maybe it is better to discuss this somewhere else.

    e.g. the oracle forum

    or contact me in Linked In.

    Cheers Mathias

  • Thanks for  your support, I will connect via linked In.

Page 1 of 2 (20 items) 12
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Post