Last updated on JUNE 16, 2017
Applies to:
Oracle Database - Enterprise Edition - Version 11.1.0.6 and laterInformation 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')
(
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