Failed to delete old table partition.

Question

I use the 11g spacing function in one of my tables. I set it to create 1 day sections in the timestamp field and created a task to delete data for 3 months. When I try to delete the oldest partition, I get the following error:

ORA-14758: last section in range section cannot be deleted

I would think that "Last" refers to the newest section, and not to the oldest. How to interpret this error? Is something wrong with my partitions, or should I store the oldest partition there?

Answer 1

Yes, the error message is a little misleading, but it refers to the last STATICALLY created partition (in your original DDL table before Oracle starts automatically creating partitions. I think the only way to avoid this is to create an artificial "MINVAL msgstr" partition which you are sure will never be used, and then discard the real sections above this.

[Edit after sharing comments]

I assume this test case reproduces your problem:

CREATE TABLE test ( t_time DATE ) PARTITION BY RANGE (t_time) INTERVAL(NUMTODSINTERVAL(1, 'DAY')) ( PARTITION p0 VALUES LESS THAN (TO_DATE('09-1-2009', 'MM-DD-YYYY')), PARTITION p1 VALUES LESS THAN (TO_DATE('09-2-2009', 'MM-DD-YYYY')), PARTITION p2 VALUES LESS THAN (TO_DATE('09-3-2009', 'MM-DD-YYYY')), PARTITION p3 VALUES LESS THAN (TO_DATE('09-4-2009', 'MM-DD-YYYY')) ); insert into test values(TO_DATE('08-29-2009', 'MM-DD-YYYY')); insert into test values(TO_DATE('09-1-2009', 'MM-DD-YYYY')); insert into test values(TO_DATE('09-3-2009', 'MM-DD-YYYY')); insert into test values(TO_DATE('09-10-2009', 'MM-DD-YYYY')); 

When I do this, I can delete the partitions p0, p1 and p2, but get your error when trying to reset p3, even if there is a system partition for that.

The only workaround I could find was to temporarily override the partition of the table into:

 alter table test set interval (); 

and then drop the p3 partition. Then you can override the section according to the original specification:

 alter table test set INTERVAL(NUMTODSINTERVAL(1, 'DAY')); 

Answer 2

That's right in dpbradley's answer. But this can be done in a safer way if you drop the oldest sections (sections):

Actually, just resetting the interval is enough, like this:

 alter table test set interval (); alter table test set INTERVAL(NUMTODSINTERVAL(1, 'DAY')); 

And then undo the oldest section of the section.

Otherwise, there is a risk if the fail-safe partition is not executed, then the table will not have an interval. Therefore, you need to catch all the exceptions and handle this.