Features
Complete list of Ora2Pg features for Oracle to PostgreSQL migration
FEATURES
Ora2Pg consists 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 optionally 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, SYNONYM.
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/list partitions and sub partitions.
- 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 platform.
- Export Oracle tables as foreign data wrapper tables.
- Export materialized view.
- Show a report of an Oracle database content.
- Migration cost assessment of an Oracle database.
- Migration difficulty level 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 Pentaho Data Integrator (Kettle)
- Export Oracle locator and spatial geometries into PostGIS.
- Export DBLINK as Oracle FDW.
- Export SYNONYMS as views.
- Export DIRECTORY as external table or directory for external_file extension.
- Dispatch a list of SQL orders over multiple PostgreSQL connections
- Perform a diff between Oracle and PostgreSQL database for test purposes.
- MySQL/MariaDB and Microsoft SQL Server migration.Ora2Pg does its best to automatically convert your Oracle database to PostgreSQL but there's still manual work 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 recommendations on porting Oracle PL/SQL code to PostgreSQL PL/PGSQL at "Converting from other Databases to PostgreSQL", section: Oracle (http://wiki.postgresql.org/wiki/Main\_Page).
See http://ora2pg.darold.net/report.html for an HTML sample of an Oracle database migration report.
