0 Replies Latest reply on Feb 3, 2014 9:17 PM by User606689

    Running DBMS_COMPRESSION PL/SQL Package in oracle 11g

    User606689

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