Script to find out space saving with HCC

The following scrips loops through a list of tables and displays estimated compression ratio for all the tables for all types of HCC compression :


set serveroutput on
spool all.out
declare
v_blkcnt_cmp pls_integer;
v_blkcnt_uncmp pls_integer;
v_row_cmp pls_integer;
v_row_uncmp pls_integer;
v_cmp_ratio number;
v_comptype_str varchar2(60);
type Str_Array is VARRAY(10) of VARCHAR2(50);
v_array Str_Array ;
begin
v_array:= Str_Array('lineitem','nation');
for i in v_array.first .. v_array.last loop
        dbms_output.put_line('hello ' || v_array(i));
end loop;
for i in v_array.first .. v_array.last
loop
for j in 1..5
loop
dbms_compression.get_compression_ratio(
scratchtbsname => upper('scratch1'),
ownname => upper('tpch'),
tabname => upper(v_array(i)),
partname => NULL,
comptype => power(2,j),
blkcnt_cmp => v_blkcnt_cmp,
blkcnt_uncmp => v_blkcnt_uncmp,
row_cmp => v_row_cmp,
row_uncmp => v_row_uncmp,
cmp_ratio => v_cmp_ratio,
comptype_str => v_comptype_str,
subset_numrows => -1) ;
dbms_output.put_line('OUTPUT FOR ' || v_comptype_str ||' FOR TABLE ' || v_array(i)) ;
dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio));
dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp));
dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp));
end LOOP;
end LOOP;
end;
/
;

Cannot open database due to corrupt file

If you cannot open database due to some non-critical datafile being corrupt or not found and you do not have the backup either, you can offline the corrupt file and restart the database ....

Starting the database gave the following error:


SQL> SQL> ORACLE instance started.

Total System Global Area 1.4008E+11 bytes
Fixed Size                  2166320 bytes
Variable Size            5.3687E+10 bytes
Database Buffers         8.5899E+10 bytes
Redo Buffers              489947136 bytes
Database mounted.
ORA-01122: database file 166 failed verification check
ORA-01110: data file 166: '/test1/sf1000/scratch_ts'
ORA-01210: data file header is media corrupt

Login to the database and offline that particular datafile


-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 28 14:38:51 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


SQL> alter database datafile '/test1/sf1000/scratch_ts' offline for drop
  2  ;

Database altered.

SQL> alter database open;

Database altered.