How To Drop The Interval Partitions? [ID 1285036.1]

Goal

How to drop the Interval Partitions?

Example:

create table sales
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
interval (numtoyminterval(1,'MONTH'))
( partition p0901 values less than (to_date('2009-02-01','yyyy-mm-dd')) );

insert into sales values (1,'01-jun-09');
insert into sales values (1,'01-sep-09');

commit;

SQL> select partition_name, interval, high_value
from dba_tab_partitions where table_name = 'SALES'
order by partition_position; 2 3

PARTITION_NAME       INTERVAL  HIGH_VALUE
-------------------- --------- -------------------------------------------------------------------------------------
P0901                NO        TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P11046           YES       TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P11047           YES       TO_DATE(' 2009-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

How to drop the September partition of this table?

Fix

There are 2 options to do this:

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.

OR

2. You can use:

alter table sales drop partition for (to_date('01-sep-2009','DD-MON-YYYy'));
to drop the September partition.

There is some point we need to take care at when droping interval partitioning and this is related to the situation when we want to drop the last partition in the range section of an interval partitioned table.

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.

Please also note that interval column of the view dba_tab_partitions was added starting with 11.2.0.1