Overview

You can use the ALTER TABLE... DROP SUBPARTITION command to delete a subpartition definition and the data stored in that subpartition. Syntax:
ALTER TABLE table_name DROP SUBPARTITION subpartition_name;

Description

The ALTER TABLE... DROP SUBPARTITION command deletes a subpartition and the data stored in the subpartition. To use the DROP SUBPARTITION 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.
subpartition_name The name of the subpartition to be deleted.

Example - delete a subpartition

The following example deletes a subpartition 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 RANGE(date)
  SUBPARTITION BY LIST (country)
  (
    PARTITION first_half_2012 VALUES LESS THAN('01-JUL-2012')
    (
      SUBPARTITION europe VALUES ('ITALY', 'FRANCE'),
      SUBPARTITION americas VALUES ('CANADA', 'US'),
      SUBPARTITION asia VALUES ('PAKISTAN', 'INDIA')
    ),
    PARTITION second_half_2012 VALUES LESS THAN('01-JAN-2013') 
  );
Querying the ALL_TAB_SUBPARTITIONS view displays the subpartition names:
acctg=# SELECT subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS;
 subpartition_name |     high_value       
-------------------+---------------------
 europe            | 'ITALY', 'FRANCE'   
 americas          | 'CANADA', 'US'      
 asia              | 'PAKISTAN', 'INDIA' 
(3 rows)
To delete the americas subpartition from the sales table, run the following command:
ALTER TABLE sales DROP SUBPARTITION americas;
Querying the ALL_TAB_SUBPARTITIONS view shows that the subpartition has been successfully deleted:
acctg=# SELECT subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS;
 subpartition_name |     high_value      
-------------------+---------------------
 europe            | 'ITALY', 'FRANCE'
 asia              | 'PAKISTAN', 'INDIA'
(2 rows)