CODEWITHSUNDEEP
oracle-databasecreate-tablealter-tablepartition
Some Java Guy
Some Java Guy

Reputation: 5118

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

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.

Upvotes: 12

Views: 65809

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

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

Upvotes: 7

hinotf
hinotf

Reputation: 1138

Beacuse your table non-partitioned you have two options:

  1. Export data, drop table, create new patitioned table, import data.
  2. Use split then exchange partition method. https://oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition

Also, if you want new partition per month read about SET INTERVAL. For example:

CREATE TABLE tst
   (col_date DATE)
 PARTITION BY RANGE (col_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION col_date_min VALUES LESS THAN (TO_DATE('2010-01-01', 'YYYY-MM-DD')));

Upvotes: 3

Related Questions