TL;DR: The manual work in an Oracle to PostgreSQL migration depends entirely on how the schema was built — not on its size.
This guide maps three common Oracle schema patterns to the specific migration example that covers each one: legacy OLTP, modern OLTP, and data warehouse.
If you are unsure which applies to your database, the diagnostic section below gives you four questions to find out.
Three DBAs can all say “I’m migrating Oracle to PostgreSQL” and face completely different problems.
The first has a 15-year-old ERP running on Oracle 10g with sequence-trigger pairs on every table.
The second has a customer orders application built on Oracle 19c with IDENTITY columns and JSON stored in BLOBs.
The third is migrating a data warehouse with range-partitioned fact tables, materialized views, and bitmap indexes.
ora2pg handles all three.
The manual work is completely different in each case.
Table of Contents
Why Does Schema Type Determine Migration Effort?
Oracle supports several application patterns — legacy OLTP, modern OLTP, and analytics/data warehouse — and each uses a distinct set of Oracle-specific features.
PostgreSQL supports the same patterns, but differently.
The translation work concentrates wherever Oracle features have no direct PostgreSQL equivalent: sequences wired to triggers, IDENTITY column sequence management, bitmap indexes, materialized view refresh scheduling.
Knowing your schema pattern before you start tells you exactly where the manual work is, before you open ora2pg.
Legacy OLTP: The HR Schema
What this looks like: Tables with sequence-based primary keys, BEFORE INSERT triggers that assign the next sequence value on insert, stored procedures, and foreign key constraints.
Real-world examples: ERP systems, HR platforms, CRM databases, any Oracle application built before Oracle 12c.
The central problem is the Oracle pre-12c auto-increment pattern:
CREATE OR REPLACE TRIGGER employees_bir
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
SELECT employees_seq.NEXTVAL INTO :NEW.employee_id FROM DUAL;
END;
ora2pg exports this trigger as PL/pgSQL.
It compiles without errors in PostgreSQL.
It is the wrong pattern — PostgreSQL handles auto-increment through column defaults pointing to a sequence, not triggers.
The trigger approach works but adds unnecessary overhead on every insert and masks the sequence dependency from the schema reader.
The HR schema guide covers this conversion in full, along with two other problems that appear in almost every legacy OLTP migration: the %TYPE parameter bug in exported PL/pgSQL functions, and the ora2pg FK re-apply bug.
Read the full HR schema migration example →
Modern OLTP: The CO Schema
What this looks like: Primary keys using GENERATED BY DEFAULT ON NULL AS IDENTITY, BLOB columns storing JSON, and views that use Oracle-specific SQL functions.
Real-world examples: Customer-facing transaction databases, order management systems, any Oracle application built or modernised on Oracle 12c or later.
CO introduces three problems that do not appear in the HR schema.
The first is IDENTITY column sequence reset.
After ora2pg loads the data, the sequences are still at their starting value — not at the maximum loaded value.
The first application insert after migration fails with a duplicate key violation.
The fix is a one-line ALTER SEQUENCE per table, but it must run after the data load.
The second is BLOB-stored JSON.
ora2pg exports the column as bytea.
The data is there but unusable for JSON queries.
The correct target type is jsonb, which requires a data migration step alongside the schema conversion.
The third is Oracle-specific SQL in views.LISTAGG, PIVOT, CONNECT BY, and NVL have no direct PostgreSQL equivalents.
Views using these functions must be rewritten in standard SQL before they will work.
Read the full CO schema migration example →
If your schema combines patterns from both legacy and modern OLTP — for example, a 15-year-old application extended on Oracle 19c — read both the HR and CO guides.
If you would rather have an expert map the manual work before committing your team to the project, a fixed-fee assessment covers exactly that →
Data Warehouse: The SH Schema
What this looks like: Range-partitioned fact tables, materialized views used for pre-aggregated reporting, and bitmap indexes on low-cardinality dimension columns.
Real-world examples: Oracle data warehouses, BI reporting platforms, analytics databases, any schema designed for reporting rather than transaction processing.
SH introduces three problems not seen in HR or CO.
The first is partition export.
ora2pg splits a partitioned table into two separate export files: one for the parent table definition and one for the child partition tables.
Loading them in the wrong order breaks the migration.
The correct order is parent table first, then data, then child partitions — and that order is different from what the ora2pg project template suggests by default.
The second is bitmap index conversion.
Oracle bitmap indexes have no PostgreSQL equivalent.
ora2pg converts them to standard B-tree indexes, which is the correct outcome — but it produces a GIN index on integer foreign key columns in some configurations, which is wrong.
Each converted index must be checked before the data load.
The third is materialized view refresh scheduling.
PostgreSQL’s REFRESH MATERIALIZED VIEW is equivalent to Oracle’s DBMS_MVIEW.REFRESH.
The scheduling mechanism is not.
Oracle uses DBMS_JOB or DBMS_SCHEDULER.
PostgreSQL has neither.
Refresh jobs must be recreated using pg_cron or an external scheduler.
Read the full SH schema migration example →
Which Guide Applies to Your Schema?
Answer these four questions:
1. How are primary keys implemented?
Sequence + BEFORE INSERT trigger → HR guide (legacy OLTP).GENERATED ... AS IDENTITY → CO guide (modern OLTP).
Natural keys or no identity pattern → read both.
2. Does your schema have partitioned tables?
Yes, with PARTITION BY RANGE or PARTITION BY LIST → SH guide (data warehouse).
No → skip the SH guide unless you have materialized views.
3. Do you have materialized views?
Yes → the SH guide covers the refresh scheduling problem in full.
No → this section does not apply.
4. Do any views use Oracle-specific functions — NVL, DECODE, LISTAGG, CONNECT BY, ROWNUM, or PIVOT?
Yes → the CO guide covers manual view rewriting in detail.
No → your views will likely export cleanly with ora2pg.
Most real production schemas are a combination.
A 15-year-old ERP extended on Oracle 19c might have sequence-trigger pairs on legacy tables and IDENTITY columns on newer ones — read both the HR and CO guides.
If the same schema also drives reporting queries on partitioned tables, add the SH guide.
Frequently Asked Questions
Which guide should I start with if I have never done an Oracle to PostgreSQL migration before?
Start with the HR schema guide.
It covers the smallest schema with the most universal problems.
Sequence-trigger pairs, stored procedure type mismatches, and the FK re-apply bug appear in almost every Oracle migration, regardless of schema size or complexity.
The HR guide also explains the complete ora2pg workflow before the CO and SH guides introduce their additional layers.
Do I need to read all three guides?
Not necessarily.
Read the one that matches your primary schema pattern first.
If your schema combines elements from more than one category — IDENTITY columns and partitioned tables, for example — read the relevant sections of both guides.
Each guide is structured so that the core workflow is in the first half and the schema-specific problems are in the second.
Are these guides specific to a particular Oracle version?
The migration steps work with Oracle 12c and later.
IDENTITY columns, covered in the CO guide, require Oracle 12c minimum.
The sequence-trigger pattern, covered in the HR guide, is the Oracle pre-12c idiom but also appears in 12c and 19c schemas that were not modernised.
All three guides use ora2pg 25.0 with Oracle 19c as source and PostgreSQL 18 as target.
What if my schema has none of these patterns?
A schema with no sequence-trigger pairs, no IDENTITY columns, no partitioning, and no materialized views is the simplest migration case.
The HR guide still applies — it covers the base ora2pg workflow, data type mapping, file load order, and TEST validation that every migration requires.
In Summary
Oracle to PostgreSQL migration problems are predictable — once you know which schema pattern you are starting from.
Legacy OLTP schemas built before Oracle 12c need sequence-trigger cleanup and stored procedure fixes.
Modern OLTP schemas on Oracle 12c and later need IDENTITY sequence resets and view rewrites.
Oracle data warehouse schemas need careful handling of partitions, bitmap indexes, and materialized view scheduling.
Run the migration report first to confirm the effort estimate before committing to a timeline:
ora2pg -t SHOW_REPORT --estimate_cost --dump_as_html > reports/migration_report.html
If your schema combines patterns from more than one category, or if the migration report flags complexity you do not know how to resolve, get in touch →.
I offer a fixed-fee migration assessment that maps every manual conversion item and gives you a clear effort estimate before the project starts.