Oracle Database / Advanced: secure and recoverable evolution / Lesson 18

Schema evolution and operational review

Plan compatible releases with DDL checkpoints, dependency evidence and clear limits on online features.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Build expand, backfill, switch and contract phases.
Review invalid objects, licensing and recovery before release.

A release has overlapping readers and writers

A deployment often runs old and new application instances simultaneously. Renaming or dropping a column in the same step that changes the new code can break the old instances still serving requests. Plan an additive expansion, a controlled data backfill, a reader or writer switch and a later contraction after evidence shows the old path is retired. Oracle DDL implicit commits make these checkpoints especially important. A rollback of the application binary cannot automatically undo committed schema changes or the new data meaning. Define both forward repair and compatible rollback paths before release.

An additive column still needs a contract

The illustrative change introduces delivery_zone without immediately requiring every historical row to have it. Before approval, define whether NULL means unknown, not yet migrated or legitimately inapplicable. The backfill must distinguish those meanings instead of filling every row with a convenient fiction. New writers and old writers may disagree during overlap, so choose which code populates the field and whether a temporary compatibility mechanism is required. Adding a default or constraint can have release-specific locking and execution behavior; do not promise “instant” or “online” from syntax alone without the deployed-version evidence.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

ALTER TABLE shipments ADD (delivery_zone VARCHAR2(12 CHAR));

An illustrative additive DDL phase with implicit-commit consequences. It is not executed and makes no claim about lock duration or metadata-only behavior.

Make progress restartable and explainable

A large backfill should have a stable selection rule, bounded transaction work, deterministic derived values and a durable progress record. Row identifiers chosen only by a changing OFFSET can skip or repeat work as data changes. Select by a reviewed key range or equivalent stable method, and make replay harmless where possible. Track unmatched source mappings as rejected work rather than assigning a guessed zone. Batching trades one giant lock and undo footprint for partial committed progress, so recovery must understand which batches are durable. Neither strategy is universally correct without workload and downtime requirements.

Inspect invalid objects and actual errors

Schema changes can invalidate dependent views and PL/SQL units. USER_OBJECTS supplies status, while USER_ERRORS exposes compilation diagnostics for objects in the user’s scope. An INVALID status is a symptom, not the complete reason. Recompiling blindly can hide a missing grant, removed column or incompatible signature until the next runtime call. Review dependency changes and compile errors, then test the actual runtime principal through its permitted interface. The inventory below is read-only theory and returns environment-dependent rows; no invalidation or compilation was triggered during course authoring.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT object_name, object_type, status
FROM user_objects WHERE status = 'INVALID'
ORDER BY object_type, object_name;

SELECT name, type, line, position, text
FROM user_errors ORDER BY name, sequence;

The first query identifies invalid owned objects; the second gives available diagnostics rather than repairing them. Empty results have their own scope limits.

Online capabilities need prerequisites and proof

Edition-based redefinition can support application upgrades through editions, editioning views and appropriate crossedition mechanisms. Tables themselves are not editioned copies, so EBR is not a general time machine for data. Online table redefinition and other maintenance features have their own restrictions, prerequisites and entitlement considerations. Select one justified mechanism and rehearse it against the required object types and concurrent workload in an independent lab. A final release review should cover compatibility, denied access paths, restore evidence, operational monitoring and exact licensed tools. Record what was actually tested and keep unexecuted assumptions visible to the next maintainer.

Common errors and fixes

ORA-04063

Why it happens: A referenced view or stored unit has errors, often after an incompatible dependency change.
How to fix it: Inspect scoped compilation diagnostics and the changed dependency.
Prevent it next time: Include dependency and runtime-account checks in release validation.

Old application instances fail after a column removal

Why it happens: The schema contract changed before all old readers were retired.
How to fix it: Restore a compatible path or apply the reviewed forward repair.
Prevent it next time: Separate expansion from contraction with explicit retirement evidence.

A restarted backfill skips records

Why it happens: Progress was based on changing offsets or an ambiguous selection rule.
How to fix it: Reconcile by stable keys and resume only from a documented checkpoint.
Prevent it next time: Make batch boundaries and replay semantics part of the migration design.

Check your understanding

1. When is it safe to remove the old delivery field after introducing delivery_zone?

Show worked solution

Only after the backfill is reconciled, new writes follow the intended contract and evidence shows no supported reader or writer depends on the old field. Keep the rollback compatibility requirement and historical recovery plan explicit. Time elapsed alone is not sufficient proof of retirement.

2. Does switching an EBR edition restore table rows to their prior values?