Another option you can Create New table with new partitions as CTAS. Then
rename new as old.
Karth
Get Outlook for iOS<https://aka.ms/o0ukef>
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Radoulov, Dimitre <cichomitiko@xxxxxxxxx>
Sent: Thursday, May 28, 2020 11:49:52 AM
To: Jonathan Lewis <jlewisoracle@xxxxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxx>
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
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@xxxxxxxxx<mailto:cichomitiko@xxxxxxxxx>> 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
--
//www.freelists.org/webpage/oracle-l
