Posts

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)”
Posted on

ora2pg Migration Example: Oracle HR Schema to PostgreSQL

colorful stacked fabrics with intricate patterns

Oracle's HR schema is the closest thing the database world has to a universal reference.

Every Oracle DBA has seen it.

Every migration consultant uses it to explain how the process works.

What makes it genuinely useful as a case study is not its size — it is seven tables and 107 employees.

What makes it useful is that it contains, in minimal form, the exact patterns that make Oracle-to-PostgreSQL migrations non-trivial: sequences wired to triggers, stored procedures, type mismatches that compile cleanly and fail at runtime.

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 things that required manual intervention — and why each one matters far more on a production schema than it does on a demo.

Continue reading “ora2pg Migration Example: Oracle HR Schema to PostgreSQL”