Forum Stats

  • 3,839,357 Users
  • 2,262,486 Discussions
  • 7,900,949 Comments

Discussions

Running DBMS_COMPRESSION PL/SQL Package in oracle 11g

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;
/

This discussion has been closed.