How to drop the Interval Partitions?
How to drop the September partition of this table?
1. You can use the following command to drop the interval partition:
alter table sales drop partition <partition_name>; but you need manually add the partition name which most of the times is system generated.
2. You can use:
alter table sales drop partition for (to_date('01-sep-2009','DD-MON-YYYy'));
to drop the September partition.
In your example you will get
SQL> alter table sales drop partition for (to_date('01-JAN-2009','DD-MON-YYYy'));
alter table sales drop partition for (to_date('01-JAN-2009','DD-MON-YYYy'))
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped
when trying to drop the first partition.
And the error is coming because we tried to drop the last partition in the range section.
In conclusion you can also drop range partitions in an interval-partitioned table. The rules for dropping a range partition in an interval-partitioned table follow the rules for dropping a range partition in a range-partitioned table. If you drop a range partition in the middle of a set of range partitions, then the lower boundary for the next range partition shifts to the lower boundary of the range partition you just dropped. You cannot drop the highest range partition in the range-partitioned section of an interval-partitioned table.