Creating Range Partition on date column using numtodsinterval

==========CH_NOBOOK=====================================
Step 1: Rename CH_NOBOOK to CH_NOBOOK_TEMP.
SQL> RENAME CH_NOBOOK TO CH_NOBOOK_TEMP;


Step 2: Create CH_NOBOOK partitioned table.
CREATE TABLE CH_NOBOOK
   (    "DAT_TXN" DATE,
        "COD_CC_BRN_TXN" NUMBER(5,0),
        "COD_USERNO" NUMBER(10,0),
        "CTR_BATCH_NO" NUMBER(10,0),
        "REF_SYS_TR_AUD_NO" NUMBER(10,0),
        "REF_SUB_SEQ_NO" NUMBER(10,0),
        "COD_ACCT_NO" CHAR(48 CHAR),
        "TXT_TXN_DESC" VARCHAR2(120 CHAR),
        "DAT_VALUE" DATE,
        "REF_CHQ_NO" VARCHAR2(36 CHAR),
        "COD_DRCR" CHAR(3 CHAR),
        "COD_TXN_MNEMONIC" NUMBER(5,0),
        "COD_TXN_LITERAL" VARCHAR2(9 CHAR),
        "AMT_TXN" NUMBER DEFAULT 0,
        "FLG_PASBKUPD" CHAR(3 CHAR),
        "RAT_CCY" NUMBER,
        "COD_MSG_TYP" NUMBER(5,0),
        "COD_PROC" NUMBER(10,0),
        "CTR_UPDAT_SRLNO" NUMBER(10,0),
        "DAT_POST" DATE,
        "AMT_TXN_TCY" NUMBER DEFAULT 0,
        "COD_TXN_CCY" NUMBER(5,0),
        "RAT_CONV_TCLCY" NUMBER,
        "COD_SC" NUMBER(5,0),
        "COD_AUTH_ID" VARCHAR2(36 CHAR),
         CHECK (cod_drcr IN ('C','D')) ENABLE
   )  partition by range (dat_post)
   interval (numtodsinterval(1,'DAY'))
   (partition p0 values less  than 
    (to_date('31-OCT-2001','DD-MON-YYYY')));

Step 2: Insert into table and commit. 
    SQL> insert /*+append parallel(10) */ into RPRUSRBASE.CH_NOBOOK select /*+ parallel(10)*/  * from RPRUSRBASE.CH_NOBOOK_TEMP;
SQL> commit;

Steps 2.1: Drop Indexes on CH_NOBOOK

SQL> DROP INDEX RPRUSRBASE.IN_CH_NOBOOK_1;
SQL> DROP INDEX RPRUSRBASE.IN_CH_NOBOOK_2;
SQL> DROP INDEX RPRUSRBASE.IN_CH_NOBOOK_3;

Step 3: Create partitioned indexes.


 SQL> CREATE INDEX "RPRUSRBASE"."IN_CH_NOBOOK_1" ON "RPRUSRBASE"."CH_NOBOOK" ("COD_ACCT_NO", "DAT_TXN")
  PCTFREE 15 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 786432000 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FCUBSTBS" ;

 SQL>  CREATE INDEX "RPRUSRBASE"."IN_CH_NOBOOK_2" ON "RPRUSRBASE"."CH_NOBOOK" ("COD_ACCT_NO", "DAT_POST")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 534773760 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FCUBSTBS" ;
  
  SQL> CREATE INDEX "RPRUSRBASE"."IN_CH_NOBOOK_3" ON "RPRUSRBASE"."CH_NOBOOK" ("DAT_POST")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 282066944 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FCUBSTBS" LOCAL;

Step 4: Verification 
A. Count number of partitions in CH_NOBOOK,
SQ> select * from dba_tab_partitions where table_name='CH_NOBOOK_TEMP' and table_owner='RPRUSRBASE';

B. verify indexes
SQL> select * from dba_indexes where table_name='CH_NOBOOK_TEMP' and table_owner='RPRUSRBASE';

C. verify table row count between CH_NOBOOK and CH_NOBOOK_TEMP;
SQL> select /*+parallel(20) */  count(*) from RPRUSRBASE.CH_NOBOOK ;
SQL> select /*+parallel(20) */  count(*) from RPRUSRBASE.CH_NOBOOK_TEMP ;


Step5: Drop table CH_NOBOOK_TEMP.
SQL> DROP TABLE RPRUSRBASE.CH_NOBOOK_TEMP;

==========CH_NOBOOK_ORG=====================================
Step 1: Rename CH_NOBOOK_ORG to CH_NOBOOK_ORG_TEMP
SQL> RENAME CH_NOBOOK_ORG TO CH_NOBOOK_ORG_TEMP;

Step 2: Create CH_NOBOOK_ORG as partitioned table;

 SQL>  CREATE TABLE "CH_NOBOOK_ORG"
   (    "DAT_TXN" DATE,
        "COD_CC_BRN_TXN" NUMBER(5,0),
        "COD_USERNO" NUMBER(10,0),
        "CTR_BATCH_NO" NUMBER(10,0),
        "REF_SYS_TR_AUD_NO" NUMBER(10,0),
        "REF_SUB_SEQ_NO" NUMBER(10,0),
        "COD_ACCT_NO" CHAR(48 CHAR),
        "TXT_TXN_DESC" VARCHAR2(120 CHAR),
        "DAT_VALUE" DATE,
        "REF_CHQ_NO" VARCHAR2(36 CHAR),
        "COD_DRCR" CHAR(3 CHAR),
        "COD_TXN_MNEMONIC" NUMBER(5,0),
        "COD_TXN_LITERAL" VARCHAR2(9 CHAR),
        "AMT_TXN" NUMBER DEFAULT 0,
        "FLG_PASBKUPD" CHAR(3 CHAR),
        "RAT_CCY" NUMBER,
        "COD_MSG_TYP" NUMBER(5,0),
        "COD_PROC" NUMBER(10,0),
        "CTR_UPDAT_SRLNO" NUMBER(10,0),
        "DAT_POST" DATE,
        "AMT_TXN_TCY" NUMBER DEFAULT 0,
        "COD_TXN_CCY" NUMBER(5,0),
        "RAT_CONV_TCLCY" NUMBER,
        "COD_SC" NUMBER(5,0),
        "COD_AUTH_ID" VARCHAR2(36 CHAR)
   ) ENABLE ROW MOVEMENT    partition by range (dat_post)
   interval (numtodsinterval(1,'DAY'))
   (partition p0 values less  than 
    (to_date('07-DEC-2014','DD-MON-YYYY')));


Step 2: Insert into CH_NOBOOK_ORG from CH_NOBOOK_ORG_TEMP table.    
    insert /*+append parallel(10) */ into REPUSRBASE.CH_NOBOOK_ORG select /*+ parallel(10)*/  * from REPUSRBASE.CH_NOBOOK_ORG_TEMP;


Steps 2.1: Drop Indexes on CH_NOBOOK_ORG_TEMP

SQL> DROP INDEX REPUSRBASE.MIG_CH_NOBOOK_ORG_1;
SQL> DROP INDEX REPUSRBASE.IN_CH_NOBOOK_ORG_1;
SQL> DROP INDEX REPUSRBASE.IN_CH_NOBOOK_ORG_2;

Step 3: Create partitioned indexes.

  
  SQL> CREATE INDEX "REPUSRBASE"."MIG_CH_NOBOOK_ORG_1" ON "REPUSRBASE"."CH_NOBOOK_ORG" ("COD_ACCT_NO", "COD_TXN_MNEMONIC", "COD_PROC")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FCUBSTBS" LOCAL PARALLEL 10;

  
  
  SQL> CREATE INDEX "REPUSRBASE"."IN_CH_NOBOOK_ORG_1" ON "REPUSRBASE"."CH_NOBOOK_ORG" ("COD_ACCT_NO", "DAT_TXN")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 104857600 NEXT 31457280 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FCUBSTBS" LOCAL PARALLEL 10;

  SQL> CREATE INDEX "REPUSRBASE"."IN_CH_NOBOOK_ORG_2" ON "REPUSRBASE"."CH_NOBOOK_ORG" ("DAT_POST")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 104857600 NEXT 31457280 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FCUBSTBS" LOCAL PARALLEL 10;


Step 4: Verification 
A. Count number of partitions in CH_NOBOOK_ORG,
SQ> select * from dba_tab_partitions where table_name='CH_NOBOOK_ORG' and table_owner='REPUSRBASE';

B. verify indexes
SQL> select * from dba_indexes where table_name='CH_NOBOOK_ORG' and table_owner='REPUSRBASE';

C. verify table row count between CH_NOBOOK_ORG and CH_NOBOOK_ORG_TEMP;
SQL> select /*+parallel(20) */  count(*) from REPUSRBASE.CH_NOBOOK_ORG ;
SQL> select /*+parallel(20) */  count(*) from REPUSRBASE.CH_NOBOOK_ORG_TEMP ;


Step5: Drop table CH_NOBOOK_ORG_TEMP.
SQL> DROP TABLE REPUSRBASE.CH_NOBOOK_ORG_TEMP;