Ora2Pg v21.0 has been released

Grenoble, France - October 12th, 2020

Ora2Pg 21.0 released

Version 21.0 of Ora2Pg, a free and reliable tool used to migrate an Oracle database to PostgreSQL, has been officially released and is publicly available for download.

This release fix several issues reported since last release and adds several new features and improvements.

New features and improvements

• Add clause OVERRIDING SYSTEM VALUE to INSERT statements when the table has an IDENTITY column.
• Considerably increase the speed to generate the report about the migration assessment, especially for database with huge number of objects.
• Reduce time passed in the progress bar. Following the number of database objects we were spending too much time in refreshing the progress bar.
• Add number of identity columns in migration assessment report.
• Make assessment details report initially hidden using HTML5 tags details+summary
• Improve speed of BLOB/CLOB data export. Oracle recommends reading from and writing to a LOB in batches using a multiple of the LOB chunk size. This chunk size defaults to 8k (8192). Recent tests show that the best performances can be reach with higher value like 512K or 4Mb.
• Add progress bar when --oracle_speed is use in single process mode.
• Automatically activate USER_GRANTS when the connection user has no DBA privilege. A warning is displayed.
• Complete port to Windows by using the Windows separator on stdout redirection into a file at ora2pg command line call and improve ora2pg_scanner port on Windows OS.
• Add rewrite of MySQL JOIN with WHERE clause instead of ON.
• Add MGDSYS (Oracle E-Business Suite) and APEX_040000 to the list of schemas excluded from the export.
• Supply credentials interactively when a password is not defined in the configuration file. Need the installation of a new Perl module Term::ReadKey.
• Add supports oracle connections "as sysdba" with username "/" and an empty password to connect to a local oracle instance.
• Add translation of PRIVATE TEMPORARY TABLE from Oracle 18c into PostgreSQL basic temporary table, only the default behavior for on commit change.

New command line options

• Add new command line option to ora2pg_scanner: -b | --binpath DIR to set the full path to directory where the ora2pg binary stays. Might be useful only on Windows OS.
• Add -r | --relative command line option and PSQL_RELATIVE_PATH configuration directive. By default Ora2Pg use \i psql command to execute generated SQL files if you want to use a relative path following the script execution file enabling this option will use \ir. See psql help for more information.

New configuration directives

• NO_VIEW_ORDERING by default Ora2Pg try to order views to avoid error at import time with nested views. With a huge number of views this can take a very long time, you can bypass this ordering by enabling this directive.
• NO_FUNCTION_METADATA: force Ora2Pg to not look for function declaration. This will prevent Ora2Pg to rewrite function replacement call if needed. Do not enable it unless looking forward at function breaks other export.
• LOB_CHUNK_SIZE: see explanation in the new features and improvement list about BLOB/CLOB export speed improvement.
• ALTERNATIVE_QUOTING_REGEXP: to support the Alternative Quoting Mechanism ('Q' or 'q') for String Literals set the regexp with the text capture to use to extract the text part. The value of this directive can be a list of regexp separated by a semi colon. The capture part is mandatory in each regexp if you want to restore the string constant.

Backward compatibility changes

• Default for NO_LOB_LOCATOR is now 1 to benefit from LOB_CHUNK_SIZE performances gain.
• Enable schema compilation (COMPILE_SCHEMA set to 1) by default to speed up DDL extraction.
• Change the behavior of Ora2Pg with the parameters that follows a parameter with a default value. Ora2Pg used to change the order of the parameters function to put all parameters with a default value at end of the list which need a function call rewrite. This have been abandoned, any parameter without default value after a parameter with a default value will now be appended with DEFAULT NULL.

For a complete list of change see https://github.com/darold/ora2pg/blob/master/changelog

Thanks to all contributors, they are all cited in the changelog file.

Useful Links

• Website: http://www.ora2pg.com/
• Download1: https://github.com/darold/ora2pg/releases
• Download2: http://sourceforge.net/projects/ora2pg/
• Development: https://github.com/darold/ora2pg
• Changelog: https://github.com/darold/ora2pg/blob/master/changelog

• Documentation: http://www.ora2pg.com/documentation.html

• Global Temporary Tables for PostgreSQL extension: https://github.com/darold/pgtt

• BFILE for PostgreSQL extension: https://github.com/darold/external_file

About Ora2Pg

Ora2Pg is an easy and reliable tool to migrate from Oracle to PostgreSQL. It is developed since 2001 by Gilles Darold and can export most of the Oracle objects (table, view, materialized view, tablespace, sequence, indexes, trigger, grant, function, procedure, package, partition, data, blob, external table, etc.).

Ora2Pg works on any platform and is available under the GPL v3 license.

Docs, Download & Support at http://www.ora2pg.com/