Use one of the following statements to drop a table partition or subpartition:
ALTER TABLE DROP SUBPARTITION to drop a subpartition of a composite *-[range | list] partitioned table
To preserve the data in the partition, use the MERGE PARTITION statement instead of the DROP PARTITION statement. For information about merging a partition, refer to "About Merging Partitions and Subpartitions".
To remove data in the partition without dropping the partition, use the TRUNCATE PARTITION statement. For information about truncating a partition, refer to "About Truncating Partitions and Subpartitions".
If local indexes are defined for the table, then this statement also drops the matching partition or subpartitions from the local index. All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE unless either of the following is true:
You specify UPDATE INDEXES (Cannot be specified for index-organized tables. Use UPDATE GLOBAL INDEXES instead.)
The partition being dropped or its subpartitions are empty
If a table contains only one partition, you cannot drop the partition. Instead, you must drop the table.
You cannot drop the highest range partition in the range-partitioned section of an interval-partitioned or interval-* composite partitioned table.
With asynchronous global index maintenance, a drop partition update indexes operation is on metadata only and all global indexes remain valid.
For information about asynchronous index maintenance for dropping partitions, refer to "Asynchronous Global Index Maintenance for Dropping and Truncating Partitions".
The following sections contain some scenarios for dropping table partitions.