CODEWITHSUNDEEP
oracle-databaseoracle11gpartitioning
ZetaPR
ZetaPR

Reputation: 993

How to change RANGE value in PARTITIONS BY Range Oracle

I have an existing table within the next script:

create table sales6
        (
           sales_id    number,
           sales_dt    date
        )
        partition by range (sales_dt)

        (
           partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd'))
        );

What I need is to change the partition range to add an interval like this :

interval (numtoyminterval(1,'MONTH'))

I know that the right way to do it is when you create the table, but the table already exists and there are lot of records stored.

Is there any way to achieve this in Oracle 11g? I tried to ALTER the table but is not working due to 00940. 00000 - "invalid ALTER command"

Hope you can help me.

PS: I've been reading the whole documentation of Oracle in this two links without luck: https://docs.oracle.com/cd/E17952_01/refman-5.5-en/alter-table-partition-operations.html https://docs.oracle.com/cd/E17952_01/refman-5.1-en/partitioning-management-range-list.html

Upvotes: 1

Views: 2276

Answers (1)

ivanzg
ivanzg

Reputation: 527

You can change a range partitioned table to a interval partitioned table with this command :

ALTER TABLE X SET INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

You can change back to range partitioned table with this command :

ALTER TABLE X SET INTERVAL();

Interval partitioning is always a more preferable option to range partitioning if your partitions are always evenly created (in identical periods).

The commands are not resource intensive because you don't manipulate segments and data, you just tell Oracle to begin or stop creating new partitions if new data that is inserted in the table doesn't fit by partition key in any existing partition.

Upvotes: 1

Related Questions