How To Drop The Interval Partitions?
(Doc ID 1285036.1)
Last updated on AUGUST 13, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 11.1.0.6 to 12.1.0.2 [Release 11.1 to 12.1]Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A 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')
(
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Goal |
| Solution |