The 10 Biggest Oracle Migration Mistakes (And How to Avoid Them)
The Mistakes Are Predictable — Which Means Avoidable
After enough Oracle-to-PostgreSQL migrations, a pattern emerges: the things that go wrong are almost never exotic. They're the same ten mistakes, and most of them happen beforeanyone writes a line of conversion code — in the assumptions teams carry over from Oracle without noticing they're assumptions.
That's good news. A predictable failure is a preventable one. Here are the ten that show up most, what each one actually does to you, and the one-line fix for each.
1. Starting with the data move, not the assessment
Why it bites: You discover the hard constructs at the worst possible time — mid-project, with a date already committed.
The fix: Assess first. Inventory objects, PL/SQL surface, and hard constructs before moving a single row.
2. Trusting row counts as validation
Why it bites: Counts match even when every numeric was truncated, every timestamp shifted an hour, every NULL became an empty string.
The fix: Validate at the column level — checksums or per-partition cryptographic proof, not just counts.
3. Collapsing NULL and empty string
Why it bites: Oracle treats '' as NULL; PostgreSQL does not. A naive load turns distinct values into the same thing and corrupts logic silently.
The fix: Decide the mapping explicitly per column, and assert it in validation. (NULL, 'foo') ≠ ('', 'foo').
4. Forgetting to resync sequences at cutover
Why it bites: The new sequence starts at 1 while the table already has IDs up to 4 million → "duplicate key value violates unique constraint" on the first insert.
The fix: After load, setval() every sequence to MAX(id). It's the single most common post-cutover error.
5. Mapping every NUMBER to NUMERIC
Why it bites: NUMERIC everywhere is correct but slow. Integer keys and counters lose index and arithmetic performance for no reason.
The fix: NUMBER(p,0) → INTEGER/BIGINT; NUMBER(p,s) → NUMERIC(p,s); only unscaled NUMBER → NUMERIC.
6. Assuming Oracle DATE is date-only
Why it bites: Oracle DATE carries a time component. Map it to PG DATE and you silently drop hours, minutes, seconds.
The fix: Oracle DATE → PostgreSQL TIMESTAMP. Reserve PG DATE for genuinely date-only columns.
7. Emulating packages instead of translating them
Why it bites: A package-emulation layer adds overhead on hot paths, muddies stack traces, and becomes a permanent maintenance burden.
The fix: Translate each package construct to its idiomatic PG equivalent: schema + functions, GUC/temp-table state.
8. Leaving CONNECT BY for last
Why it bites: Hierarchical queries need a genuine rewrite to WITH RECURSIVE — not a find-and-replace. Found late, they stall a release.
The fix: Grep for CONNECT BY in the assessment; convert to recursive CTEs early, with test data behind each.
9. Big-bang cutover with no CDC
Why it bites: Without change capture, the cutover window equals the full re-load time — hours of downtime on a large database.
The fix: Run CDC to keep both databases in sync; cutover collapses to the seconds it takes to flip a connection string.
10. Skipping ANALYZE and blaming PostgreSQL
Why it bites: A freshly loaded table has no statistics. The planner makes bad choices, queries crawl, and PostgreSQL takes the blame.
The fix: Run ANALYZE after load, review indexes, and baseline key queries before declaring a regression.
The Pattern Behind All Ten
Read the list again and a theme appears: nine of the ten are caught by a thorough assessment and a real validation step. They're not failures of skill — they're failures of sequence. Teams that move data first and ask questions later hit every one of these. Teams that assess first, validate at the column level, and rehearse the cutover hit almost none.
None of these mistakes mean Oracle and PostgreSQL are far apart. They're close — close enough that the differences hide in plain sight until a NULL, a DATE, or an unsynced sequence reminds you they're there.
The Honest Verdict
You don't avoid these by being clever on cutover night. You avoid them by front-loading the boring work: assess, map the types deliberately, validate the data cryptographically, and rehearse the switch. Do that and the ten biggest mistakes become ten boxes you already ticked.
Catch nine of the ten automatically
DBMigrateAIPro's assessment flags the hard constructs, the type-mapping traps, and the dependency issues behind most of this list — and its validation proves the data landed correctly at the column level. Free for Year 1, no signup.
- 🔗 Download the desktop tool: medaxai.com
- 🔗 Related — The 40-Point Oracle Migration Checklist (Before You Start)
- 🔗 Related — Why Data Validation Is the Most Underrated Migration Step