Truncate/drop a partition while keeping global indexes usable

Command

-- Empty a partition / remove a partition (global indexes stay maintained):
ALTER TABLE customer_app.sale_part TRUNCATE PARTITION p2025 UPDATE INDEXES;
ALTER TABLE customer_app.sale_part DROP PARTITION p2025 UPDATE INDEXES;

-- 12c+ asynchronous global index maintenance: index is VALID but carries orphaned entries
SELECT index_name, status, orphaned_entries
FROM   dba_indexes
WHERE  owner = 'CUSTOMER_APP' AND table_name = 'SALE_PART';

-- Clean orphaned entries now (normally SYS.PMO_DEFERRED_GIDX_MAINT_JOB does it at night):
EXEC DBMS_PART.CLEANUP_GIDX(schema_name_in => 'CUSTOMER_APP', table_name_in => 'SALE_PART');
-- or a single index: ALTER INDEX customer_app.sale_part_id_gidx COALESCE CLEANUP;

-- Target the partition by value instead of name:
-- ALTER TABLE customer_app.sale_part DROP PARTITION FOR (DATE '2025-06-15') UPDATE INDEXES;

What it does

Removes old data instantly at partition level instead of row-by-row DELETE. In 12c+, UPDATE INDEXES only marks global indexes in the dictionary (asynchronous maintenance) instead of updating every entry; the index stays usable immediately and orphaned entries are cleaned later.

When to use it

When purging old periods under a data retention policy, in ILM/purge jobs, and to avoid the undo/redo load of DELETE on large tables.

Caution

Requires the Partitioning option (extra-cost EE). DROP/TRUNCATE is irreversible (flashback table does not bring a partition back); back up or archive via exchange first. If UPDATE INDEXES is forgotten, global indexes become UNUSABLE and queries may hit ORA-01502. The operation fails if child tables reference it (FK). On interval tables the last range (transition) partition cannot be dropped.