Re: Interval partitioning - modify transition point

From: Karthikeyan Panchanathan <>
Date: Thu, 28 May 2020 16:28:37 +0000
Message-ID: <>

Another option you can Create New table with new partitions as CTAS. Then rename new as old.


Get Outlook for iOS<>

From: <> on behalf of Radoulov, Dimitre <> Sent: Thursday, May 28, 2020 11:49:52 AM To: Jonathan Lewis <> Cc: <> Subject: Re: Interval partitioning - modify transition point

Thank you very much Jonathan!
We currently have a relatively small number of materialized partitions (the last 90 days). I'll check if I can merge the anchor point with the oldest materialized partition.

Best regards

On 28/05/2020 17.44, Jonathan Lewis wrote:

I'm going to be a bit lazy and leave you to check if this works in your version for composite partitioning.

Recent versions of Oracle (possibly 12.2 onwards) will simply roll the anchor point forward if you drop the current anchor point; in earlier versions the "normal" way of handling the problem was to convert the interval partitioned table into a range partitioned table, then back into an interval partition - but that made the current top partition the the new anchor.

What I found for simple range partitioning was that if you merge the anchor point with the next partition the result is a new anchor point. To make this efficient I then ensured that I had truncated the current anchor and the next partition up so that the merge was of two empty partitions and did no work. Unfortunately I don't know if you can merge adjacent partitions when you also have sub-partitions.

Jonathan Lewis

On Thu, May 28, 2020 at 4:22 PM Radoulov, Dimitre <<>> wrote: Hello all,

can the transition point (the first partition) of an interval partitioned table be modified in order to avoid the ORA-14299 error?

We have a daily range partitioned table with list subpartitions. We're trying to modify the subpartition template to add new values to the list and we're hitting the error below:

ORA-14299: total number of partitions/subpartitions exceeds the maximum limit

Automatic partition retention is in place, but of course the interval partitioning considers the entire date range from the transition point at table creation until now.

Oracle EE

Best regards


-- Received on Thu May 28 2020 - 18:28:37 CEST

Original text of this message