1 Reply Latest reply: Apr 4, 2013 11:47 AM by rp0428 RSS

    PARTITIONING HELP

    Data Boy
      we need to create below partitions and compress the partions only until Q1FY12 on a existing table..

      PARTITION BY RANGE (CREATION_DATE)
      (PARTITION Q3FY11 VALUES LESS THAN (TO_DATE ('01-AUG-2012')),
      PARTITION Q4FY11 VALUES LESS THAN (TO_DATE ('01-NOV-2012')),
      PARTITION Q1FY12 VALUES LESS THAN (TO_DATE ('01-FEB-2013')),
      PARTITION Q2FY12 VALUES LESS THAN (TO_DATE ('01-MAY-2013'),
      PARTITION Q3FY12 VALUES LESS THAN (TO_DATE ('01-AUG-2013'),
      PARTITION Q4FY12 VALUES LESS THAN (TO_DATE ('01-NOV-2013'),
      PARTITION MAX_PARTITION VALUES LESS THAN (TO_DATE ('01-FEB-4074'))
      )

      Can someone please help me with steps ..

      Thanks a lot in advance
        • 1. Re: PARTITIONING HELP
          rp0428
          >
          we need to create below partitions and compress the partions only until Q1FY12 on a existing table..
          >
          You can't partition an existing table.

          You need to either redefine the table online using the DBMS_REDEFINITION package or create a new table and move the data to it.

          See the Oracle DBA doc
          http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm

          And this Oracle-base article
          http://www.oracle-base.com/articles/11g/online-table-redefinition-enhancements-11gr1.php

          This works for me. It creates a new table, inserts the old data into it and compresses some of the partitions.
          drop table emp_part
          
          CREATE TABLE EMP_PART
          PARTITION BY RANGE (HIREDATE)
          (PARTITION Q3FY11 VALUES LESS THAN (TO_DATE ('01-AUG-2012')) compress,
          PARTITION Q4FY11 VALUES LESS THAN (TO_DATE ('01-NOV-2012')) compress,
          PARTITION Q1FY12 VALUES LESS THAN (TO_DATE ('01-FEB-2013')) compress,
          PARTITION Q2FY12 VALUES LESS THAN (TO_DATE ('01-MAY-2013')) compress,
          PARTITION Q3FY12 VALUES LESS THAN (TO_DATE ('01-AUG-2013')) compress,
          PARTITION Q4FY12 VALUES LESS THAN (TO_DATE ('01-NOV-2013')),
          PARTITION MAX_PARTITION VALUES LESS THAN (TO_DATE ('01-FEB-4074')) 
          )
           AS SELECT * FROM EMP
          
          select PARTITION_NAME, COMPRESSION, COMPRESS_FOR
           from user_tab_partitions where table_name = 'EMP_PART'
          
          PARTITION_NAME     COMPRESSION     COMPRESS_FOR
          Q3FY11     ENABLED     BASIC
          Q4FY11     ENABLED     BASIC
          Q1FY12     ENABLED     BASIC
          Q2FY12     ENABLED     BASIC
          Q3FY12     ENABLED     BASIC
          Q4FY12     DISABLED     
          MAX_PARTITION     DISABLED