Deep Dive — EXCHANGE PARTITION

Forty million rows move from a partition to a table — or the other way — by swapping two segment pointers in the dictionary. Nothing is read, nothing is written. This is why the big table is partitioned.

Used in: Chapter 9 — Huge Tables
Appendix entry: EXCHANGE PARTITION
Related: TRUNCATE APPEND HINT PARALLEL DML INSERT SELECT

1. What It Is and When

ALTER TABLE ... EXCHANGE PARTITION p WITH TABLE t swaps the segment of partition p with the segment of standalone table t. After it, t contains what p had, and p contains what t had (usually nothing). Local index partitions are swapped with t's matching indexes when INCLUDING INDEXES is given. Data is not copied; the operation is a dictionary update plus, optionally, global-index maintenance.

Two directions, two use cases:

Out: archive or purge a partition without DELETE — exchange it with an empty table; the table now holds the old data (drop it, compress it, move it to cheap storage), the partition is empty.
In: load a partition without locking the live table — build the data in a staging table (direct path, parallel, indexes built, stats gathered, validated), then exchange it into the partition in a millisecond.

2. Syntax

SQL

ALTER TABLE <partitioned> EXCHANGE PARTITION <p> WITH TABLE <plain>
  [INCLUDING | EXCLUDING INDEXES]
  [WITH | WITHOUT VALIDATION]
  [UPDATE [GLOBAL] INDEXES]
  [CASCADE];                                          -- 12c+: reference-partitioned children too
ALTER TABLE <partitioned> EXCHANGE SUBPARTITION <sp> WITH TABLE <plain> ...;

CREATE TABLE <plain> FOR EXCHANGE WITH TABLE <partitioned>;      -- 12.2+: guaranteed-compatible structure

Requirements for <plain>:

same number of columns, in the same order, same data types and lengths (names may differ), same NOT NULL constraints; unused/hidden columns count;
INCLUDING INDEXES — an index on <plain> for each local index of the partitioned table, with matching columns and uniqueness;
WITH VALIDATION (default) — Oracle scans <plain> to verify every row belongs in partition p (ORA-14099 otherwise); WITHOUT VALIDATION skips the scan — and lets wrong rows in;
constraints: a primary key / unique constraint on <plain> must match; check constraints must be compatible;
<plain> must be non-partitioned (or, 12.2+, a partitioned table for composite exchange).

3. Examples on the Course Schema

3a. Archive a month — out

SQL

-- 1. an empty, structurally identical table (12.2+)
CREATE TABLE orders_2024_12 FOR EXCHANGE WITH TABLE orders;
-- pre-12.2: CREATE TABLE orders_2024_12 AS SELECT * FROM orders WHERE 1 = 0;  then add matching indexes/constraints

-- 2. matching local index(es)
CREATE INDEX orders_2024_12_cust_ix ON orders_2024_12 (customer_id);       -- mirrors the local index orders_cust_lix

-- 3. swap: ~1 ms for 40M rows
ALTER TABLE orders EXCHANGE PARTITION p_2024_12 WITH TABLE orders_2024_12
  INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES;

-- 4. the partition is empty; the archive table is a normal table now
ALTER TABLE orders_2024_12 MOVE COMPRESS TABLESPACE archive_ts;             -- optional: compress, relocate
ALTER TABLE orders DROP PARTITION p_2024_12;                                -- optional: if the partition is not needed

WITHOUT VALIDATION is safe here: the empty table trivially "fits".

3b. Load a month — in

SQL

-- 1. build the month in a staging table, at leisure, with no locks on orders
CREATE TABLE orders_load FOR EXCHANGE WITH TABLE orders;
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(l, 8) */ INTO orders_load l
SELECT /*+ PARALLEL(s, 8) */ * FROM orders_stage s
WHERE  order_date >= DATE '2026-09-01' AND order_date < DATE '2026-10-01';
COMMIT;
CREATE INDEX orders_load_cust_ix ON orders_load (customer_id) PARALLEL 8;
ALTER INDEX orders_load_cust_ix NOPARALLEL;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS_LOAD');

-- 2. swap it in: the live table gains 40M rows in a millisecond; readers never blocked
ALTER TABLE orders EXCHANGE PARTITION p_2026_09 WITH TABLE orders_load
  INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES;

-- 3. orders_load now holds whatever p_2026_09 had before (nothing, if it was new)
DROP TABLE orders_load;

WITH VALIDATION here — it costs a scan of the staging table, and it guarantees no row outside September lands in the September partition.

3c. Bring archived data back — the same statement

SQL

ALTER TABLE orders EXCHANGE PARTITION p_2024_12 WITH TABLE orders_2024_12
  INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES;

Exchange is symmetric. If the archive table was compressed or moved, it comes back as is (compression is a segment property, so the partition is now compressed — fine).

3d. What mismatch errors look like — and FOR EXCHANGE

SQL

ALTER TABLE orders EXCHANGE PARTITION p_2024_12 WITH TABLE orders_2024_12;
-- ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
-- ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
-- ORA-14099: all rows in table do not qualify for specified partition
-- ORA-14096: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of columns

ORA-14097 with visually identical tables usually means: an UNUSED column on one side, a virtual column, a different VARCHAR2 semantics (BYTE vs CHAR), or a NOT NULL on one side only. CREATE TABLE ... FOR EXCHANGE WITH TABLE (12.2+) copies all of that, including hidden columns — use it and the class of error disappears.

3e. The monthly cycle as a procedure

SQL

CREATE OR REPLACE PROCEDURE roll_month (p_new_month DATE, p_archive_month DATE) IS
  l_new  VARCHAR2(30) := 'P_' || TO_CHAR(p_new_month, 'YYYY_MM');
  l_old  VARCHAR2(30) := 'P_' || TO_CHAR(p_archive_month, 'YYYY_MM');
  l_arch VARCHAR2(30) := 'ORDERS_' || TO_CHAR(p_archive_month, 'YYYY_MM');
BEGIN
  -- 1. new partition for next month (interval partitioning does this automatically)
  EXECUTE IMMEDIATE 'ALTER TABLE orders ADD PARTITION ' || DBMS_ASSERT.SIMPLE_SQL_NAME(l_new) ||
                    ' VALUES LESS THAN (DATE ''' || TO_CHAR(ADD_MONTHS(p_new_month, 1), 'YYYY-MM-DD') || ''')';
  -- 2. archive the old one
  EXECUTE IMMEDIATE 'CREATE TABLE ' || DBMS_ASSERT.SIMPLE_SQL_NAME(l_arch) || ' FOR EXCHANGE WITH TABLE orders';
  EXECUTE IMMEDIATE 'CREATE INDEX ' || DBMS_ASSERT.SIMPLE_SQL_NAME(l_arch || '_CUST_IX') ||
                    ' ON ' || DBMS_ASSERT.SIMPLE_SQL_NAME(l_arch) || ' (customer_id)';
  EXECUTE IMMEDIATE 'ALTER TABLE orders EXCHANGE PARTITION ' || DBMS_ASSERT.SIMPLE_SQL_NAME(l_old) ||
                    ' WITH TABLE ' || DBMS_ASSERT.SIMPLE_SQL_NAME(l_arch) ||
                    ' INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES';
  EXECUTE IMMEDIATE 'ALTER TABLE orders DROP PARTITION ' || DBMS_ASSERT.SIMPLE_SQL_NAME(l_old) || ' UPDATE GLOBAL INDEXES';
END;
/

Every statement is DDL: each commits, none can be rolled back. Run it outside any transactional work, and log each step.

3f. Statistics travel with the segment

After an exchange, the partition-level statistics of p and the table-level statistics of t are swapped too (12c; earlier versions swap partition stats, and global stats may need re-gathering). The loading pattern in 3b therefore gathers stats on the staging table before the exchange, so the partition arrives with correct statistics.

4. What Breaks at Scale

Global indexes are the cost. A global index on orders has entries for every partition; exchanging a partition invalidates 40M of them. UPDATE GLOBAL INDEXES maintains them (synchronously pre-12c: minutes; asynchronously 12c+: the DDL returns at once, cleanup runs later). Without it the index goes UNUSABLE. Design rule for partitioned tables: local indexes wherever possible, global only where the query pattern forces it (a unique key not containing the partition key).
Foreign keys. A partitioned table referenced by an enabled FK from another table cannot have partitions exchanged out that contain referenced rows (ORA-02266-style); and a FK on the partitioned table needs a matching FK on the exchange table. Reference partitioning (child partitioned by the parent's key) makes CASCADE exchanges possible.
Triggers do not fire, and no DML is generated — replication based on redo (GoldenGate, Streams-style CDC) sees a DDL, not 40M inserts. If a downstream system needs the rows, it needs its own load.
Row movement: rowids of exchanged rows change. Rowid-based chunking (DBMS_PARALLEL_EXECUTE) created before an exchange is invalid after it.
WITHOUT VALIDATION with real data puts rows into a partition they do not belong in. Partition pruning then skips them for queries on the partition key — they become invisible to correct queries and visible to full scans. This is a silent data-correctness bug; validate whenever the staging table came from anywhere but an empty state.

5. Pitfalls

ORA-14097 on "identical" tables — hidden/unused/virtual columns, BYTE/CHAR, NOT NULL. Use FOR EXCHANGE WITH.
ORA-14098 — local index without a counterpart on the exchange table (or vice versa), or INCLUDING INDEXES with a mismatched unique index.
ORA-14099 — rows outside the partition's range with WITH VALIDATION; with WITHOUT, no error and wrong data.
Global indexes UNUSABLE after forgetting UPDATE GLOBAL INDEXES.
Exchanging into a partition that is not empty — its old contents end up in the exchange table; nothing is lost, but the "load" replaced data instead of adding to it. Truncate the partition first if that was the intent, or use a different partition.
Expecting the archive table to be a copy — it is the segment. Dropping it drops the data.
Materialized view logs on the partitioned table — the exchange does not record 40M inserts in the log; fast refresh is now incorrect; complete refresh required.
Doing it inside a transaction — each ALTER commits.