Ora2Pg

Ora2Pg is an open source, multiplatform and freely distributed command-line program implemented in Perl and designed from the ground up to be used for easily exporting an Oracle database schema to a PostgreSQL compatible schema.

Features at a glance

Key features include support for exporting full database schema, support for exporting grants and privileges for users and groups, support for exporting a table selection, as well as support for exporting Oracle schema to a PostgreSQL 7.3+ schema.

The project is also capable of connecting to any Oracle database, supports extracting of its internal structure and generates an SQL script that you can load into your PostgreSQL database.

Additionally, the software is capable of exporting range and list Oracle partitions, export predefined triggers, functions, procedures, packages, as well as package bodies. Various command-line options are available to users, which can be viewed at a glance by running the ‘ora2pg --help’ command in a terminal emulator.

Getting started with Ora2Pg

To install and use the Ora2Pg software on your GNU/Linux computer, you must download the latest version of the program from Softoware or via its official website (see link at the end of the article), save the archive somewhere on your PC and unpack it using an archive manager utility.

Then, open a Terminal application, navigate to the location of the extracted archive files (e.g. cd /home/softoware/ora2pg-14.1), run the ‘perl Makefile.PL’ command to configure the project, followed by the ‘make’ command to compile it.

Install Ora2Pg system wide by executing the ‘make install’ command as root or the ‘sudo make install’ command as a privileged user. Use it by running the ‘ora2pg’ command in a terminal emulator.

Ora2Pg is supported on any operating system where Perl is available, including GNU/Linux, Microsoft Windows and Mac OS X.

What is new in this release:

• Remove ALLOW_CODE_BREAK, it is no more useful.
• Change output of SHOW_ENCODING to reflect change to default encoding.
• Comment ALLOW_PARTITION in default configuration file.
• Add QUERY and KETTLE export type in configuration file comments.

What is new in version 12.1:

• Fix example given for the WHERE configuration directive. Thanks to Bob Treumann for the report.
• Add INDEXES_SUFFIX configuration option to allow append a suffix to indexes names.
• Replace special charater ^M by \r as they are not supported by git.
• Fix IF EXISTS in alter table of sub _drop_foreign_keys. Thanks to Francis Corriveau for the patch.
• Fix isolation level when exporting data. Thanks to Ludovic Penet for the report.
• Fix regression when ora2pg tries to create foreign keys on tables or to tables that are not selected for export. Thanks to Ludovic Penet.
• Add information about backslashed comma into directive MODIFY_TYPE into Makefile.PL.
• Add missing MODIFY_TYPE definition in documentation.
• Allow backslashed comma into MODIFY_TYPE type redefinition. Example: TABLE1:COL3:decimal(9\,6),TABLE1:COL4:decimal(9\,6). Thanks to Mike Kienenberger for the report
• Fix missing single cote into create_materialized_view() call. Thanks to Jacky Rigoreau for the patch.
• Fix some typo in documentation, thanks to Mike Kienenberger for the report.
• Add a chapter about installing DBD::Oracle into documentation. Thanks to Raghavendra for the patch.
• Fix case sensitivity on external table name with FDW export type. Thanks to Guillaume Lelarge for the report.
• Fix export of materialized views when PG_SUPPORTS_MVIEW is disabled. Thanks to Christian Bjornbak for the report.
• Update copyright.

What is new in version 12.0:

• This version fixes many issues and adds three new features.
• Using the REORDERING_COLUMNS directive, you will be able to reorder columns to minimized the footprint on disk so that more rows fit on a data page.
• The PG_SUPPORTS_MVIEW allow the export of materialized view with native PostgreSQL 9.3 syntax.
• The USE_TABLESPACE variable can be used to export objects using their original tablespace.

What is new in version 11.4:

• Fix other major issues in migration cost assessment.
• Redefine some migration cost values to be more precise.

What is new in version 11.3:

• Add full details about PL/SQL evaluation by ora2pg when --estimate_cost or ESTIMATE_COST is enable. This will display cost units per keywords detected in the function/package code.
• Fix wrong cost unit assessment on PL/SQL code size, this bug generated very high migration cost assessment for functions/packages with lot of lines. Please run your tests again, estimated times can be up to tree time lower on huge code.
• Remove comments before code evalution.
• Fix file input parser for PL/SQL packages export when IS or AS was in the next line than the CREATE PACKAGE BODY ...
• Exclude NOT NULL constraint from the count of CHECK constraints into the TABLE report.
• Fix decimal precision in table migration assessment cost.
• Fix typo in changelog.

What is new in version 11.2:

• Update doc about Windows multiprocess issues and acknowledgements.
• Fix Windows OS issues using multiprocessing options by disabling multiprocess support on this plateform. When -J or -j will be used a warning will be displayed and Ora2Pg will simply run single process like in previous 10.x versions. Thanks to Jean Marc Yao Adingra for the report.
• Fix RAW and LONG RAW export to ByteA. Thanks to Prabhat Tripathi for the report and testing.
• Fix patch regression on multiple TRUNCATE call for a single table. Thanks to David Greco for the report.
• Placed calls to DB handle InactiveDestroy outside the forked process to prevent fatal errors on Windows. Thanks to Jean Marc Adingra for the report.
• Forked running processes are renamed into more readable name like "ora2pg logger" for the progress bar, "ora2pg
• querying Oracle" when used with -J option and "ora2pg
• sending to PostgreSQL" to better know what is the current job of the process.
• Removed the use of /Y flag in Windows install script, this was causing error "dmake: Error code 130, while making install_all". Thanks to Jean-Marc Adingra for the report.
• Fix direct import to PostgreSQL that was just producing nothing. Thank to David Greco for the patch.
• Fix ora2pg usage documentation.
• Add an underscore to CLIENT ENCODING in SHOW_ENCODING output to be the same as the configuration directive.

What is new in version 11.1:

• Adjust cost assessment for indexes, tables and tables partition.
• Add comment to report of index partition about local index only.
• Fix position of TRUNCATE TABLE in output file.
• Fix export of data from RAW or LONG RAW columns, they was exported as hex string. Now data are converted using utl_raw.cast_to_varchar2() function before being escaped for insert into a bytea. Thanks to Alex Delianis for the report.
• Fix issue with Oracle TIMESTAMP(0) data export that add a single ending point, ex: "2008-08-09 00:00:00.", this ending character is now removed by format_data_type(). Thanks to Pierre-Marie Petit for the report.
• Fix typo on MODIFY_STRUCT description.
• Force DEBUG to off in default configuration file.
• Change range PARTITION operators in the check conditions, >= and < replaced by > and

What is new in version 11.0:

• Add documentation about JOBS, ORACLE_COPIES, DEFINED_PK configuration directive and informations about KETTLE export type.
• Add KETTLE export type to generate XML transformation file definition for Penthatlo Data Integrator (Kettle). Thanks to Marc Cousin for the work. Example of use: ora2pg -c ora2pg.conf -t KETTLE -j 12 -J 4 -o loaddata.sh
• Fix major bug in export of auto generated named constraint. Thanks to mrojasaquino fot the report.
• Show number of rows in the top largest tables.
• Add TOP_MAX description to the documentation.
• Add the TOP_MAX directive to default configuration file and update documentation. Directive used to control the top N tables to show.
• Add top N of largest tables in SHOW_TABLE, SHOW_COLUMN and SHOW_REPORT export type.
• Fix progressbar output when ora2pg is interrupted by ctrl+c.
• Add JOBS, ORACLE_COPIES and DEFINED_PK directives to configuration file. JOBS replacing THREAD_COUNT but backward compatibility is preserved.
• Add 3 new command line options, -j | --jobs and -J | --copies, used to set the number of connection to PostgreSQL and Oracle for parallel processing. The third, -L | --limit is used to change DATA_LIMIT at command line.
• Add multiprocess support on data export. With the help of Thomas Ogrisegg.
• Add more schema in SYSUSERS that should not be exported.
• Add full detailed information about SYNONYM in SHOW_REPORT.
• Add MODIFY_TYPE configuration directive to allow some table/column type to be changed on PostgreSQL side during the export.
• Fix objects type count in progressbar of SHOW_REPORT.
• Restrict table and index in SHOW_REPORT to the tables defined in ALLOW and EXCLUDE directives.
• Show total number of rows in SHOW_TABLE and SHOW_REPORT output.
• Add top 10 of tables sorted by number of rows in SHOW_TABLE and SHOW_REPORT output.
• Fix typo in SYNONYM objects.
• Add report of top ten tables ordered y number of rows.
• Rewrite most of the Oracle schema storage information extraction for speed improvement.
• Use Hash to store column informations.
• Fix %unique_keys declaration in _table() method.
• Remove call to _table_info() from SHOW_REPORT code as those informations are already loaded with the _table() method.
• Fix missing column definition on TABLE export.
• Add progress bar during output generation following export type.
• Add STOP_ON_ERROR configuration directive to enable/disable the call to ON_ERROR_STOP into generated SQL scripts. Thanks to Ludovic Penet for the feature request.
• Huge speed improvement on columns informations retrieving.
• Fix progress bar to keep the total number of tables related to the ALLOW or EXCLUDE configuration directives. Thanks to Ludovic Penet for the report.
• Change return type of function _table_info(), it now returns data instead of the database handle.
• Improve speed on indexes and constraints extraction for database with huge number of tables.
• Improve performance to retrieve columns information and comments.
• Remove report of column details during export in debug mode, use SHOW_COLUMN instead.
• Remove call to upper() in objects owner condition to improve performance with database with huge number of objects.
• Add a fix to not export foreign key for exclude tables. Thanks to Ludovic Penet for the report.
• Fix Windows install issue with copying ora2pg.conf.dist. Thanks to Dominique FOURDRINOY for the report.
• Increase the cost of Oracle function not converted to PG automatically.

What is new in version 10.1:

• Fix global where clause that should not be overwritten. Thanks to Dan Harbin for the patch.
• Fix bug/typo in boolean replacement, where a colon was used instead of a single quote. Thanks to Alex Delianis for the patch.
• Update copyright.
• Add detection of additional Oracle functions for better migration cost assessment.
• Update documentation.
• Force report detail in lowercase.
• Added information about the migration cost value to the reports.
• Add --dump_as_html command line option and DUMP_AS_HTML configuration directive.
• Allow migration report to be generated as HTML.
• Separate report generation code from data collection code