Deep Dive — TRUNCATE

DDL, not DML: it resets the table's high-water mark instead of deleting rows. Seconds instead of hours — and it commits your transaction on the way.

Used in
Chapter 9 — Huge Tables
Appendix entry
TRUNCATE
Related
EXCHANGE PARTITION APPEND HINT COMMIT EXECUTE IMMEDIATE DBMS_ASSERT

DELETE visits every row: undo for each, redo for each, index entries removed one by one, triggers fired, and the space stays allocated. TRUNCATE rewrites the segment header: the high-water mark goes back to zero, extents are released (or kept with REUSE STORAGE), indexes are reset the same way. No per-row work at all.

Use it for: emptying staging tables, dropping a partition's contents, resetting work tables between runs. The price: it is DDL — it commits the current transaction, cannot be rolled back, fires no triggers, and needs the table to be free of enabled foreign-key references.

SQL 
TRUNCATE TABLE <table> [PRESERVE | PURGE MATERIALIZED VIEW LOG] [DROP STORAGE | DROP ALL STORAGE | REUSE STORAGE] [CASCADE];
ALTER TABLE <table> TRUNCATE PARTITION <p> [, <p2> ...] [DROP | REUSE STORAGE] [UPDATE [GLOBAL] INDEXES] [CASCADE];
ALTER TABLE <table> TRUNCATE SUBPARTITION <sp> ...;
ALTER TABLE <table> DROP PARTITION <p> [UPDATE [GLOBAL] INDEXES];
TRUNCATE CLUSTER <cluster>;
  • DROP STORAGE (default) — deallocate extents beyond MINEXTENTS. REUSE STORAGE — keep them (faster, and the space is about to be reused anyway). DROP ALL STORAGE (11.2.0.2+) — deallocate everything including the first extent.
  • CASCADE (12c+) — also truncate child tables whose FK to this table is ON DELETE CASCADE.
  • UPDATE INDEXES on a partition truncate — keep global indexes usable (does the maintenance now, or asynchronously in 12c+); without it, global indexes become UNUSABLE.

SQL 
TRUNCATE TABLE orders_stage REUSE STORAGE;             -- ms; HWM reset, extents kept
INSERT /*+ APPEND */ INTO orders_stage SELECT ... FROM external_orders_ext;
COMMIT;

This pair is the standard nightly-load shape. DELETE + APPEND instead would grow the table every night (APPEND HINT §4).

SQL 
-- DELETE of 40M rows: hours, 40M undo records, 40M index deletes
DELETE FROM orders WHERE order_date >= DATE '2024-12-01' AND order_date < DATE '2025-01-01';

-- the same, as DDL: seconds
ALTER TABLE orders TRUNCATE PARTITION p_2024_12 UPDATE INDEXES;

Requires the table to be partitioned on order_date so that "December 2024" is a partition — this is the main reason large tables are range-partitioned by date.

SQL 
CREATE OR REPLACE PROCEDURE purge_month (p_partition VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE orders TRUNCATE PARTITION ' ||
                    DBMS_ASSERT.SIMPLE_SQL_NAME(p_partition) || ' UPDATE INDEXES';
END;
/

Call it before the transactional work of a job, or after its final commit — never between an UPDATE and its intended COMMIT:

SQL 
BEGIN
  UPDATE orders SET status = 'X' WHERE order_id = 1;
  purge_month('P_2024_12');            -- commits the UPDATE silently
  RAISE_APPLICATION_ERROR(-20001, 'validation failed');   -- too late: the UPDATE is permanent
END;

SQL 
TRUNCATE TABLE customers;
-- ORA-02266: unique/primary keys in table referenced by enabled foreign keys

-- options:
ALTER TABLE orders DISABLE CONSTRAINT orders_cust_fk;
TRUNCATE TABLE customers;
ALTER TABLE orders ENABLE NOVALIDATE CONSTRAINT orders_cust_fk;    -- or ENABLE VALIDATE if the data allows

-- 12c+, if orders_cust_fk is ON DELETE CASCADE:
TRUNCATE TABLE customers CASCADE;                                  -- truncates orders too

ORA-02266 is raised even if the child table is empty — the check is on the constraint's existence, not on rows.

SQL 
ALTER TABLE orders TRUNCATE PARTITION p_2024_12;                  -- global indexes → UNUSABLE
SELECT index_name, status FROM user_indexes WHERE table_name = 'ORDERS';
ALTER INDEX orders_cust_gix REBUILD PARALLEL 8;                   -- or:

ALTER TABLE orders TRUNCATE PARTITION p_2024_12 UPDATE INDEXES;   -- global indexes stay VALID

12c+ does the global-index maintenance asynchronously: the truncate returns immediately, the index entries are marked orphaned and cleaned by the SYS.PMO_DEFERRED_GIDX_MAINT_JOB job (or DBMS_PART.CLEANUP_GIDX). Local indexes are truncated along with the partition — no maintenance needed, which is the argument for local indexes on partitioned tables.

SQL 
TRUNCATE TABLE orders_stage;
-- ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Another session holds a row lock (an uncommitted INSERT into the stage table). DDL needs an exclusive table lock; it waits DDL_LOCK_TIMEOUT seconds (default 0 = fail immediately). ALTER SESSION SET ddl_lock_timeout = 60 gives it a minute.

  • DELETE of tens of millions of rows is the operation that fills undo, generates the most redo per useful byte, and leaves the table the same size with free space scattered inside — every later full scan still reads the empty blocks up to the HWM. TRUNCATE (or partition truncate/drop) is not an optimization of DELETE; it is the correct operation, and the table design (partitioning) should make it available.
  • DROP STORAGE on a table with hundreds of thousands of extents can itself take a while (extent deallocation in the dictionary / bitmap). REUSE STORAGE when the space will be refilled.
  • Materialized view logs on the table: TRUNCATE by default purges the MV log (PURGE MATERIALIZED VIEW LOG), which forces the next refresh to be complete rather than fast.
  • Statistics are not touched; after a truncate-and-reload the old stats may describe a very different table. Gather after the load (or rely on 12c online stats gathering for direct-path loads into empty tables).
  • Flashback Query cannot see truncated rows (no undo was generated). Recovery is RMAN / Flashback Database — which is the DBA's reminder: truncating the wrong table is a restore, not an undo.

  1. Implicit COMMIT — in the middle of a transaction, silently.
  2. No ROLLBACK, no Flashback Query.
  3. ORA-02266 — enabled FK referencing the table, even with an empty child.
  4. ORA-00054 — DDL lock conflict with any uncommitted DML on the table; set DDL_LOCK_TIMEOUT.
  5. Global indexes UNUSABLE after partition truncate without UPDATE INDEXES — queries fail with ORA-01502 or fall back to full scans depending on SKIP_UNUSABLE_INDEXES.
  6. Triggers do not fire — audit triggers miss the deletion entirely.
  7. Truncating a table other schemas depend on for MV fast refresh — the MV log is purged.
  8. TRUNCATE on a table in someone else's schema needs DROP ANY TABLE, not DELETE — a much bigger privilege. Wrap it in a definer's-rights procedure in the owning schema and grant EXECUTE.