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
Dimitre

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.
>
>
> Regards.
> Jonathan Lewis
>
>
>
>
> On Thu, May 28, 2020 at 4:22 PM Radoulov, Dimitre
> <cichomitiko_at_gmail.com <mailto: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