Am currently working on how best to use compression techniques in an OLTP environment on a current 10g database which is due to be migrated to 11.2 in the next 6 months and I came across this OTN note
The following statement got me very interested
This package can be used on Oracle Databases running Oracle Database 9i Release 2 through 11g Release 1.
A compression advisor (DBMS_COMPRESSION) comes packaged with Oracle Database 11g Release 2.
So lets give it a try...
I downloaded the package and compiled it..
Package body created.
SQL> set serveroutput on
SQL> exec dbms_comp_advisor.getratio('OWNER1','CROSS','OLTP',1);
Invalid Compression option for the current COMPAT setting
Thankfully the package body is not encrypted and a quick look at the code reveals that it does the following
SQL> create table DBMS_COMPRESSION_TEMP_UNCMP as select /*+ full('CROSS') */ * from cross sample block( 1);
SQL> create table DBMS_COMPRESSION_TEMP_CMP compress for all operations as select * from DBMS_COMPRESSION_TEMP_UNCMP;
create table DBMS_COMPRESSION_TEMP_CMP compress for all operations as select * from DBMS_COMPRESSION_TEMP_UNCMP
ERROR at line 1:
ORA-00922: missing or invalid option
Hmmm... not sure how they expect "compress for all operations" to work in a pre 11g database...