procedure example works only in 11gR2
you will get this message in 11gR2
Please use DBMS_COMPRESSION package for estimating compression ratio
go to rdbms dir
oracle@apt-amd-02:/opt/app/oracle/product/11.2/rdbms/admin> ls -ltr dbmscomp.sql
-rw-r--r-- 1 oracle oinstall 5180 2009-07-02 13:48 dbmscomp.sql
oracle@apt-amd-02:/opt/app/oracle/product/11.2/rdbms/admin> ls -ltr prvtcomp.plb
-rw-r--r-- 1 oracle oinstall 1226 2009-08-14 22:33 prvtcomp.plb
oracle@apt-amd-02:/opt/app/oracle/product/11.2/rdbms/admin> !sql
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 22 14:48:28 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @dbmscomp.sql
Package created.
Synonym created.
Grant succeeded.
No errors.
SQL> @prvtcomp.plb
Library created.
Package body created.
No errors.
SQL> set serveroutput on
SQL>
DECLARE
blkcnt_cmp pls_integer;
blkcnt_uncmp pls_integer;
row_cmp pls_integer;
row_uncmp pls_integer;
cmp_ratio pls_integer;
comptype_str varchar2(100);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('USERS', 'SCOTT', 'EMP', '',
DBMS_COMPRESSION.COMP_FOR_OLTP,
blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);
DBMS_OUTPUT.PUT_LINE('Block count compressed = ' || blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Block count uncompressed = ' || blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || row_uncmp);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || comptype_str);
DBMS_OUTPUT.PUT_LINE('Compression ratio = '||blkcnt_uncmp/blkcnt_cmp||' to 1');
DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio);
END;
/
Block count compressed = 1
Block count uncompressed = 1
Row count per block compressed = 14
Row count per block uncompressed = 14
Compression type = "Compress For OLTP"
Compression ratio = 1 to 1
Compression ratio org= 1
PL/SQL procedure successfully completed.
===
DECLARE
blkcnt_cmp pls_integer;
blkcnt_uncmp pls_integer;
row_cmp pls_integer;
row_uncmp pls_integer;
cmp_ratio pls_integer;
comptype_str varchar2(100);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('USERS', 'SCOTT', 'ANUJ_BIG_TABLE', '',
DBMS_COMPRESSION.COMP_FOR_OLTP,
blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);
DBMS_OUTPUT.PUT_LINE('Block count compressed = ' || blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Block count uncompressed = ' || blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || row_uncmp);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || comptype_str);
DBMS_OUTPUT.PUT_LINE('Compression ratio = '||blkcnt_uncmp/blkcnt_cmp||' to 1');
DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio);
END;
/
Block count compressed = 12
Block count uncompressed = 194
Row count per block compressed = 550
Row count per block uncompressed = 34
Compression type = "Compress For OLTP"
Compression ratio = 16.16666666666666666666666666666666666667 to 1
Compression ratio org= 16
PL/SQL procedure successfully completed.
col SEGMENT_NAME format a20
select segment_name, bytes, a.blocks, compression, compress_for from dba_segments a, dba_tables b
where a.segment_name = b.table_name
and a.segment_name='ANUJ_BIG_TABLE';
SEGMENT_NAME BYTES BLOCKS COMPRESS COMPRESS_FOR
-------------------- ---------- ---------- -------- ------------
ANUJ_BIG_TABLE 26673152 3256 DISABLED
declare
lv_cmp_ratio number;
lv_comptype_str varchar2(300);
lv_BLKCNT_CMP number;
lv_BLKCNT_UNCMP number;
lv_ROW_CMP number;
lv_ROW_UNCMP number;
begin
dbms_compression.GET_COMPRESSION_RATIO(
SCRATCHTBSNAME=>'USERS', ---- TableSpace
OWNNAME=>'SCOTT', ---- User Name
TABNAME=>'ANUJ_BIG_TABLE', --- Table Name
PARTNAME =>null,
COMPTYPE =>2, ---2 means OLTP
BLKCNT_CMP=>lv_BLKCNT_CMP,
BLKCNT_UNCMP=>lv_BLKCNT_UNCMP,
ROW_CMP =>lv_ROW_CMP,
ROW_UNCMP =>lv_ROW_UNCMP,
CMP_RATIO=>lv_cmp_ratio,
COMPTYPE_STR=>lv_COMPTYPE_STR);
dbms_output.put_line('====================================================');
dbms_output.put_line('1. Compression Ratio :'||lv_cmp_ratio);
dbms_output.put_line('2. Block Count :'||lv_blkcnt_cmp);
dbms_output.put_line('3. Compression Type :'||lv_comptype_str);
dbms_output.put_line('4. Blk Count Compressed :'||lv_BLKCNT_CMP);
dbms_output.put_line('5. Blk Count Un-compressed:'||lv_BLKCNT_UNCMP);
dbms_output.put_line('6. Row Count Compressed :'||lv_row_cmp);
dbms_output.put_line('4. Row Count Un-Compressed:'||lv_row_uncmp);
dbms_output.put_line('====================================================');
end;
/
====================================================
1. Compression Ratio :17.25
2. Block Count :12
3. Compression Type :"Compress For OLTP"
4. Blk Count Compressed :12
5. Blk Count Un-compressed:207
6. Row Count Compressed :584
4. Row Count Un-Compressed:33
====================================================
PL/SQL procedure successfully completed.
