Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Praveen.

Asked: August 21, 2017 - 3:29 am UTC

Last updated: August 22, 2017 - 1:07 pm UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

Hi,

We have a existing table. And this table is partitioned by range interval. Now i would like to change the column its referring to. Can i do that?

Eg:

CREATE TABLE partition_test
  ( PROD_ID       NUMBER NOT NULL,
    DATE_SK       NUMBER,
    URL           VARCHAR2(100 BYTE)
  )
  PARTITION BY RANGE (PROD_ID) INTERVAL(100) 
  (PARTITION PART_MIN VALUES LESS THAN (20051001));


Now i would like to change the column from PROD_ID to DATE_SK under the partition using the SQL script.

I was able to do this through sql developer, update Partitions through the UI. But i need the script for the same.

Regards,
Praveen Lobo

and Chris said...

No, there isn't a straightforward way to change the partition column of a table.

You can use dbms_redefinition to migrate the data to a new table with the partitioning scheme you want.

For a worked example of how to use dbms_redefinition, see:

https://oracle-base.com/articles/misc/partitioning-an-existing-table

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Add new column as the range interval?

Praveen Lobo, August 22, 2017 - 1:03 am UTC

Thanks for that Chris.

Is there a way to add DATE_SK as the new partition column to the existing table? Instead of changing the column, i want to add DATE_SK as the partition column after the table is created.

Chris Saxon
August 22, 2017 - 1:07 pm UTC

You have to recreate the table to change the partitioning scheme or columns. Or add partitioning to a non-partitioned table up to 12.1

From 12.2 you can partition a table in one statement like so:

create table t ( x int, y int, z int ); 

alter table t modify partition by range (x) interval (100) (
  partition p1 values less than (100)
) online;

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database