MIGRATION PARTITIONNED TABLE : FROM LIST TO RANGE

Goal :

This note is intented to migrate a table that has been created with LIST to a table with RANGE partition in order to use tha automatic partition creation.

Initial Table: LIST Partition

CREATE TABLE SRLTFR1.MY_TABLE
(
  COL1        NUMBER(3)            NOT NULL,
  COL2        NUMBER(5)            NOT NULL,
  COL3        DATE NOT NULL ,
  COL4        VARCHAR2(3 CHAR)     NOT NULL
)
LOGGING
PARTITION BY LIST (COL3)
( 
  PARTITION T20171220 VALUES (TO_DATE(' 2017-12-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION T20171221 VALUES (TO_DATE(' 2017-12-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION COL3_OTHERS VALUES (DEFAULT)
)
NOCACHE
MONITORING;

some indexes ...

Migration

 1 .Export

 expdp my-user/pwd ... tables=MY_TABLE dumpfile=MY_TABLE.dump ...

 2 .Rename

Rename the table and all its indexes, constraint ..
  alter table MY_TABLE rename to MY_TABLE_OLD;

 alter index ...

3. Create the table with new DDL

CREATE TABLE SRLTFR1.MY_TABLE
(
  COL1        NUMBER(3)            NOT NULL,
  COL2        NUMBER(5)            NOT NULL,
  COL3        DATE NOT NULL ,
  COL4        VARCHAR2(3 CHAR)     NOT NULL
)
LOGGING
PARTITION BY RANGE  (COL3)
INTERVAL( NUMTODSINTERVAL(1, 'DAY'))
( 
  PARTITION T20171220 VALUES (TO_DATE(' 2017-12-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
NOCACHE
MONITORING;

 4. import the dump

impdp my-user/pwd   dumpfile=MY_TABLE.dump tables=MY_TABLE table_exists_action=append parallel=4 ...

5. indexes

create the indexes for the table

6. gather stats