DBAtlas · Database operations atlas

Bulk load or archive data with exchange partition

OraclePartitioning

Command

-- 1) Staging table that exactly matches the target (12.2+):
CREATE TABLE customer_app.sale_stage
  FOR EXCHANGE WITH TABLE customer_app.sale_part;

-- 2) Load the staging table and build indexes matching the local indexes:
INSERT /*+ APPEND */ INTO customer_app.sale_stage
SELECT * FROM customer_app.sale_load WHERE created_at >= DATE '2027-01-01';
COMMIT;
CREATE INDEX customer_app.sale_stage_customer_ix ON customer_app.sale_stage (customer_id);

-- 3) Swap (dictionary-only operation, no data movement):
ALTER TABLE customer_app.sale_part
  EXCHANGE PARTITION p2027 WITH TABLE customer_app.sale_stage
  INCLUDING INDEXES WITHOUT VALIDATION
  UPDATE GLOBAL INDEXES;

-- 4) Verify:
SELECT COUNT(*) FROM customer_app.sale_part PARTITION (p2027);
SELECT index_name, partition_name, status
FROM   dba_ind_partitions
WHERE  index_owner = 'CUSTOMER_APP' AND status <> 'USABLE';

What it does

Swaps the segment of a partition with the segment of a regular table in the dictionary; millions of rows "enter" the table or "leave" it for archiving in seconds. FOR EXCHANGE WITH TABLE creates a staging table that guarantees column and hidden-column compatibility.

When to use it

For DWH/ETL bulk loads, moving an old partition out to an archive table, and preparing indexes and statistics without affecting the live table during the load.

Caution

Requires the Partitioning option (extra-cost EE). WITHOUT VALIDATION does not check that staging rows fit the partition bounds; wrong data stays in the wrong partition and queries can return wrong results; if unsure use WITH VALIDATION (full scan). Without UPDATE GLOBAL INDEXES global indexes become UNUSABLE. Unique/PK constraint states must match on both sides.

Open in DBAtlas →

Related