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

Question

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..

Do I need Alter statement to add partitioning mechanism or need to work with create statement?
What is the proper syntax for keeping each partition having only ONE MONTH data.

You can not `alter` a table to be partitioned. You need to drop and re-create it. You can only use `alter` to _add_ partitions to a table that is _already_ partitioned — , Mar 11 '16 at 06:59
@a_horse_with_no_name `You can not alter a table to be partitioned` actually you can :) — Lukasz Szozda, Jan 25 '18 at 20:23
@lad2025: but only with `dbms_redefinition` which is not exactly an `alter` statement — , Jan 25 '18 at 20:44
@a_horse_with_no_name I was referring to [my answer](https://stackoverflow.com/a/48451221/5070879), not DBMS_REDEFINITION. — Lukasz Szozda, Jan 25 '18 at 20:52

Lukasz Szozda · Answer 1 · 2019-08-28T21:41:13.723

6

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

edited Aug 28 '19 at 21:41

answered Jan 25 '18 at 20:18

Lukasz Szozda

162,964
23
234
275

When i runt he above sql, It throws an error as "Invalid Partition name" – Teju MB Aug 27 '19 at 06:17
@TejuMB You have lower version than 12R2 demo: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=dddc997829b0a41ad3722920aee8b639 – Lukasz Szozda Aug 28 '19 at 21:41

score 3 · Accepted Answer · answered Mar 11 '16 at 07:25

Beacuse your table non-partitioned you have two options:

Export data, drop table, create new patitioned table, import data.
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')));

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

2 Answers2

Linked