create a partition table using a exsit table

Step 4:
======
Steps to partition the table
1)
Please partition the table DME_BATCH_OUTBOUND_PAYLOAD with the same
partitioning rules as the DME_OUTBOUND_PAYLOAD table is having.

Please note that DME_BATCH_OUTBOUND_PAYLOAD is an existing table and it is not
empty. New partitioned table must have the same Indexes, Constraints and
Grants as the original
table is having.

Question: please check whether we can use the following approach to partition the table.[Rastogi, Shalabh]  These steps are fine. Apart from these you will also need to add an entry into the RETENTION_PERIODS_BY_PART table to match the retention period of DME_OUTBOUND_PAYLOAD table.

Step 1. Create a new partition table DME_BATCH_OUTBOUND_PAYLOAD_NEW using the partition option

 CREATE TABLE "DMEDBA"." DME_BATCH_OUTBOUND_PAYLOAD_NEW"
   (    "BATCH_REQ_ID" NUMBER(15,0) NOT NULL ENABLE,
        "EXTRACT_MESSAGE_TEXT" VARCHAR2(2000),
        "RECORD_FLAG" CHAR(1) NOT NULL ENABLE,
        "CRT_LOGIN" VARCHAR2(30) NOT NULL ENABLE,
        "CRT_DT" DATE NOT NULL ENABLE,
        "UPD_LOGIN" VARCHAR2(30) NOT NULL ENABLE,
        "UPD_DT" DATE NOT NULL ENABLE,
         CONSTRAINT "DME_RETENTION_PERIODS_FK1" FOREIGN KEY ("BATCH_REQ_ID")
          REFERENCES "DMEDBA"."DME_BATCH_OUTBOUND_REQUEST" ("OUTBOUND_BATCH_REQ_ID") ENABLE
   )
  TABLESPACE " DMEDBA_D"  ENABLE
   PARTITION BY RANGE (CRT_DT)
(
   PARTITION " W_20120219"  VALUES LESS THAN (TO_DATE(' 2012-03-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  TABLESPACE " DMEDBA_D",
   PARTITION " W_20120226"  VALUES LESS THAN (TO_DATE(' 2012-03-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  TABLESPACE " DMEDBA_D",
   PARTITION " W_20120304"  VALUES LESS THAN (TO_DATE(' 2012-03-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  TABLESPACE " DMEDBA_D",
   PARTITION " W_20120311"  VALUES LESS THAN (TO_DATE(' 2012-03-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  TABLESPACE " DMEDBA_D",
   PARTITION " W_20120318"  VALUES LESS THAN (TO_DATE(' 2012-03-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  TABLESPACE " DMEDBA_D";
);

Step 2.
Insert non-partition table's data into the new partition table DME_BATCH_OUTBOUND_PAYLOAD_NEW

Insert into DME_BATCH_OUTBOUND_PAYLOAD_NEW  as
select * from DME_BATCH_OUTBOUND_PAYLOAD;

step3.

Rename DME_BATCH_OUTBOUND_PAYLOAD to DME_BATCH_OUTBOUND_PAYLOAD_OLD.

Rename DME_BATCH_OUTBOUND_PAYLOAD_NEW to DME_BATCH_OUTBOUND_PAYLOAD;

Make sure the tables’ data are same.

Drop table DME_BATCH_OUTBOUND_PAYLOAD_OLD;

Step 3.

Add index to the new partition table DME_BATCH_OUTBOUND_PAYLOAD

CREATE INDEX "DMEDBA"." DME_BATCH_OUTBOUND_PAYLOAD_IX1" ON "DMEDBA"." DME_BATCH_OUTBOUND_PAYLOAD" ("BATCH_REQ_ID")  TABLESPACE " DMEDBA_I";