CREATE USER z_test IDENTIFIED BY welcome1;

GRANT CONNECT, RESOURCE TO z_test;

CREATE TABLESPACE bigdata
   DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

ALTER USER z_test QUOTA UNLIMITED ON bigdata;

CONN z_test/welcome1

CREATE TABLE bigtable
   TABLESPACE bigdata
AS
SELECT rownum id, a.*
  FROM all_objects a
 WHERE 1 = 0;

ALTER TABLE bigtable NOLOGGING;

DECLARE
   l_cnt  NUMBER;
   l_rows NUMBER := 1000000;
BEGIN
   INSERT /*+ APPEND */ INTO bigtable
   SELECT rownum, a.*
     FROM all_objects a;

   l_cnt := SQL%ROWCOUNT;

   COMMIT;

   WHILE (l_cnt < l_rows) LOOP

      INSERT /*+ APPEND */ INTO bigtable
      SELECT rownum+l_cnt, owner, object_name, subobject_name, object_id, data_object_id,
             object_type, created, last_ddl_time, timestamp, status, temporary, generated, 
             secondary, namespace, edition_name, sharing, editionable, oracle_maintained
        FROM bigtable
       WHERE rownum <= l_rows-l_cnt;

      l_cnt := l_cnt + SQL%ROWCOUNT;

      COMMIT;
   END LOOP;

   COMMIT;
END;
/


CONN / as sysdba

SELECT segment_name, SUM(bytes)/1024/1024 MB
  FROM dba_segments
 WHERE segment_name = 'BIGTABLE'
 GROUP BY segment_name;

SET SERVEROUTPUT ON
DECLARE
  v_blocks_comp     PLS_INTEGER;
  v_blocks_uncomp   PLS_INTEGER;
  v_rows_comp       PLS_INTEGER;
  v_rows_uncomp     PLS_INTEGER;
  v_compress_ratio  NUMBER;
  v_compress_type   VARCHAR2(32767);
BEGIN
  DBMS_COMPRESSION.get_compression_ratio (
    scratchtbsname  => 'BIGDATA',
    ownname         => 'Z_TEST',
    objname         => 'BIGTABLE',
    subobjname      => NULL,
    comptype        => 2,
    blkcnt_cmp      => v_blocks_comp,
    blkcnt_uncmp    => v_blocks_uncomp,
    row_cmp         => v_rows_comp,
    row_uncmp       => v_rows_uncomp,
    cmp_ratio       => v_compress_ratio,
    comptype_str    => v_compress_type,
    subset_numrows  => DBMS_COMPRESSION.comp_ratio_minrows
  ) ;

  dbms_output.put_line('OUTPUT: ');
  dbms_output.put_line('Estimated Compression Ratio: '||v_compress_ratio);
  dbms_output.put_line('Blocks used - compressed sample: '||v_blocks_comp);
  dbms_output.put_line('Blocks used - uncompressed sample: '||v_blocks_uncomp);
  dbms_output.put_line('Rows in a block - compressed sample: '||v_rows_comp);
  dbms_output.put_line('Rows in a block - uncompressed sample: '||v_rows_uncomp);
END;
/


DECLARE
  v_blocks_comp     PLS_INTEGER;
  v_blocks_uncomp   PLS_INTEGER;
  v_rows_comp       PLS_INTEGER;
  v_rows_uncomp     PLS_INTEGER;
  v_compress_ratio  NUMBER;
  v_compress_type   VARCHAR2(32767);
BEGIN
  DBMS_COMPRESSION.get_compression_ratio (
    scratchtbsname  => 'BIGDATA',
    ownname         => 'Z_TEST',
    objname         => 'BIGTABLE',
    subobjname      => NULL,
    comptype        => 4,
    blkcnt_cmp      => v_blocks_comp,
    blkcnt_uncmp    => v_blocks_uncomp,
    row_cmp         => v_rows_comp,
    row_uncmp       => v_rows_uncomp,
    cmp_ratio       => v_compress_ratio,
    comptype_str    => v_compress_type,
    subset_numrows  => DBMS_COMPRESSION.comp_ratio_minrows
  ) ;
  
  dbms_output.put_line('OUTPUT: ');
  dbms_output.put_line('Estimated Compression Ratio: '||v_compress_ratio);
  dbms_output.put_line('Blocks used - compressed sample: '||v_blocks_comp);
  dbms_output.put_line('Blocks used - uncompressed sample: '||v_blocks_uncomp);
  dbms_output.put_line('Rows in a block - compressed sample: '||v_rows_comp);
  dbms_output.put_line('Rows in a block - uncompressed sample: '||v_rows_uncomp);
END;
/

-- Clean-Up

DROP USER z_test CASCADE;

DROP TABLESPACE bigdata INCLUDING CONTENTS AND DATAFILES;