How do I alter my existing table to create a range partition in Oracle

user_387774 0
Viewed 38759

I have existing table which has 10 years of data (I have taken dump).

I would like to Range partition the existing table on one date key column within the table.

Most of the examples I see are with CREATE TABLE..PARTITION BY RANGE... to add new partitions. But my table is existing table.

I assume I need some ALTER statement.

ALTER TABLE TABLE_NAME
PARTITION BY RANGE(CREATED_DATE)
 PARTITION JAN16 VALUES LESS THAN (01-02-2016),
 PARTITION FEB16 VALUES LESS THAN (01-03-2016) AND GREATER THAN(31-01-2016),//OR?
 PARTITION MAR16 VALUES BETWEEN (01-03-2016) AND (31-03-2016),  //OR?

Two questions..

  1. Do I need Alter statement to add partitioning mechanism or need to work with create statement?

  2. What is the proper syntax for keeping each partition having only ONE MONTH data.

partition create-table alter-table oracle
2 Answers

If you are using Oracle 12c Release 2 you could use single ALTER to convert non-partitioned table to partitioned one (this is one way trip):

CREATE TABLE my_tab ( a NUMBER(38,0), b NUMBER(38,0)); 

ALTER TABLE MY_TAB MODIFY PARTITION BY RANGE (a) INTERVAL (1000) (   
    PARTITION p1 VALUES LESS THAN (1000)) ONLINE;

You could convert indexes too, adding:

update indexes (index_name [local/global]);

db<>fiddle demo

user_50708790
Related