Exchange Partition, Rolling Windows, Bulk Loads, Archiving, and Lifecycle Automation

Learning outcomes

ServiceHub receives 20 million September event rows from a staging pipeline. Row-by-row insert into the live partition generates a long load window and makes rollback awkward. The data lifecycle also needs to remove July as an archive unit. Partition exchange swaps segment metadata between a partition and a structurally compatible nonpartitioned table, letting a prepared segment enter or leave the live table quickly.

1. Build a monthly rolling table

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_events_roll PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_events_roll (  event_id NUMBER NOT NULL,  event_ts DATE NOT NULL,  region_code VARCHAR2(8) NOT NULL,  event_type VARCHAR2(20) NOT NULL,  payload VARCHAR2(200),  CONSTRAINT sh19_roll_pk PRIMARY KEY(event_id,event_ts))PARTITION BY RANGE(event_ts) (  PARTITION p2026m07 VALUES LESS THAN (DATE '2026-08-01'),  PARTITION p2026m08 VALUES LESS THAN (DATE '2026-09-01'),  PARTITION p2026m09 VALUES LESS THAN (DATE '2026-10-01'),  PARTITION p_future VALUES LESS THAN (MAXVALUE));INSERT INTO servicehub_events_rollSELECT  700000+LEVEL,  DATE '2026-07-01' + MOD(LEVEL,31),  CASE MOD(LEVEL,2) WHEN 0 THEN 'AZ-N' ELSE 'AZ-S' END,  'CLOSED',  RPAD('archive',20,'x')FROM dualCONNECT BY LEVEL <= 5000;COMMIT;

2. Deliberately wrong: hand-create a staging table with a subtle mismatch

CREATE TABLE servicehub_events_stage_bad (  event_id NUMBER NOT NULL,  event_ts DATE NOT NULL,  region_code VARCHAR2(8) NOT NULL,  event_type VARCHAR2(20) NOT NULL,  payload VARCHAR2(100));ALTER TABLE servicehub_events_roll  EXCHANGE PARTITION p2026m09  WITH TABLE servicehub_events_stage_bad  WITH VALIDATION;-- Expected structural exchange error such as:-- ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Exchange compares physical column shape/properties more strictly than an ordinary insert-select. Manually duplicating DDL is brittle when invisible/virtual/unused/internal column attributes evolve.

3. FOR EXCHANGE WITH TABLE creates the exact column shape

DROP TABLE servicehub_events_stage_bad PURGE;CREATE TABLE servicehub_events_stageFOR EXCHANGE WITH TABLE servicehub_events_roll;SELECT column_id,column_name,data_type,data_length,nullableFROM user_tab_columnsWHERE table_name IN (  'SERVICEHUB_EVENTS_ROLL',  'SERVICEHUB_EVENTS_STAGE')ORDER BY table_name,column_id;

FOR EXCHANGE WITH TABLE creates no data and no indexes, but it copies the column ordering/properties needed for exchange. Constraints/indexes/stats still require lifecycle design.

4. Boundary validation prevents the wrong month entering the partition

INSERT INTO servicehub_events_stageSELECT  900000+LEVEL,  DATE '2026-09-01' + MOD(LEVEL,30),  'AZ-N',  'ASSIGNED',  RPAD('load',30,'l')FROM dualCONNECT BY LEVEL <= 3000;INSERT INTO servicehub_events_stageVALUES(999999,DATE '2026-10-05','AZ-S','OPEN','wrong month');COMMIT;ALTER TABLE servicehub_events_roll  EXCHANGE PARTITION p2026m09  WITH TABLE servicehub_events_stage  WITH VALIDATION;-- Expected:-- ORA-14099: all rows in table do not qualify for specified partition

WITHOUT VALIDATION is tempting because it is faster, but it trusts the staging data. If out-of-bound rows enter a partition, optimizer pruning can make them effectively invisible to predicates that assume valid partition boundaries. Use WITHOUT VALIDATION only when an upstream constraint/process proves the boundary.

5. Repair the staging data and exchange the load

DELETE FROM servicehub_events_stageWHERE event_ts >= DATE '2026-10-01';COMMIT;SELECT MIN(event_ts),MAX(event_ts),COUNT(*)FROM servicehub_events_stage;ALTER TABLE servicehub_events_roll  EXCHANGE PARTITION p2026m09  WITH TABLE servicehub_events_stage  WITH VALIDATION;SELECT COUNT(*) AS live_september_rowsFROM servicehub_events_rollPARTITION(p2026m09);SELECT COUNT(*) AS old_partition_rows_now_in_stageFROM servicehub_events_stage;

The exchange is primarily metadata movement between segments. The formerly live partition's contents move into the staging table and the prepared staging segment becomes the partition. On an empty target partition, the stage table becomes empty.

6. Indexes, constraints and statistics need an explicit contract

Local indexes are partition-aligned, but FOR EXCHANGE WITH TABLE does not create matching staging indexes. INCLUDING INDEXES requires compatible index structures. Global indexes can be marked unusable by exchange unless maintained; UPDATE INDEXES preserves availability but can turn an otherwise fast exchange into heavier logged index maintenance.

Statistics can be gathered on staging before exchange when the workflow supports it, but verify table/partition statistics after exchange and publish/pending-statistics policy if production plan stability matters.

7. Exchange an old month out before archival

CREATE TABLE servicehub_events_archiveFOR EXCHANGE WITH TABLE servicehub_events_roll;ALTER TABLE servicehub_events_roll  EXCHANGE PARTITION p2026m07  WITH TABLE servicehub_events_archive  WITH VALIDATION;SELECT COUNT(*) AS archived_rowsFROM servicehub_events_archive;SELECT COUNT(*) AS remaining_july_partition_rowsFROM servicehub_events_roll PARTITION(p2026m07);

Now the archive data is a normal nonpartitioned table that can be validated/exported before the empty partition is truncated/dropped. This gives a clean rollback point: exchange it back if validation fails and the live partition has not been repopulated.

8. Export before destruction

expdp system@//localhost:1521/FREEPDB1   tables=SERVICEHUB_OWNER.SERVICEHUB_EVENTS_ARCHIVE   directory=DATA_PUMP_DIR   dumpfile=servicehub_2026m07.dmp   logfile=servicehub_2026m07.log

Data Pump is a logical export, not an RMAN backup. Verify the log, dump storage, encryption/security policy and import rehearsal before treating the file as an archive. Parallel Data Pump is not licensed in Free under the current matrix, so the mandatory command is intentionally serial.

9. Rolling-window closeout

ALTER TABLE servicehub_events_roll  DROP PARTITION p2026m07  UPDATE INDEXES;SELECT partition_name,partition_positionFROM user_tab_partitionsWHERE table_name='SERVICEHUB_EVENTS_ROLL'ORDER BY partition_position;

Dropping a partition is immediate DDL and bypasses the recycle bin. Your real rollback is the exported/exchanged copy plus tested backup/recovery—not an assumption that DROP can be undone.

10. Row-by-row movement versus exchange

11. Cleanup

DROP TABLE servicehub_events_stage PURGE;DROP TABLE servicehub_events_archive PURGE;DROP TABLE servicehub_events_roll PURGE;

12. Production judgment

Exchange partition is ideal when ingestion/archive units align with partition boundaries and staging can be structurally and semantically validated before publication. Build automation around row-count/hash/business checks, index usability, statistics, export/backup evidence and a defined exchange-back rollback point.

Oracle Partitioning is included in Free but is an extra-cost option on EE/EE-ES. Parallel Data Pump is not part of the Free path. Lesson 4 turns to throughput inside one statement—and why parallel execution can improve elapsed time while consuming enough CPU/I/O/PX servers to hurt everyone else.

Authoritative references

• Partition Administration — Exchange — exchange workflows and validation
• CREATE TABLE — FOR EXCHANGE WITH TABLE
• ALTER TABLE — EXCHANGE/UPDATE INDEXES syntax
• Data Pump Export — logical export behavior
• VLDB and Partitioning Guide — rolling-window/VLDB patterns