Unable to delete oldest table partition

oracle11g, partitioning

Solution

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'));

Problem

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?

Original source