Oracle 11gr2 compression

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.