How to add interval partitions to an existing table in Oracle

Question

I have a scenarios like ,I need to create interval partitions(monthly) to an existing table in Oracle which has no partitions .please suggest me how to proceed.

I tried with below

alter table RSST_TP_ORDERINVOICED_NETREV_F 
  set interval(NUMTOYMINTERVAL(1,'MONTH')); 
TABLESPACE "RSST_DATA" 
  PARTITION BY RANGE ( "DATE_SK" ) 
  INTERVAL ( NUMTOYMINTERVAL(1,'MONTH') ) ( 
PARTITION "P_FIRST" VALUES LESS THAN (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
TABLESPACE "RSST_DATA" )

Have you got code that you have tried already? Please post if you have — SEB BINFIELD, Aug 26 '14 at 13:44
Please don't post additional info in comments - edit your question instead (using the "edit" button). — Frank Schmitt, Aug 26 '14 at 13:58
Similar recent question with detailed answers: https://stackoverflow.com/questions/35932771/how-do-i-alter-my-existing-table-to-create-a-range-partition-in-oracle — Vadzim, Apr 09 '18 at 14:23

score 2 · Answer 1 · answered Jun 27 '18 at 11:05

As discussed above, we cannot partition an existing non-partitioned table directly using an alter command (Oracle 11g and below).

1) Create new Table "RSST_TP_ORDERINVOICED_NETREV_F_TEMP" with partitions (with similar structure).

2) Insert whole data from RSST_TP_ORDERINVOICED_NETREV_F to RSST_TP_ORDERINVOICED_NETREV_F_TEMP

Use: INSERT /*+ append */ INTO RSST_TP_ORDERINVOICED_NETREV_F_TEMP AS SELECT * FROM RSST_TP_ORDERINVOICED_NETREV_F; or Bulk Collect

3) Take back up scripts for creating indexes,constraints,grants,triggers.

4) Drop table RSST_TP_ORDERINVOICED_NETREV_F.

5) Rename table RSST_TP_ORDERINVOICED_NETREV_F_TEMP to RSST_TP_ORDERINVOICED_NETREV_F.

6) Re-create all corresponding indexes,constraints(Primary key, Foreign),grants, triggers.

score 1 · Answer 2 · answered Aug 26 '14 at 14:34

You cannot partition an existing non-partitioned table.

In general, you'll need to create a new partitioned table, move the data from the existing table to the new table (probably using a direct-path insert with parallel DML), drop the old table, and rename the new table to use the old name. You can do that manually. Or you could use the dbms_redefinition package to manage these steps-- that will likely be less efficient but it would allow you to do this without an outage window.

How to add interval partitions to an existing table in Oracle

2 Answers2

Linked