Overview

You can use the ALTER TABLE... DROP PARTITION command to delete a partition definition and the data stored in that partition. Syntax:
ALTER TABLE table_name DROP PARTITION partition_name;

Description

The ALTER TABLE... DROP PARTITION command deletes a partition and the data stored in the partition. When you delete a partition, all subpartitions of the partition are also deleted.

To use the DROP PARTITION clause, you must be the owner of the partitioning root, a member of a group that owns the table, or have superuser or administrative privileges.

Parameters

Parameter Description
table_name The name (optionally schema-qualified) of the partitioned table.
partition_name The name of the partition to be deleted.

Example - delete a partition

The following example deletes a partition of the sales table. Run the following command to create the sales table:
CREATE TABLE sales
(
  dept_no     number,   
  part_no     varchar2,
  country     varchar2(20),
  date    date,
  amount  number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);
Querying the ALL_TAB_PARTITIONS view displays the partition names:
acctg=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |     high_value      
----------------+---------------------
 europe         | 'FRANCE', 'ITALY'
 asia           | 'INDIA', 'PAKISTAN'
 americas       | 'US', 'CANADA'
(3 rows)
To delete the americas partition from the sales table, invoke the following command:
ALTER TABLE sales DROP PARTITION americas;
Querying the ALL_TAB_PARTITIONS view shows that the partition has been successfully deleted:
acctg=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |     high_value      
----------------+---------------------
 asia           | 'INDIA', 'PAKISTAN'
 europe         | 'FRANCE', 'ITALY'
(2 rows)