-- compressed table
CREATE TABLE table_comp_test(
   anumber NUMBER(10),
   avarchar2 VARCHAR2(100),
   adate DATE)
COMPRESS FOR ALL OPERATIONS;

-- uncompressed table
CREATE TABLE TABLE_TEST(
   anumber NUMBER(10),
   avarchar2 VARCHAR2(100),
   adate DATE);

-- lets verify compression is set properly
SELECT table_name, compression, compress_for
  FROM user_tables
 WHERE table_name IN ('TABLE_TEST', 'TABLE_COMP_TEST');

-- lets insert one million rows in both of them
DECLARE
 x number := 1000000;
BEGIN
 WHILE x > 0 LOOP
  INSERT INTO table_test VALUES (x,RPAD('0',100,'0'),SYSDATE);
  x := x - 1;
 END LOOP;
END;
/

DECLARE
 x number := 1000000;
BEGIN
 WHILE x > 0 LOOP
  INSERT INTO table_comp_test VALUES (x,RPAD('0',100,'0'),SYSDATE);
  x := x - 1;
 END LOOP;
END;
/

COMMIT;

-- lets gather stats
EXEC DBMS_STATS.gather_schema_stats(USER, cascade => TRUE);

-- verify compression 
SELECT table_name, num_rows, blocks, empty_blocks
  FROM user_tables
 WHERE table_name IN ('TABLE_TEST', 'TABLE_COMP_TEST');


CREATE TABLE part_comp_test(
   anumber NUMBER(10),
   avarchar2 VARCHAR2(100),
   adate DATE)
PARTITION BY RANGE (adate) (
  PARTITION PART_COMP_TEST_P1 VALUES LESS THAN (TO_DATE('01/01/2012', 'DD/MM/YYYY')) COMPRESS,
  PARTITION PART_COMP_TEST_P2 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')) COMPRESS FOR DIRECT_LOAD OPERATIONS,
  PARTITION PART_COMP_TEST_P3 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')) COMPRESS FOR OLTP,
  PARTITION PART_COMP_TEST_P4 VALUES LESS THAN (MAXVALUE) NOCOMPRESS
);

-- lets insert one million rows in each partition
DECLARE
 x number := 1000000;
 y date ;
 c number := 4;
BEGIN
 WHILE c > 0 LOOP
  y := to_date('01-06-' || to_char(2010 + c), 'DD-MM-YYYY');
  x := 1000000;    
  WHILE x > 0 LOOP
   INSERT INTO part_comp_test VALUES (x,RPAD('0',100,'0'),y);
   x := x - 1;
  END LOOP;
  c := c - 1;
 END LOOP;
END;
/

COMMIT;

-- gather stats
EXEC DBMS_STATS.gather_schema_stats(USER, cascade => TRUE);

-- verify compression
SELECT partition_name, compression, compress_for, blocks 
  FROM user_tab_partitions
 WHERE table_name = 'PART_COMP_TEST';


-- How to alter compression option

ALTER TABLE table_comp_test NOCOMPRESS;

ALTER TABLE part_comp_test MODIFY PARTITION part_comp_test_p4 COMPRESS FOR OLTP;

SELECT table_name, compression, compress_for
  FROM USER_TABLES 
 WHERE table_name IN ('TABLE_TEST', 'TABLE_COMP_TEST');

SELECT partition_name, compression, compress_for 
  FROM user_tab_partitions
 WHERE table_name = 'PART_COMP_TEST';

-- Clean-up

DROP TABLE table_comp_test;

DROP TABLE table_test;

DROP TABLE part_comp_test;