ITRemote

Compression Check Script

#!/bin/ksh
############################################################################
# Script Name..: comp_chk.ksh
# Description..: Estimate the compression for a table
# Author.......: Michael Culp
# Date.........:
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set serveroutput on
-- spool <some file name>
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',
                                                 OWNNAME=>'CFGADM',
                                                 TABNAME=>'EQUIPMENT',
                                                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;
/
-- spool off
EOF

Compression Example