Ora2Pg v16.1 released!, November 30 2015

This release fixes several issues and adds some very useful features:

    * Generate automatically a new shell script when using option
      --init_project to help automate all import into PostgreSQL.

	See sh -? for more information.

    * Export Oracle bitmap index as PostgreSQL btree_gin index. This require the
      btree_gin extension and PostgreSQL >= 9.4. This is the default.

    * Auto set DEFINED_PK to the first column of a table that have a unique key
      defined that is a NUMBER. This allow data of any table with a numeric
      unique key to be extracted using multiple connexions to Oracle using -J
      option. Tables with no numeric unique key will be exported with a single

    * Improve BLOB export speed by using hex encoding instead of escape. This
      might speed up be BLOB export by 10.

    * Allow use of LOB locator to retrieve BLOB and CLOB data to prevent having
      to set LONGREADLEN. Now LONGREADLEN is set to 8KB. Old behavior using
      LONGREADLEN can still be enabled by setting NO_LOB_LOCATOR to 0, given
      for backward compatibility. Default is to use LOB locator.

    * Ora2Pg will also auto detect table with BLOB and automatically decrease
      DATA_LIMIT to a value lower or equal to 1000. This is to prevent OOM.

    * Improving indexes and constraints creation speed by using the LOAD action
      and a file containing SQL orders to perform. It is possible to dispatch
      those orders over multiple PostgreSQL connections. To be able to use this
      feature, PG_DSN, PG_USER and PG_PWD must be set. Then:

	ora2pg -t LOAD -c config/ora2pg.conf -i schema/tables/INDEXES_table.sql -j 4
      will dispatch indexes creation over 4 simultaneous PostgreSQL connections.
      This will considerably accelerate this part of the migration process with
      huge data size.

    * Domain indexes are now exported as b-tree but commented to let you know
      where possible FTS are required.

    * Add number of refresh ON COMMIT materialized view in detailed report.

    * Allow redefinition of numeric type, ex: NUMBER(3)::bigint to fix wrong
      original definition in Oracle.

    * Allow export of all schemas from an Oracle Instance when SCHEMA directive
      is empty and EXPORT_SCHEMA is enabled. All exported objects will be
      prefixed with the name of their original Oracle schema or search_path will
      be set to that schema name. Thanks to Magnus Hagander for the feature
    * Allow use of COPY FREEZE to export data when COPY_FREEZE is enabled. This
      will only works with export to file and when -J or ORACLE_COPIES is not
      set or default to 1. It can be used with direct import into PostgreSQL
      under the same condition but -j or JOBS must be unset or default to 1.
      Thanks to Magnus Hagander for the feature request.

Some new configuration directives:

    * BITMAP_AS_GIN: enable it to use btree_gin extension to create bitmap
      like index with pg >= 9.4. You will need to create the extension by
      yourself: "create extension btree_gin;". Default is to create GIN index,
      when disabled, a btree index will be created.
    * NO_LOB_LOCATOR: to disable use of LOB locator and extract BLOB "inline"
      using a less or more high value in LONGREADLEN.
    * BLOB_LIMIT: to force the value of DATA_LIMIT for tables with BLOB. Default
      is to automatically set this limit using the following code:
    * COPY_FREEZE: use it to use COPY FREEZE instead of simple COPY to speedup
      import into PostgreSQL.

See Release note for full list of changes.

Ora2Pg v16.0 is out!, October 15 2015

This major release improve PL/SQL code replacement, fixes several bugs and adds some major new features:

  * Full migration of MySQL database, it just work like with Oracle database.
  * Full migration assessment report for MySQL database.
  * New script, ora2pg_scanner, to perform a migration assessment of all Oracle and MySQL instances on a network.
  * Add technical difficulty level in migration assessment.
  * Allow migration assessment on client queries extracted from AUDIT_TRAIL (oracle) or general_log table (mysql).
  * Ora2Pg has a "made in one night" brand new Web site (still need some work)

Example of technical difficulty level assessment output for the sakila database with some more difficulties:

	Total	83.90 cost migration units means approximatively 1 man-day(s).
	Migration level: B-5

Here are the explanation of the migration level code:

    Migration levels:
	A - Migration that might be run automatically
	B - Migration with code rewrite and a human-days cost up to 10 days
	C - Migration with code rewrite and a human-days cost above 10 days
    Technical levels:
	1 = trivial: no stored functions and no triggers
	2 = easy: no stored functions but with triggers, no manual rewriting
	3 = simple: stored functions and/or triggers, no manual rewriting
	4 = manual: no stored functions but with triggers or views with code rewriting
	5 = difficult: stored functions and/or triggers with code rewriting

This is to help you to find the database that can be migrated first with small efforts (A and B) and those who need to conduct a full migration project (C).

This release has also some new useful features:

  * Export type SHOW_TABLE now shows additional information about table type (FOREIGN, EXTERNAL or PARTITIONED with the number of partition).
  * Connection's user and password can be passed through environment variables ORA2PG_USER and ORA2PG_PASSWD to avoid setting them at ora2pg command line.
  * Improve PL/SQL replacement on ADD_MONTH(), ADD_YEAR(), TRUNC(), INSTR() and remove the replacement limitation on DECODE().
  * Add detection of migration difficulties in views, it was previously reserved to functions, procedures, packages and triggers.
  * Replace values in auto generated configuration file from command line options -s, -n, -u and -p when --init_project is used.
  * Adjust lot of scores following new functionalities in Ora2Pg, ex: dblink or synomyms are now easy to migrate.

There is some new command line options to ora2pg script:

  * -m | --mysql : to be used with --init_project and -i option to inform ora2pg that we work with a MySQL format
  * -T | --temp_dir : option to be able to set a distinct temporary directory to run ora2pg in parallel.
  * --audit_user : option to set the user used in audit filter and enable migration assessment report on queries from AUDIT_TRAIL (oracle) or general_log table (mysql).
  * --dump_as_sheet and --print_header options to be able to compute a CSV file with all migration assessment from a list of oracle database.
  * --dump_as_csv option to report assessments into a csv file. It will not include comments or details, just objects names, numbers and cost.

Backward compatibility:

  - Change NULL_EQUAL_EMPTY to be disabled by default to force change in the application instead of transforming the PL/SQL.

This release adds some new configuration directives:

  * MYSQL_PIPES_AS_CONCAT: Enable it if double pipe and double ampersand (|| and &&) should not be taken as equivalent to OR and AND.
  * MYSQL_INTERNAL_EXTRACT_FORMAT: Enable it if you want EXTRACT() replacement to use the internal format returned as an integer.
  * AUDIT_USER: Set the comma separated list of user name that must be used to filter from the DBA_AUDIT_TRAIL or general_log tables.
  * REPLACE_ZERO_DATE: "zero" date: 0000-00-00 00:00:00 it is replaced by a NULL by default, use it to use the date of your choice.
  * INDEXES_RENAMING: force renaming of all indexes using tablename_columnsnames. Very useful for database that have multiple time the same index name or that use the same name than a table.
  * HUMAN_DAYS_LIMIT: default to 5 days, used to set the number of human-days limit for migration of type C.

See Release note for full list of changes.

Paris, September 24 2015

Video of a presentation at PgSessions #7, Conference "Oracle : comment s'en passer?" in french.

Version 15.3 released, Monday June 01 2015

This is a maintenance release only that fixes several minor bugs and typos. The configuration file have been entirely rewritten to classify configuration directives in section for better understanding.

See Release note for full list of changes.

Version 15.2 released, Monday April 13 2015

This new minor release fixes some issues and adds two new configuration directives:

  • ORA_INITIAL_COMMAND to be able to execute a custom command just after the connection to Oracle,
    for example to unlock a security policy.
  • INTERNAL_DATE_MAX to change the behavior of Ora2Pg with internal date found in user defined types.

This version will also automatically re-order exported views taking into account interdependencies.

See Release note for full list of changes.

Moscow, Saturday Febuary 7 2015

Slides of a presentation at PgConf.RU, Conference "Oracle to PostgreSQL migration: a hard way?" in english.

Version 15.1 released, Friday Febuary 6 2015

New minor release just to fix two annoying bugs in previous release.

    - Fix replacement of function name which include SELECT in their name by
      PERFORM. Thanks to Frederic Bamiere for the report.
    - Fix creation of sources subdirectories when initializing a new migration project.

Version 15.0 released, Wednesday Febuary 4 2015

This major release improve PL/SQL code replacement, fixes several bugs and adds some new useful features:

    - Add support to the PostgreSQL external_file extension to mimic BFILE
      type from Oracle. See for
      more information.
    - Allow export of Oracle's DIRECTORY as external_file extension objects
      This will also try to export read/write privilege on those directories.
    - Allow export of Oracle's DATABASE LINK as Oracle foreign data wrapper
      server using oracle_fdw.
    - Allow function with PRAGMA AUTONOMOUS_TRANSACTION to be exported through
      a dblink wrapper to achieve the autonomous transaction.
    - Allow export of Oracle's SYNONYMS as views. Views can use foreign table
      to create "synonym" on object of a remote database.
    - Add trimming of data when DATA_TYPE is used to convert CHAR(n) Oracle
      column into varchar(n) or text. Default is to trim both side any space
      character. This behavior can be controlled using two new configuration
      directives TRIM_TYPE and TRIM_CHAR.
    - Add auto detection of geometry constraint type and dimensions through
      spatial index parameters. This avoid the overhead of sequential scan
      of the geometric column.
    - Add support to export Oracle sub partition and create sub partition
      for PostgreSQL with the corresponding trigger.
    - ALLOW and EXCLUDE directives are now able to apply filter on the object
      type. Backward compatibility can not be fully preserved, older definition
      will apply to current export type only, this could change your export in
      some conditions. See documentation update for more explanation.
    - Add PACKAGE_AS_SCHEMA directive to change default behavior that use a
      schema to emulate Oracle package function call. When disable, all calls
      to package_name.function_name() will be turn into package_name_function_name()
      just like a function call in current schema.
    - Add FKEY_OPTIONS to force foreign keys options. List of supported options
    - Add rewriting of internal functions in package body, those functions will
      be prefixed by the package name. Thanks to Dominique Legendre for the
      feature request.

Some change can break backward compatibility and make configuration directives obsolete:

    - The ALLOW_PARTITION configuration directive has been removed. With new
      extended filters in ALLOW/EXCLUDE directive, this one is obsolete.
      Backward compatibility is preserved but may be removed in the future.
    - ALLOW and EXCLUDE directives do not works as previously. Backward
      compatibility may be preserved with some export type but may be broken
      in most of them. See documentation.
    - It is recommended now to leave the NLS_LANG and CLIENT_ENCODING commented
      to let Ora2Pg handle automatically the encoding. Those directives may be
      removed in the future.

See Release note for full list of changes.