Oracle Schema Diff

Problem

Our team maintains several Oracle schemas across environments (dev, test, UAT, prod). Any time a dev made a DDL change locally, propagating it upward was manual: open two SQL Developer windows side by side, click through object lists, hand-write a migration script.

Two developers doing this simultaneously meant merge conflicts in migration files. Doing it wrong meant a missed column or constraint that broke the next environment.

What it does

ora-diff connects to two schemas via ODP.NET, queries ALL_TABLES, ALL_COLUMNS, ALL_CONSTRAINTS, ALL_INDEXES, and ALL_SEQUENCES, then diffs the object graphs and emits an ordered migration script.

ora-diff --source DEV --target UAT --out migration.sql

Output is deterministic: tables before constraints, drop constraints before drop columns, add columns before add constraints. Running the same diff twice produces the same script.

Implementation notes

The hardest part was constraint ordering. Oracle’s DEFERRABLE and INITIALLY DEFERRED flags interact with ENABLE/DISABLE in non-obvious ways. I ended up building a small dependency graph and topologically sorting the emitted statements.

The second hardest part: Oracle doesn’t give you CREATE INDEX DDL back — you have to reconstruct it from ALL_INDEXES + ALL_IND_COLUMNS. The reconstructed DDL doesn’t always match what the developer originally wrote (column order, hints), but it’s functionally equivalent.

What I’d do differently

I’d add a dry-run mode that connects to the target and validates that the generated script would actually apply cleanly before you run it in UAT. Currently you find out at apply time.