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

This error occurs, when you try to drop the last statically created partition (from the initial table creation DDL) from a Interval Partitioned table



The easiest workaround to drop the last statically created partition is to temporally disable the interval partition (which switches to range partition), drop the last statically created partition and switch it back to interval partition



alter table invoices set interval ();
-> After dropping the last static Partition
alter table invoices set interval (numtoyminterval(1,'month'))

Example :

create table invoices
(
invoice_no number not null,
invoice_date date not null,
comments varchar2(500)
)
partition by range (invoice_date)
interval (numtoyminterval(1,'month'))
(partition p_first values less than (to_date('01-jan-2012','DD-MON-YYYY')) ,
 partition p_second values less than (to_date('01-feb-2012','DD-MON-YYYY')) );


-- inserts a record into the last statically created partition p_second
Insert into invoices values (135,'03-JAN-2012','test invoice');
commit;
when you try to drop the partition p_second, using..

alter table invoices set interval ();
ERROR at line 1:

ORA-14758: Last partition in the range section cannot be dropped
 To overcome this error,

-- reset it to range partition table
alter table invoices set interval ();

then run the following command,which should drop the partition
alter table invoices drop partition p_second;

finally, switch the partition back to interval
alter table invoices set interval (numtoyminterval(1,'month'))