Unable to delete oldest table partition

Question

I'm using the 11g interval partitioning feature in one of my tables. I set it up to create 1 day partitions on a timestamp field and created a job to delete data 3 months old. When I try to delete the oldest partition I get the following error:

ORA-14758: Last partition in the range section cannot be dropped

I would have thought that "Last" refers to the newest partition and not the oldest. How should I interpret this error? Is there something wrong with my partitions or should I in fact keep the oldest partition there at all time?

Solution 1

Yes, the error message is somewhat misleading, but it refers to the last STATICALLY created partition (in your original table DDL before Oracle started creating the partitions automatically. I think the only way to avoid this is to create an artifical "MINVAL" partition that you're sure will never be used and then drop the real partitions above this.

[Edit after exchange of 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 drop partitions p0,p1, and p2 but get your error when attempting to drop p3 even though there is a system-generated partition beyond this.

The only workaround I could find was to temporarily redefine the table partitioning by:

alter table test set interval ();

and then drop partition p3. Then you can redefine the partitioning as per the original specification by:

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

Solution 2

All correct in dpbradley's answer. But it could be done more safe way if you're dropping oldest partition(s):

In fact it is enough just to reset interval like this :

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

And then drop partition oldest partition.

Otherwise there is a risk if drop partition fails then table will have no interval. So need to catch all exceptions and handle this.