# 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](../course-plan.md) · Appendix entry: [EXCHANGE PARTITION](../appendix.md#exchange-partition) · Related: [TRUNCATE](truncate.md), [APPEND HINT](append-hint.md), [PARALLEL DML](parallel-dml.md), [INSERT SELECT](insert-select.md)

---

## 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](dbms_parallel_execute.md)) 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

1. `ORA-14097` on "identical" tables — hidden/unused/virtual columns, `BYTE`/`CHAR`, `NOT NULL`. Use `FOR EXCHANGE WITH`.
2. `ORA-14098` — local index without a counterpart on the exchange table (or vice versa), or `INCLUDING INDEXES` with a mismatched unique index.
3. `ORA-14099` — rows outside the partition's range with `WITH VALIDATION`; with `WITHOUT`, no error and wrong data.
4. Global indexes `UNUSABLE` after forgetting `UPDATE GLOBAL INDEXES`.
5. 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.
6. Expecting the archive table to be a copy — it is *the* segment. Dropping it drops the data.
7. 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.
8. Doing it inside a transaction — each `ALTER` commits.

## 6. Related

- [TRUNCATE](truncate.md) — emptying a partition when the data is not needed.
- [APPEND HINT](append-hint.md) / [PARALLEL DML](parallel-dml.md) — building the staging table fast.
- [INSERT SELECT](insert-select.md) — the copy this replaces.
- [DBMS_PARALLEL_EXECUTE](dbms_parallel_execute.md) — rowid chunks vs partition maintenance.