Oracle has made available a package you can use with Oracle Database 9i Release 2 through Oracle Database 11g release 1 - this is DBMS_COMP_ADVISOR available for download here.
This script is made to run for all versions, 9i to 12c, and requires only the database version as an input.
DECLARE
v_owner VARCHAR2(60);
v_segment_name VARCHAR2(60);
v_execute112 varchar2(4000);
v_execute10 varchar2(4000);
v_execute varchar2(4000);
BEGIN
v_execute112 := q'[DECLARE
l_blkcnt_cmp pls_integer;
l_blkcnt_uncmp pls_integer;
l_row_cmp pls_integer;
l_row_uncmp pls_integer;
l_cmp_ratio NUMBER;
l_comptype_str VARCHAR2(60);
begin dbms_compression.get_compression_ratio(
-- input parameters
scratchtbsname => 'SYSAUX', -- scratch tablespace
ownname => :v_owner, -- owner of the table
tabname => :v_segment_name, -- table name
partname => NULL, -- partition name
comptype => dbms_compression.COMP_FOR_OLTP, -- compression algorithm
-- output parameters
blkcnt_cmp => l_blkcnt_cmp, -- number of compressed blocks
blkcnt_uncmp => l_blkcnt_uncmp, -- number of uncompressed blocks
row_cmp => l_row_cmp, -- number of rows in a compressed block
row_uncmp => l_row_uncmp, -- number of rows in an uncompressed block
cmp_ratio => l_cmp_ratio, -- compression ratio
comptype_str => l_comptype_str -- compression type
);
dbms_output.put_line('SegmentOwner: '||:v_owner||' SegmentName: '||:v_segment_name||' - '||' Ratio: '||to_char(l_cmp_ratio,'99.99'));
end;]';
v_execute10 := q'[declare
l_compression NUMBER:= 10;
begin dbms_comp_advisor.getratio(:v_owner,:v_segment_name,l_compression);
end;]';
if substr('&db_version',1,4)='11.2' or substr('&&db_version',1,2)='12' then
v_execute := v_execute112;
else
v_execute := v_execute10;
end if;
FOR rec IN (select segment_name, owner
from (
select * from
(select dba_tables.owner, segment_name, segment_type, dba_extents.tablespace_name, round(sum(dba_extents.bytes/1024/1024),2) SizeMB,
dba_tables.num_rows
from dba_extents, dba_tables
where segment_type = 'TABLE'
and dba_tables.table_name = dba_extents.segment_name
and dba_tables.compression = 'DISABLED'
group by dba_tables.owner, segment_name, segment_type, dba_extents.tablespace_name,
dba_tables.num_rows,
to_char(last_analyzed, 'DD-MON-RRRR')
having sum(dba_extents.bytes/1024/1024) > 10
)
order by SizeMB desc)
where rownum <= 50)
LOOP
v_owner := rec.owner;
v_segment_name := rec.segment_name;
execute immediate v_execute using in v_owner,v_segment_name;
END LOOP;
END;
/
