We are on the process of reviewing our storage hardware, as such we have got this query from our oracle hardware agents to run and see the DB compression rate.. Can you let me know how can i run this.
DBMS_COMPRESSION PL/SQL Package
Estimates Hybrid Columnar Compress storage savings on non
Exadata hardware
Requires Patch # 8937922
set serveroutput on
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);
begin
dbms_compression.get_compression_ratio(
scratchtbsname => upper('&Tablespace'), -- Tablespace Name
ownname => upper('&UserName'), -- USER NAME
tabname => upper('&TableName'), -- TABLE NAME
partname => NULL,
comptype => dbms_compression.comp_for_query_high, --compression type
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);
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;
/