Convert non-partition table to partition table in Oracle 11g R2

In this procedure, we are using Oracle redefinition technique.

Goals:

1-    To convert the non-partition table to partition table

2-    No or minimal application downtime

3-    Dependent objects should also get created

4-    Option to convert current non-primary key global index to local indexes

5-    Option to fall back to the initial configuration

In this procedure, we have used ISL_MTNC schema to redefine ISL_TRX_EVENT_DETAIL table by using DBMS_REDEFINITION Package.

Step1:

Check the existing table by generate the script:

CREATE TABLE ISL_MTNC.ISL_TRX_EVENT_DETAIL

(

  TRX_ID             CHAR(32 BYTE) CONSTRAINT CK_TRXEDTL_TRXID NOT NULL,

  TRX_EVENT_ID       CHAR(32 BYTE) CONSTRAINT CK_TRXEDTL_TRXEVI NOT NULL,

  TRX_EVENT_TYPE_ID  NUMBER(19) CONSTRAINT CK_TRXEDTL_TRXEVT NOT NULL,

  DISTRIBUTORNAME    VARCHAR2(100 BYTE),

  CREATE_DATE_TIME   TIMESTAMP(6)               DEFAULT SYSTIMESTAMP CONSTRAINT CK_TRXEDTL_CREDATT NOT NULL

)

TABLESPACE ISL_TAB_TBSP

Step 2:

Check to make sure the table can use the online redefinition feature:

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('ISL_MTNC','ISL_TRX_EVENT_DETAIL');

PL/SQL procedure successfully completed.

Step 3:

Create a temporary partitioned table like original table without any constraints:

SQL> CREATE TABLE ISL_MTNC.ISL_TRX_EVENT_DETAIL_TEMP

(

  TRX_ID             CHAR(32 BYTE),

  TRX_EVENT_ID       CHAR(32 BYTE),

  TRX_EVENT_TYPE_ID  NUMBER(19),

  DISTRIBUTORNAME    VARCHAR2(100 BYTE),

  CREATE_DATE_TIME   TIMESTAMP(6)

)

TABLESPACE ISL_TAB_TBSP

PARTITION BY RANGE (CREATE_DATE_TIME)

(

PARTITION P20160901 VALUES LESS THAN (TO_DATE(' 2016-09-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160902 VALUES LESS THAN (TO_DATE(' 2016-09-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160903 VALUES LESS THAN (TO_DATE(' 2016-09-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160904 VALUES LESS THAN (TO_DATE(' 2016-09-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160905 VALUES LESS THAN (TO_DATE(' 2016-09-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160906 VALUES LESS THAN (TO_DATE(' 2016-09-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160907 VALUES LESS THAN (TO_DATE(' 2016-09-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160908 VALUES LESS THAN (TO_DATE(' 2016-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160909 VALUES LESS THAN (TO_DATE(' 2016-09-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160910 VALUES LESS THAN (TO_DATE(' 2016-09-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160911 VALUES LESS THAN (TO_DATE(' 2016-09-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160912 VALUES LESS THAN (TO_DATE(' 2016-09-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160913 VALUES LESS THAN (TO_DATE(' 2016-09-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160914 VALUES LESS THAN (TO_DATE(' 2016-09-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160915 VALUES LESS THAN (TO_DATE(' 2016-09-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160916 VALUES LESS THAN (TO_DATE(' 2016-09-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160917 VALUES LESS THAN (TO_DATE(' 2016-09-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160918 VALUES LESS THAN (TO_DATE(' 2016-09-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160919 VALUES LESS THAN (TO_DATE(' 2016-09-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160920 VALUES LESS THAN (TO_DATE(' 2016-09-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160921 VALUES LESS THAN (TO_DATE(' 2016-09-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160922 VALUES LESS THAN (TO_DATE(' 2016-09-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160923 VALUES LESS THAN (TO_DATE(' 2016-09-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160924 VALUES LESS THAN (TO_DATE(' 2016-09-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160925 VALUES LESS THAN (TO_DATE(' 2016-09-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160926 VALUES LESS THAN (TO_DATE(' 2016-09-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160927 VALUES LESS THAN (TO_DATE(' 2016-09-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160928 VALUES LESS THAN (TO_DATE(' 2016-09-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160929 VALUES LESS THAN (TO_DATE(' 2016-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP,

PARTITION P20160930 VALUES LESS THAN (TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE ISL_TAB_TBSP

);

Table created.

Step 4:

Start the online redefinition process:

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('ISL_MTNC','ISL_TRX_EVENT_DETAIL','ISL_TRX_EVENT_DETAIL_TEMP');

PL/SQL procedure successfully completed.

Step 5:

Copy dependent objects:

SQL> VARIABLE NUM_ERRORS NUMBER;

SQL> EXEC DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ISL_MTNC','ISL_TRX_EVENT_DETAIL','ISL_TRX_EVENT_DETAIL_TEMP', 1,TRUE,TRUE,TRUE,FALSE,:NUM_ERRORS,FALSE);

PL/SQL procedure successfully completed.

SQL> PRINT NUM_ERRORS

 NUM_ERRORS

----------         

0

Step 6:

Resync the table. This will copy the initial data from the ISL_TRX_EVENT_DETAIL to ISL_TRX_EVENT_DETAIL_TEMP table.

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('ISL_MTNC','ISL_TRX_EVENT_DETAIL','ISL_TRX_EVENT_DETAIL_TEMP');

PL/SQL procedure successfully completed.

Step 7:

Finish the online redefinition. Here the redefinition process will be over and interim table will become original table.

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('ISL_MTNC','ISL_TRX_EVENT_DETAIL','ISL_TRX_EVENT_DETAIL_TEMP');

PL/SQL procedure successfully completed.

Step 7

Verify the structure of the new partitioned table.

SQL> Select partition_name, high_value from dba_tab_partitions where table_owner='ISL_MTNC' and table_name='ISL_TRX_EVENT_DETAIL';

Note:

If we want to abort the redefinition process in between, we can use abort procedure:

SQL> EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE ('SCOTT', 'ISL_TRX_EVENT_DETAIL','ISL_TRX_EVENT_DETAIL_TEMP',NULL);

Limitations to the redefinition:

1.    Cannot belong to SYS or SYSTEM schema

2.    The table to be redefined online should not be using User-defined data types

3.    Should not be a clustered table

4.    Should not have Materialized View Logs or Materialized views defined

5.    Table owner cannot be changed, as part of re-definition

6.    Additional space requirement for the interim table (same as original table)