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: TABLE with constraints, VIEW, MVIEW, TABLESPACE, SEQUENCE, INDEXES, TRIGGER, GRANT, FUNCTION, PROCEDURE, PACKAGE, PARTITION, TYPE, INSERT or COPY, FDW, QUERY, KETTLE .

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 range and list partition.
        - Export a table selection (by specifying the table names).
        - Export Oracle schema to a PostgreSQL 8.4+ schema.
        - Export predefined functions, triggers, procedures, packages and
          package bodies.
        - Export full data 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 some basic automatic conversion of PLSQL code to PLPGSQL.
        - Works on any plateform.
        - 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.
        - Generate XML ktr files to be used with Penthalo Data Integrator (Kettle)
        - Export Oracle locator and spatial geometries into PostGis.

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: 8,938
Estimated Cost to Develop: $ 269,429

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