Drop or Truncate an Oracle Table Partition

ALTER TABLE ... DROP PARTITION removes a partition and its rows. TRUNCATE PARTITION removes the rows while retaining the partition definition. Use the first when retiring a partition boundary; use the second when the same partition will receive replacement data.

These are DDL operations with implicit commits, not reversible DELETE statements. A dropped partition does not enter the recycle bin. Plan recovery or preserve the data before maintenance; ROLLBACK and Flashback Drop are not a recovery plan for this operation.

Inspect the target before changing it

This example assumes a heap-organized SALES table with a range partition named SALES_2026_Q1. The inspection queries do not change data. Confirm the owner, partitioning strategy, boundaries, foreign keys, indexes, and maintenance window before running the destructive statement.

SELECT table_name, partition_name, partition_position, high_value
FROM user_tab_partitions
WHERE table_name = 'SALES'
ORDER BY partition_position;

SELECT index_name, index_type, partitioned, status
FROM user_indexes
WHERE table_name = 'SALES';

Names in these dictionary views are normally uppercase for unquoted identifiers. A similarly named table in another schema is a different target. USER views describe objects owned by the current user.

Drop the selected partition

ALTER TABLE sales
    DROP PARTITION sales_2026_q1
    UPDATE INDEXES;

The example requests global index maintenance for a heap table. Index-organized tables and special index types have different restrictions. Check the supported maintenance options rather than applying this statement unchanged to every partitioned object.

Do not append PURGE or CASCADE CONSTRAINTS: those are not general options for this DROP PARTITION operation. Reference-partitioned tables have specific supported cascading operations; they do not make an arbitrary foreign-key hierarchy safe to delete.

The corresponding local index partitions^[1] are removed too. Global indexes need the maintenance plan chosen for the operation. On eligible heap tables, asynchronous global index maintenance can defer cleanup while keeping indexes usable.

A partition boundary also has meaning for future rows. For an ordinary range-partitioned table, dropping a middle partition changes the range covered by the next partition; dropping the highest partition without an accommodating upper boundary may make subsequent inserts fail. Do not generalize this behavior to hash, list, or interval partitioning.

Oracle does not permit dropping a table's only partition. Interval tables have additional restrictions, including their highest range partition. Confirm the rules for the actual partitioning method.

ALTER TABLE sales
    TRUNCATE PARTITION sales_2026_q1
    REUSE STORAGE
    UPDATE INDEXES;

This is an alternative to DROP, not the next step after dropping the same partition. The partition remains available for new rows, and its matching local index partition is emptied. REUSE STORAGE retains allocated space for reuse; storage-release options should be chosen separately from the decision to retain the partition definition.

Do not use ALTER INDEX ... TRUNCATE PARTITION as an independent substitute. The table partition operation maintains its corresponding local index partition.

An empty partition definition and an allocated segment are different things. Removing rows does not by itself tell you how much space remains allocated. Inspect storage after maintenance and use the documented storage clause appropriate to the table. Do not drop an empty partition simply to reclaim space if its boundary is still needed.

Repeat the partition inventory to confirm whether the target is absent or retained, according to the chosen operation. Then inspect indexes at the correct level:

SELECT index_name, status
FROM user_indexes
WHERE table_name = 'SALES'
  AND partitioned = 'NO'
  AND status = 'UNUSABLE';

SELECT p.index_name, p.partition_name, p.status
FROM user_ind_partitions p
JOIN user_indexes i ON i.index_name = p.index_name
WHERE i.table_name = 'SALES'
  AND p.status = 'UNUSABLE';

SELECT p.index_name, p.subpartition_name, p.status
FROM user_ind_subpartitions p
JOIN user_indexes i ON i.index_name = p.index_name
WHERE i.table_name = 'SALES'
  AND p.status = 'UNUSABLE';

A partitioned index may report N/A at the top level. That is not a command to rebuild the entire index. Diagnose any unusable partition or subpartition and choose the supported repair for that object. These checks supplement, rather than replace, validation of dependent application queries and constraints.

[1] Local index partitions correspond to table partitions and index the rows in their matching partitions.