How it works

Ora2Pg consist of a Perl script (ora2pg) and a Perl module (Ora2Pg.pm), the only thing you have to modify is the configuration file ora2pg.conf by setting the DSN to the Oracle database and optionaly the name of a schema. Once that's done you just have to set the type of export you want, here are the capabilities: TABLE with constraints, VIEW, TABLESPACE, SEQUENCE, INDEXES, TRIGGER, GRANT, FUNCTION, PROCEDURE, PACKAGE, PARTITON and DATA.

By default Ora2Pg exports to a file that you can load into PostgreSQL with the psql client, but you can also import directly into a PostgreSQL database by setting its DSN into the configuration file. With all configuration options of ora2pg.conf you have full control of what should be exported and how.

Features included: 

    - Export full database schema (tables, views, sequences, indexes), with unique, primary, foreign key and check constraints.
    - Export grants/privileges for users and groups.
    - Export a table selection (by specifying the table names).
    - Export Oracle schema to a PostgreSQL 7.3+ schema.
    - Export predefined functions, triggers, procedures, packages and package bodies.
    - Export range and list Oracle partition.
    - Export full datas or following a WHERE clause.
    - Full support of Oracle BLOB object as PG BYTEA.
    - Export Oracle views as PG tables.
    - Export Oracle user defined types.
    - Provide basic help for converting PLSQL code to PLPGSQL (still needs manual work).
    - Export Oracle tables as foreign data wrapper tables.
    - Export materialized view.
    - Show a detailled report of an Oracle database content.
    - Migration cost assessment of an Oracle database.
    - Migration cost assessment of PL/SQL code from a file.
    - Migration cost assessment of Oracle SQL queries stored in a file.
    - Works on any plateform.

Ora2Pg do its best to automatically convert your Oracle database to PostgreSQL but there's still manual works to do. The Oracle specific PL/SQL code generated for functions, procedures, packages and triggers has to be reviewed to match the PostgreSQL syntax. You will find some useful recommandations on porting Oracle PL/SQL code to PostgreSQL PL/PGSQL at Converting from other Databases to PostgreSQL, section: Oracle.

You can see a sample of an Oracle database migration cost assessment report made by Ora2Pg here.

Some links

Slides of the talk I've given at PgConf.Eu 2011 in Amsterdam, conference "Ora2Pg best practices" in french and a translation in english

In this video from Us PG Day 2011, CEO of Consistent State, Kevin Kempter, talks to us about migrating from Oracle to PostgreSQL with the new ora2pg. Kevin will start by giving us an overview of ora2pg, he will go through the install, and we will spend some time with the config file, finally he will show us some examples of ora2pg being used.

An english presentation talking about Ora2Pg at PgCon 2010 in Ottawa: Oracle to PostgreSQL migration, great thanks to Gurjeet Singh from EnterpriseDB

A french presentation of Ora2Pg at the Linux Solution 2005 in Paris: LinuxSolution2005-Ora2pg.pdf, great thanks to Jean-Paul Argudo from Dalibo.

Ora2Pg Developement

Source Lines of Code: 6,526
Estimated Cost to Develop: $ 193,652

(Generated using David A. Wheeler's 'SLOCCount'.)