Posts

Posted on

Oracle to Postgres Migration: CO Schema Issues and Fixes

Sea Ice Patterns

Oracle's CO (Customer Orders) schema is the modern replacement for the older OE schema.

It ships with Oracle 19c, it is actively maintained, and it is built the way most real Oracle applications are built today: IDENTITY columns instead of sequence-trigger pairs, JSON stored in BLOB columns, and views that use Oracle-specific SQL functions.

I ran the full migration using ora2pg 25.0 with Oracle 19c as the source and PostgreSQL 18 as the target.

This post covers the five problems that required manual intervention — and why each one will appear in almost every production schema you migrate.

This is the third post in the series.

The HR schema post covered sequence-trigger patterns, %TYPE parameters, and the FK re-apply bug.

The SH schema post covered partitioned tables, bitmap indexes, and materialized views.

CO introduces three new problem categories not seen in either of those.

Continue reading “Oracle to Postgres Migration: CO Schema Issues and Fixes”
Posted on

Oracle to Postgres Migration Step by Step: The SH Schema (Partitions, Materialized Views, Bitmap Indexes)

multicolored paint splatter

The Oracle SH (Sales History) schema is the standard Oracle example of a star schema data warehouse.

It has a central SALES fact table, six dimension tables, two materialized views, range-partitioned fact tables, and bitmap indexes on low-cardinality columns.

All of those features exist in every real Oracle data warehouse.

And all of them require decisions that a straightforward ora2pg run won't make for you.

This post walks through the exact steps I used to migrate SH from Oracle 19c to PostgreSQL 18 using ora2pg.

The Oracle SH sample schema is available on GitHub.

Continue reading “Oracle to Postgres Migration Step by Step: The SH Schema (Partitions, Materialized Views, Bitmap Indexes)”