How To Drop The Interval Partitions?

(Doc ID 1285036.1)

Last updated on JUNE 16, 2017

Applies to:

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

Goal


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

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?

Solution

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