01What it does
What is Ora2Pg, and what does it not do?
Ora2Pg reads the Oracle data dictionary and writes SQL files for PostgreSQL: tables, indexes, constraints, sequences, views, partitions, grants, and a best-effort conversion of triggers, functions, procedures and packages. It can also copy data, either as files or straight into PostgreSQL.
It does not convert your application code, and it does not promise that every PL/SQL block converts cleanly. Treat its output as a first draft that an engineer reviews. The wider plan is on the Oracle to PostgreSQL migration page.
02Step by step
Install Ora2Pg and run your first command
- 01
Install Perl and the two database drivers, DBD::Oracle (needs Oracle Instant Client) and DBD::Pg.
- 02
Download Ora2Pg from its project page or GitHub. Build it with `perl Makefile.PL`, then `make && sudo make install`.
- 03
Create a project folder: `ora2pg --init_project my_migration`. It generates a directory tree and a starter `ora2pg.conf`.
- 04
Edit the config (next section), then check the connection: `ora2pg -t SHOW_VERSION -c ora2pg.conf`.
- 05
Run the assessment report before any export. The command is in the section after the config.
03Configuration
The ora2pg.conf directives that matter first
The default file has hundreds of options. These are the ones you set on day one.
| Directive | What it sets | Example |
|---|---|---|
| ORACLE_HOME | Path to Oracle client libraries | /usr/lib/oracle/client |
| ORACLE_DSN | How to reach the Oracle database | dbi:Oracle:host=db1;sid=ORCL;port=1521 |
| ORACLE_USER / ORACLE_PWD | Credentials for the read-only account | migration_ro |
| SCHEMA | Which Oracle schema to export | SALES |
| TYPE | What to export (see next section) | TABLE |
| PG_VERSION | Target PostgreSQL version, so the output uses supported syntax | 16 |
| EXPORT_SCHEMA | Keep the schema name in the output | 1 |
| PG_DSN / PG_USER / PG_PWD | Target database, for direct data load | dbi:Pg:dbname=sales;host=pg1 |
04Assess first
How do you get the migration assessment report?
Run ora2pg -t SHOW_REPORT --estimate_cost -c ora2pg.conf --dump_as_html > report.html. Ora2Pg scans every object and lists, per object type, how many it found, how many it can convert and how hard the rest are.
The cost estimate rates each object on a difficulty level and converts the total to person-days using a cost unit (the default is 5 minutes per unit, adjustable with --cost_unit_value). Ora2Pg also assigns the whole database a letter grade. A means mostly automatic. B means some code rewrite. C means a large rewrite.
Take the day count as a rough order of magnitude. It counts conversion work, not testing, data validation, performance tuning, cutover or application changes. Those usually cost more.
05Export types
Which TYPE values do you export, and in what order?
Set TYPE in the config or pass it with -t. Run one type at a time, review the output, and load in this order.
TABLE,SEQUENCE,TYPEfor the structure.COPYorINSERTfor data. COPY is much faster. Use-o file.sqlto write to a file.VIEW,PARTITIONandGRANTafter the data.FUNCTION,PROCEDURE,PACKAGE,TRIGGERfor PL/SQL. Expect manual fixes here.TESTandTEST_COUNTto compare object and row counts between source and target.
06Limits
Where does Ora2Pg stop, especially on PL/SQL?
Simple PL/SQL converts to PL/pgSQL well. Trouble starts with Oracle-specific features. The usual list:
- Packages with state: PostgreSQL has no packages, so Ora2Pg splits them into schema functions and package variables need a workaround.
- Autonomous transactions,
DBMS_*packages andUTL_FILE: these need extensions or a rewrite. - Hierarchical queries with
CONNECT BY: converted to recursive queries, but check results. - Oracle-specific data types, empty-string-as-NULL behavior and
ROWNUMsemantics can change results without raising an error. - Compatibility extensions such as orafce close some gaps, but they are a layer you now own.
07Alternatives
Ora2Pg or AWS SCT and DMS?
The Oracle to PostgreSQL migration tools you will hear about most are Ora2Pg, AWS Schema Conversion Tool (SCT) with AWS Database Migration Service (DMS), and EDB's migration tooling. They overlap more than they compete.
| Tool | Strength | Watch for |
|---|---|---|
| Ora2Pg | Free, scriptable, runs anywhere, full control of the config | You review and fix the output yourself |
| AWS SCT + DMS | SCT converts schema with an assessment; DMS replicates data with low downtime | Targets AWS databases; ongoing replication has its own cost and limits |
| EDB tooling | Oracle compatibility layer in EDB Postgres Advanced Server | Ties you to a commercial Postgres distribution |
08How Ace8 helps
How Ace8 helps after the report
Ace8 is the AI-assisted application modernization practice of AceMQ. We run the assessment, use AI-assisted tools to draft fixes for the PL/SQL the tool could not convert, and have engineers review and test every object. Ace8 is not affiliated with AWS.
Start with the modernization assessment, which includes a database scan, or read the full plan on Oracle to PostgreSQL migration.