Re: Interval partitioning - modify transition point

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.

Regards.
Jonathan Lewis

On Thu, May 28, 2020 at 4:22 PM Radoulov, Dimitre <cichomitiko_at_gmail.com> 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 11.2.0.3 EE
>
>
> Best regards
> Dimitre
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l