Oracle Migration Safety Review¶

Quick Reference¶

§1 Scope¶

In scope — schema migration safety for Oracle 12c / 19c / 21c / 23ai:

• ALTER TABLE (add/drop/modify column, add/drop constraint, rename, move)
• CREATE / DROP / REBUILD INDEX (including ONLINE)
• Constraint management (FK, CHECK, UNIQUE with ENABLE NOVALIDATE pattern)
• Partition DDL (ADD/DROP/SPLIT/MERGE/EXCHANGE PARTITION, global index impact)
• Data backfill and transformation (CTAS, INSERT /*+ APPEND */, ROWID batching)
• Online table redefinition (DBMS_REDEFINITION)
• Migration file review (Flyway, Liquibase, custom PL/SQL deploy scripts)
• Rollback planning (DDL auto-commits — no transactional DDL rollback)

Out of scope — delegate to dedicated skills:

• Query optimization, bind variable tuning, plan stability → oracle-best-practise
• Application code changes → go-code-reviewer or language-specific reviewer
• Security hardening, privilege management → security-review

§2 Mandatory Gates¶

Execute gates sequentially. Each gate has a STOP condition.

Gate 1: Context Collection¶

If database access is available, run:

SELECT banner_full FROM v$version;
SELECT table_name, num_rows, blocks FROM dba_tables WHERE table_name = '<TABLE>';
SELECT segment_name, bytes/1024/1024 MB FROM dba_segments WHERE segment_name = '<TABLE>';

STOP: Cannot determine whether the target is Oracle. Redirect to appropriate skill.

PROCEED: At least Oracle version and table name known or conservatively assumed.

Gate 2: Scope Classification¶

STOP: Request is not migration-related. Redirect to oracle-best-practise.

PROCEED: Migration intent confirmed.

Gate 3: Risk Classification¶

STOP: Any UNSAFE item has no mitigation plan.

PROCEED: Every DDL statement has risk level and mitigation.

Gate 4: Output Completeness¶

Before delivering output, verify all §9 Output Contract sections present. §9.9 Uncovered Risks must never be empty.

§3 Depth Selection¶

Force Standard or higher when any signal appears: column type change, NOT NULL addition, constraint enforcement, partition DDL with global indexes, MOVE/SHRINK operations, column removal, edition/license-dependent features.

§4 Degradation Modes¶

When context is incomplete, degrade gracefully — never fabricate information.

Hard rule: Never claim "SAFE" without evidence. In Degraded/Minimal mode, mark as "SAFE (assumed — verify against production)" and list all assumptions in §9.9.

§5 DDL Safety Checklist¶

Execute every item. Mark SAFE / WARN / UNSAFE with evidence.

5.1 DDL Auto-Commit & Lock Assessment¶

1. DDL auto-commit awareness — Oracle DDL issues implicit COMMIT before and after execution. This means:
2. Any uncommitted DML in the session is committed when DDL runs
3. DDL itself cannot be rolled back via ROLLBACK — it is permanent immediately
4. Failed DDL still commits the pre-DDL implicit COMMIT

5. Every DDL must have a documented manual rollback path

6. DDL_LOCK_TIMEOUT — set before every DDL session:

   ALTER SESSION SET DDL_LOCK_TIMEOUT = 3;
   

   Without this, DDL fails immediately with ORA-00054 (resource busy) if it cannot acquire an exclusive lock. With timeout, Oracle retries for N seconds. When uncertain about lock behavior → load references/oracle-ddl-lock-matrix.md.

7. Online DDL availability — Oracle supports ONLINE keyword for some operations (EE only):

8. CREATE INDEX ... ONLINE — allows concurrent DML during build
9. ALTER INDEX ... REBUILD ONLINE — non-blocking rebuild
10. ALTER TABLE ... MOVE ONLINE (12.2+) — non-blocking table reorganization

11. Check edition: ONLINE operations require Enterprise Edition or specific cloud tiers

12. Partition DDL and global index impact — partition operations (DROP/SPLIT/MERGE/EXCHANGE PARTITION) can invalidate global indexes. An UNUSABLE global index causes query failures. Mitigation: UPDATE INDEXES clause or planned global index rebuild.

5.2 Data Integrity¶

1. Column modification restrictions — Oracle cannot decrease column size if data exceeds new limit. Changing data type often requires DBMS_REDEFINITION or CTAS. Adding NOT NULL to column with existing NULLs fails — use phased approach.

2. Constraint enforcement — Oracle's two-step pattern:

   ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ENABLE NOVALIDATE;
   ALTER TABLE orders MODIFY CONSTRAINT fk_user VALIDATE;
   

   ENABLE NOVALIDATE enforces for new DML but skips validating existing rows. VALIDATE then checks existing data without blocking DML.

3. FK index requirement — unlike PostgreSQL, Oracle does not require indexes on FK columns, but missing FK indexes cause full table locks during parent table DML. Always create indexes on FK columns.

4. Sequence and identity impact — DDL on tables with identity columns or sequence-based defaults may affect sequence continuity. Verify after migration.

5.3 Backward Compatibility¶

1. Deployment ordering — column add → schema first, then app; column remove → app first, then schema; column rename → not directly supported before 23ai; use virtual column or view as compatibility layer.

2. Rollback planning — DDL auto-commits, so rollback is always manual:

   • ADD COLUMN → rollback is ALTER TABLE DROP COLUMN (but see item 11)
   • ADD CONSTRAINT → rollback is ALTER TABLE DROP CONSTRAINT
   • CREATE INDEX → rollback is DROP INDEX
   • Classify: manual-rollback / irreversible (DROP COLUMN data loss)
   • Consider Flashback Table / Flashback Database as safety net for catastrophic errors

5.4 Operational Safety¶

1. DROP COLUMN behavior — Oracle SET UNUSED is faster than DROP COLUMN for wide tables. SET UNUSED marks column invisible and inaccessible immediately; physical removal via DROP UNUSED COLUMNS can happen later during maintenance.

2. UNDO/TEMP space — bulk operations (CTAS, large backfills, DBMS_REDEFINITION) consume UNDO tablespace. Insufficient UNDO → ORA-30036 (unable to extend undo segment). Check space before starting.

3. Optimizer statistics — after bulk inserts, table moves, or partition exchanges, statistics are stale. Run DBMS_STATS.GATHER_TABLE_STATS post-migration to prevent plan regression.

4. Statement granularity — DDL auto-commits, so each DDL is an atomic irreversible step. Prefer one DDL per migration script for clear rollback mapping.

§6 Execution Plan (Standard + Deep)¶

Standard phased pattern for zero-downtime migration:

1. Phase 1 — Additive schema: add nullable columns, create indexes with ONLINE, constraints with NOVALIDATE
2. Phase 2 — Backfill: populate new columns in ROWID-range or PK-range batches with periodic COMMIT (see references/large-table-migration.md §3)
3. Phase 3 — App deploy: deploy code writing to both old and new schema
4. Phase 4 — Constraint validation: MODIFY CONSTRAINT ... VALIDATE, gather stats
5. Phase 5 — Cleanup (separate release): SET UNUSED old columns, DROP UNUSED COLUMNS during maintenance

Each phase: Pre-condition → SQL (with DDL_LOCK_TIMEOUT) → Validation → Rollback → Go/No-go.

For tables >10M rows needing restructuring, use DBMS_REDEFINITION (EE) or CTAS+swap. Details in references/large-table-migration.md.

§7 Anti-Examples¶

AE-1: DDL without DDL_LOCK_TIMEOUT¶

-- WRONG: fails immediately with ORA-00054 if any session holds lock
ALTER TABLE orders ADD (tracking_id VARCHAR2(50));
-- RIGHT:
ALTER SESSION SET DDL_LOCK_TIMEOUT = 3;
ALTER TABLE orders ADD (tracking_id VARCHAR2(50));

AE-2: ADD CONSTRAINT without NOVALIDATE¶

-- WRONG: validates all rows with exclusive lock — blocks everything on large table
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
-- RIGHT: two-step
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ENABLE NOVALIDATE;
ALTER TABLE orders MODIFY CONSTRAINT fk_user VALIDATE;

AE-3: DROP COLUMN on wide high-traffic table¶

-- WRONG: physically removes column data — expensive I/O, long lock
ALTER TABLE events DROP COLUMN legacy_data;
-- RIGHT: mark unused now, drop physically later
ALTER TABLE events SET UNUSED COLUMN legacy_data;
-- During maintenance window:
ALTER TABLE events DROP UNUSED COLUMNS;

AE-4: Partition DDL without global index plan¶

-- WRONG: global indexes become UNUSABLE after DROP PARTITION
ALTER TABLE logs DROP PARTITION logs_2023_q1;
-- RIGHT: include UPDATE INDEXES clause
ALTER TABLE logs DROP PARTITION logs_2023_q1 UPDATE INDEXES;

AE-5: Monolithic UPDATE on large table¶

-- WRONG: single UPDATE locks millions of rows, fills UNDO
UPDATE orders SET status = 'migrated' WHERE status IS NULL;
-- RIGHT: batch by ROWID range with periodic COMMIT (see §6)

AE-6: Style nitpick reported as migration risk¶

-- WRONG: "WARN — column name 'USR_NM' doesn't follow naming convention"
-- RIGHT: only flag naming if it causes functional problems

Extended anti-examples (AE-7 through AE-13) in references/migration-anti-examples.md.

§8 Migration Scorecard¶

Critical — any FAIL means overall FAIL¶

• [ ] DDL_LOCK_TIMEOUT set before every DDL session
• [ ] DDL auto-commit documented: no uncommitted DML in session before DDL
• [ ] Rollback SQL provided for every phase (manual rollback since DDL auto-commits)

Standard — 4 of 5 must pass¶

• [ ] Constraints use ENABLE NOVALIDATE + VALIDATE two-step on tables >100K rows
• [ ] Large table restructuring uses DBMS_REDEFINITION or CTAS (not direct ALTER)
• [ ] Backward-compatible deployment order (additive before app, removal after app)
• [ ] Batch operations use ROWID/PK-range with periodic COMMIT, not monolithic DML
• [ ] Validation SQL provided for each phase

Hygiene — 3 of 4 must pass¶

• [ ] UNDO/TEMP space assessed for bulk operations
• [ ] DBMS_STATS.GATHER_TABLE_STATS planned after bulk changes
• [ ] Post-deploy monitoring specified (AWR/ASH or V$SQL baseline comparison)
• [ ] Global index impact assessed for all partition DDL

Verdict: X/12; Critical: Y/3; Standard: Z/5; Hygiene: W/4. PASS requires: Critical 3/3 AND Standard ≥4/5 AND Hygiene ≥3/4.

§9 Output Contract¶

Every migration review MUST produce these sections. Write "N/A — [reason]" if inapplicable.

### 9.1 Context Gate
| Item | Value | Source |

### 9.2 Depth & Mode
[Lite/Standard/Deep] × [review/generate/plan] — [rationale]

### 9.3 Risk Assessment Table
| # | DDL Statement | Lock Type | Online? | Risk | Notes |

### 9.4 Execution Plan (Standard/Deep; "N/A — Lite" for Lite)

### 9.5 Migration SQL (with DDL_LOCK_TIMEOUT, ONLINE, NOVALIDATE as applicable)

### 9.6 Validation SQL

### 9.7 Rollback Plan (per-phase; all manual since DDL auto-commits)

### 9.8 Post-Deploy Checks

### 9.9 Uncovered Risks (MANDATORY — never empty)
| Area | Reason | Impact | Follow-up |

Volume rules: - UNSAFE: always fully detailed with mitigation - WARN: up to 10; overflow to §9.9 - SAFE: summary row only - §9.9 minimum: document all assumptions and edition/license unknowns

Scorecard summary (append after §9.9):

Scorecard: X/12 — Critical Y/3, Standard Z/5, Hygiene W/4 — PASS/FAIL
Data basis: [full context | degraded | minimal | planning]

§10 Reference Loading Guide¶