How To Drop The Interval Partitions?

(Doc ID 1285036.1)

Last updated on JUNE 16, 2017

Applies to:

Oracle Database - Enterprise Edition - Version and later
Information in this document applies to any platform.


This document provides a step-by-step approach to drop interval partition.


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


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

-------------------- --------- -------------------------------------------------------------------------------------
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?


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms