When you are performing INSERT/COPY export, ora2pgpro proceeds by chunks of DATA_LIMIT tuples for speed improvement. Tuples are stored in memory before being written to disk, so if you want speed and have enough system resources you can raise this limit to an higher value for example: 100000 or 1000000. A value of 0 means that the chunk will be set to the default: 10000.

When ora2pgpro detects a table with some BLOB, it will automatically reduce the value of this directive by dividing it by 10 until its value is below 1000. You can control this value by setting BLOB_LIMIT. Exporting BLOB use lot of resources, setting it to a too high value can produce OOM.

The ora2pgpro output filename can be changed with this directive. Default value is output.sql. If you set the file name with extension .gz or .bz2, the output will be automatically compressed. This requires that the Compress::Zlib Perl module is installed if the filename extension is .gz and that the bzip2 system command is installed for the .bz2 extension.

You can define a base directory where the file will be written. The directory must exist.

This directive allows you to specify the full path to the bzip2 program if it can not be found in the PATH environment variable.

Allow object constraints to be saved in a separate file during schema export. The file will be named CONSTRAINTS_OUTPUT, where OUTPUT is the value of the corresponding configuration directive. You can use .gz or .bz2 extension to enable compression. Default is to save all data in the OUTPUT file. This directive is usable only with TABLE export type.

The constraints can be imported quickly into Postgres Pro using the LOAD export type to parallelize their creation over multiple (-j or JOBS) connections.

Allow indexes to be saved in a separate file during schema export. The file will be named INDEXES_OUTPUT, where OUTPUT is the value of the corresponding configuration directive. You can use .gz or .bz2 file extension to enable compression. Default is to save all data in the OUTPUT file. This directive is usable only with TABLE AND TABLESPACE export type. With the TABLESPACE export, it is used to write ALTER INDEX ... TABLESPACE ... into a separate file named TBSP_INDEXES_OUTPUT that can be loaded at end of the migration after the indexes creation to move the indexes.

The indexes can be imported quickly into Postgres Pro using the LOAD export type to parallelize their creation over multiple (-j or JOBS) connections.

Allow foreign key declaration to be saved in a separate file during schema export. By default foreign keys are exported into the main output file or in the CONSTRAINT_output.sql file. When enabled, foreign keys will be exported into a file named FKEYS_output.sql.

Allow data export to be saved in one file per table/view. The files will be named as tablename_OUTPUT, where OUTPUT is the value of the corresponding configuration directive. You can still use .gz or .bz2 extension in the OUTPUT directive to enable compression. Default 0 will save all data in one file, set it to 1 to enable this feature. This is usable only during INSERT or COPY export type.

Allow functions, procedures and triggers to be saved in one file per object. The files will be named as objectname_OUTPUT, where OUTPUT is the value of the corresponding configuration directive. You can still use .gz or .bz2 extension in the OUTPUT directive to enable compression. Default 0 will save all in one single file, set it to 1 to enable this feature. This is usable only during the corresponding export type, the package body export has a special behavior.

When export type is PACKAGE, and you enabled this directive, ora2pgpro will create a directory per package, named with the lower-case name of the package, and will create one file per function/procedure into that directory. If the configuration directive is not enabled, it will create one file per package as packagename_OUTPUT, where OUTPUT is the value of the corresponding directive.

If this directive is set to 1, a TRUNCATE TABLE instruction will be added before loading data. This is usable only during INSERT or COPY export type.

When activated, the instruction will be added only if there is no global DELETE clause or not one specific to the current table (see below).

Support for include a DELETE FROM ... WHERE clause filter before importing data and perform a deletion of some lines instead of truncating tables. Value is constructed as follows: TABLE_NAME[DELETE_WHERE_CLAUSE], or if you have only one WHERE clause for all tables just put the DELETE clause as a single value. Both are possible too. Here are some examples:

DELETE  1=1    # Apply to all tables and delete all tuples
DELETE  TABLE_TEST[ID1='001']   # Apply only on table TABLE_TEST
DELETE  TABLE_TEST[ID1='001' OR ID1='002] DATE_CREATE > '2001-01-01' TABLE_INFO[NAME='test']

The last applies two different delete where clause on tables TABLE_TEST and TABLE_INFO and a generic DELETE clause on DATE_CREATE to all other tables. If TRUNCATE_TABLE is enabled, it will be applied to all tables not covered by the DELETE definition. These DELETE clauses might be useful with regular updates.

Set this parameter to 0 to not include the call to \set ON_ERROR_STOP ON in all SQL scripts generated by ora2pgpro. By default this order is always present so that the script will immediately abort when an error is encountered.

Enable this directive to use COPY FREEZE instead of a simple COPY to export data with rows already frozen. This is intended as a performance option for initial data loading. Rows will be frozen only if the table being loaded has been created or truncated in the current sub-transaction. This will only work with export to file and when -J or ORACLE_COPIES is not set or defaults to 1. It can be used with direct import into Postgres Pro under the same condition, but -j or JOBS must also be unset or default to 1.

By default ora2pgpro uses CREATE OR REPLACE in functions and views DDL, if you need not to override existing functions or views disable this configuration directive, DDL will not include OR REPLACE.

To add a DROP OBJECT IF EXISTS before creating the object, enable this directive. Can be useful in an iterative work. Default is disabled.

Postgres Pro does not support Global Temporary Table natively but you can use the pgtt extension to emulate this behavior. Enable this directive to export global temporary table.

Enabling this directive will prevent ora2pgpro to print its header into output files. Only the translated code will be written.

By default ora2pgpro 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.

Number of rows that must be retrieved on both sides for data validation. Default it to compare the 10000 first rows. A value of 0 means to compare all rows.

Order of rows between both sides is different once the data have been modified. In this case data must be ordered using a primary key or a unique index, that means that a table without such objects can not be compared. If the validation is done just after the data migration without any data modification, the validation can be done on all tables without any ordering.

Stop validating data from a table after a certain amount of row mismatch. Default is to stop after 10 rows validation errors.

Use this directive to specify which transformation should be applied to a column when exporting data. Value must be a semicolon-separated list of the following:

TABLE[COLUMN_NAME, code in SELECT target list]

For example, to replace string “Oracle” by “PostgreSQL” in a varchar2 column, use the following:

TRANSFORM_VALUE   ERROR_LOG_SAMPLE[DBMS_TYPE:regexp_replace("DBMS_TYPE",'Oracle','PostgreSQL')]

To replace all Oracle char(0) in a string with a space character:

TRANSFORM_VALUE   CLOB_TABLE[CHARDATA:translate("CHARDATA", chr(0), ' ')]

The expression will be applied in the SQL statement used to extract data from the source database.

Use this directive to set the Postgres Pro data source namespace using DBD::Pg Perl module as follows:

dbi:Pg:dbname=pgdb;host=localhost;port=5432

It will connect to the database pgdb on localhost at TCP port 5432.

Note that this directive is only used for data export, other export needs to be imported manually through the use of psql or any other Postgres Pro client.

To use SSL-encrypted connection, you must add sslmode=require to the connection string like this:

dbi:Pg:dbname=pgdb;host=localhost;port=5432;sslmode=require

These two directives are used to set the login user and password. If you do not supply a credential with PG_PWD, and you have installed the Term::ReadKey Perl module, ora2pgpro will ask for the password interactively. If PG_USER is not set, it will be asked interactively too.

Specifies whether the transaction commit will wait for WAL records to be written to disk before the command returns a success indication to the client. This is the equivalent to setting synchronous_commit directive of the postgresql.conf file. This is only used when you load data directly to Postgres Pro, the default is off to disable the synchronous commit to gain speed at writing data.

This directive can be used to send an initial command to Postgres Pro, just after the connection, for example, to set some session parameters. This directive can be used multiple times.