Partitioning an Existing Table in Oracle Database Using DBMS_REDEFINITION Package

SQL>
SQL> CREATE TABLE MAIN_TABLE
  2  (ACC_NUM NUMBER(14) CONSTRAINT PK_MAIN_TABLE_ACC_NUM PRIMARY KEY,
  3  ACC_TITLE VARCHAR2(300),
  4  OPPENING_DATE DATE
  5  );

Table created.

SQL> BEGIN
  2  FOR I IN 1.. 100000 LOOP
  3  INSERT INTO MAIN_TABLE(ACC_NUM, ACC_TITLE, OPPENING_DATE)
  4  VALUES (I, 'ACC TITLE '||I, TRUNC(SYSDATE)-I);
  5  END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL>
SQL> CREATE TABLE PART_TABLE
  2  (ACC_NUM NUMBER(14) CONSTRAINT PK_PART_TABLE_ACC_NUM PRIMARY KEY,
  3  ACC_TITLE VARCHAR2(300),
  4  OPPENING_DATE DATE
  5  )
  6  TABLESPACE DATA
  7  PARTITION BY RANGE  (OPPENING_DATE)
  8  (
  9  PARTITION ACC_MON2014_JAN VALUES LESS THAN (TO_DATE('31/01/2014', 'DD/MM/YYYY')),
 10  PARTITION ACC_MON2014_FEB VALUES LESS THAN (TO_DATE('28/02/2014', 'DD/MM/YYYY')),
 11  PARTITION ACC_MON2014_MAR VALUES LESS THAN (TO_DATE('31/03/2014', 'DD/MM/YYYY')),
 12  PARTITION ACC_MON2014_APR VALUES LESS THAN (TO_DATE('30/04/2014', 'DD/MM/YYYY')),
 13  PARTITION ACC_MON2014_MAY VALUES LESS THAN (TO_DATE('31/05/2014', 'DD/MM/YYYY')),
 14  PARTITION ACC_MON2014_JUN VALUES LESS THAN (TO_DATE('30/06/2014', 'DD/MM/YYYY')),
 15  PARTITION ACC_MON2014_JUL VALUES LESS THAN (TO_DATE('31/07/2014', 'DD/MM/YYYY')),
 16  PARTITION ACC_MON2014_AUG VALUES LESS THAN (TO_DATE('31/08/2014', 'DD/MM/YYYY')),
 17  PARTITION ACC_MON2014_SEP VALUES LESS THAN (TO_DATE('30/09/2014', 'DD/MM/YYYY')),
 18  PARTITION ACC_MON2014_OCT VALUES LESS THAN (TO_DATE('31/10/2014', 'DD/MM/YYYY')),
 19  PARTITION ACC_MON2014_NOV VALUES LESS THAN (TO_DATE('30/11/2014', 'DD/MM/YYYY')),
 20  PARTITION ACC_MON2014_DEC VALUES LESS THAN (TO_DATE('31/12/2014', 'DD/MM/YYYY'))
 21  );

Table created.

SQL> EXEC Dbms_Redefinition.Can_Redef_Table(USER, 'MAIN_TABLE');

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    DBMS_REDEFINITION.start_redef_table(
  3      uname      => USER,
  4      orig_table => 'MAIN_TABLE',
  5      int_table  => 'PART_TABLE');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'PART_TABLE', cascade => TRUE);

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    dbms_redefinition.finish_redef_table(
  3      uname      => USER,
  4      orig_table => 'MAIN_TABLE',
  5      int_table  => 'PART_TABLE');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME='MAIN_TABLE';

TABLE_NAME                     COMPRESS COMPRESS_FOR                          
------------------------------ -------- ------------                          
MAIN_TABLE                                                                    

SQL> SET LINE 2000
SQL> /

TABLE_NAME                     PARTITION_NAME                 COMPRESS COMPRESS_FOR          
------------------------------ ------------------------------ -------- ------------                                      
MAIN_TABLE                     ACC_MON2014_JAN                DISABLED                                               MAIN_TABLE                     ACC_MON2014_FEB                DISABLED                                             MAIN_TABLE                     ACC_MON2014_MAR                DISABLED                                             MAIN_TABLE                     ACC_MON2014_APR                DISABLED                                             MAIN_TABLE                     ACC_MON2014_MAY                DISABLED                                             MAIN_TABLE                     ACC_MON2014_JUN                DISABLED                                               MAIN_TABLE                     ACC_MON2014_JUL                DISABLED                                               MAIN_TABLE                     ACC_MON2014_AUG                DISABLED                                             MAIN_TABLE                     ACC_MON2014_SEP                DISABLED                                               MAIN_TABLE                     ACC_MON2014_OCT                DISABLED                                             MAIN_TABLE                     ACC_MON2014_NOV                DISABLED                                             MAIN_TABLE                     ACC_MON2014_DEC                DISABLED                                             12 rows selected.

SQL> SELECT 'ALTER TABLE MAIN_TABLE MODIFY PARTITION '||PARTITION_NAME||' COMPRESS FOR ALL OPERATIONS;'
  2  FROM USER_TAB_PARTITIONS
  3  WHERE TABLE_NAME='MAIN_TABLE';

'ALTERTABLEMAIN_TABLEMODIFYPARTITION'||PARTITION_NAME||'COMPRESSFORALLOPERATIONS;'                                                                                                                                             --------------------------------------------------------------------------------------------------                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_JAN COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_FEB COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_MAR COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_APR COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_MAY COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_JUN COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_JUL COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_AUG COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_SEP COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_OCT COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_NOV COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_DEC COMPRESS FOR ALL OPERATIONS;                                                                                                                                 12 rows selected.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_JAN COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_FEB COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_MAR COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_APR COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_MAY COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_JUN COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_JUL COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_AUG COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_SEP COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_OCT COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_NOV COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_DEC COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL>
SQL>
SQL> SELECT TABLE_NAME, PARTITION_NAME, COMPRESSION, COMPRESS_FOR
  2  FROM USER_TAB_PARTITIONS
  3  WHERE TABLE_NAME='MAIN_TABLE';

TABLE_NAME                     PARTITION_NAME                 COMPRESS COMPRESS_FOR             ------------------------------ ------------------------------ -------- ------------                                         MAIN_TABLE                     ACC_MON2014_JAN                ENABLED  OLTP                                   MAIN_TABLE                     ACC_MON2014_FEB                ENABLED  OLTP                                   MAIN_TABLE                     ACC_MON2014_MAR                ENABLED  OLTP                                 MAIN_TABLE                     ACC_MON2014_APR                ENABLED  OLTP                                   MAIN_TABLE                     ACC_MON2014_MAY                ENABLED  OLTP                                 MAIN_TABLE                     ACC_MON2014_JUN                ENABLED  OLTP                                   MAIN_TABLE                     ACC_MON2014_JUL                ENABLED  OLTP                                     MAIN_TABLE                     ACC_MON2014_AUG                ENABLED  OLTP                                   MAIN_TABLE                     ACC_MON2014_SEP                ENABLED  OLTP                                   MAIN_TABLE                     ACC_MON2014_OCT                ENABLED  OLTP                                   MAIN_TABLE                     ACC_MON2014_NOV                ENABLED  OLTP                                 MAIN_TABLE                     ACC_MON2014_DEC                ENABLED  OLTP                                                                                                                                                                                                    
12 rows selected.

SQL> SPOOL OFF;