For TABLE export, you may not want to export all schema constraints, the SKIP configuration directive allows you to specify a space or comma-separated list of constraints that should not be exported. Possible values are:

SKIP    indexes,checks

For example, this will remove indexes and check constraints from export.

Enable this directive if you want to add primary key definition inside the CREATE TABLE statement. If disabled (the default), primary key definition will be added with an ALTER TABLE statement.

By default, names of the primary and unique keys in the source Oracle database are ignored, and key names are autogenerated in the target Postgres Pro database with the Postgres Pro internal default naming rules. If you want to preserve Oracle primary and unique key names, set this option to 1.

This directive allows you to add an ON UPDATE CASCADE option to a foreign key when ON DELETE CASCADE is defined or always. Oracle does not support this feature, you have to use a trigger to operate the ON UPDATE CASCADE. As Postgres Pro has this feature, you can choose how to add the foreign key option. This directive has the following values:

When exporting tables, ora2pgpro normally exports constraints as they are, if they are non-deferrable, they are exported as non-deferrable. However, non-deferrable constraints will probably cause problems when attempting to import data to Postgres Pro. The FKEY_DEFERRABLE option set to 1 will cause all foreign key constraints to be exported as deferrable.

In addition to exporting data when the DEFER_FKEY option is set to 1, it will add a command to defer all foreign key constraints during data export, and the import will be done in a single transaction. This will work only if foreign keys have been exported as deferrable and you are not using direct import to Postgres Pro (PG_DSN is not defined). Constraints will then be checked at the end of the transaction.

This directive can also be enabled if you want to force all foreign keys to be created as deferrable and initially deferred during schema export (TABLE export type).

If deferring foreign keys is not possible due to the amount of data in a single transaction, you have not exported foreign keys as deferrable or you are using direct import to Postgres Pro, you can use the DROP_FKEY directive. It will drop all foreign keys before all data import and recreate them at the end of the import.

This directive allows you to gain lot of speed improvement during data import by removing all indexes that are not an automatic index (indexes of primary keys) and recreate them at the end of data import. It is far better to not import indexes and constraints before having imported all data.

This directive is used to disable triggers on all tables in COPY or INSERT export modes. Available values are USER (disable user-defined triggers only) and ALL (includes RI system triggers). Default is 0: do not add SQL statements to disable trigger before data import.

If you want to disable triggers during data migration, set the value to USER if you are connected as non-superuser, and ALL if you are connected as Postgres Pro superuser. A value of 1 is equal to USER.

If set to 1, it disables alter of sequences on all tables during COPY or INSERT export mode. This is used to prevent the update of sequence during data migration. Default is 0, alter sequences.

By default all data that are not of type date or time are escaped. If you experience any problem with that you can set it to 1 to disable character escaping during data export. This directive is only used during a COPY export. See STANDARD_CONFORMING_STRINGS for enabling/disabling escape with INSERT statements.

This controls whether ordinary string literals ('...') treat backslashes literally, as specified in SQL standard. This is on by default, causing ora2pgpro to use the escape string syntax (E'...') if this parameter is not set to 0. This is the exact behavior of the same option in Postgres Pro. This directive is only used during data export to build INSERT statements. See NOESCAPE for enabling/disabling escape in COPY statements.

If you want to convert CHAR(n) from Oracle into varchar(n) or text in Postgres Pro using directive DATA_TYPE, you might want to do some trimming on the data. By default, ora2pgpro will auto-detect this conversion and remove any whitespace at both leading and trailing position. If you just want to remove the leadings character set the value to LEADING. If you just want to remove the trailing character, set the value to TRAILING. Default value is BOTH.

The default trimming character is space, use this directive if you need to change the character that will be removed. For example, set it to - if you have a leading - in the char(n) field. To use space as trimming charger, comment this directive, this is the default value.

If you want to preserve the case of Oracle object names, set this directive to 1. By default, ora2pgpro will convert all Oracle object names to lower case. It is not recommended to enable this, unless you will always have to double-quote object names on all your SQL scripts.

Allow escaping of column name using Oracle reserved words. Value is a list of comma-separated reserved words. Default: audit,comment,references.

Enable this directive if you have table or column names that are a reserved word for Postgres Pro. ora2pgpro will double quote the name of the object.

Set this directive to 1 to replace default password by a random password for all extracted user during a GRANT export.

In Postgres Pro, materialized views are supported with the SQL syntax CREATE MATERIALIZED VIEW. To force ora2pgpro to use the native Postgres Pro support you must enable this configuration - enable by default. If you want to use the old style with table and a set of function, you should disable it.

Set the Postgres Pro major version number of the target database. For example: 11 or 16. Default is the current major version at the time of a new release.

Use btree_gin extension to create bitmap like indexes. You will need to create the extension. Default is to create GIN index, when disabled, a B-tree index will be created.

Use this directive to set how the database handles the LongReadLen attribute to a value that will be larger than the expected size of the LOBs. The default is 1MB which may not be enough to extract BLOBs or CLOBs. If the size of the LOB exceeds the LONGREADLEN DBD::Oracle will return a “ORA-24345: A Truncation” error. Default: 1023*1024 bytes.

If you want to bypass the “ORA-24345: A Truncation” error, set this directive to 1, it will truncate the data extracted to the LONGREADLEN value. Disabled by default so that you will be warned if your LONGREADLEN value is not high enough.

Disable this if you want to load full contents of BLOB and CLOB and not use LOB locators. In this case you will have to set LONGREADLEN to the right value. Note that this will not improve speed of BLOB export as most of the time is always consumed by the bytea escaping and in this case export is done line-by-line and not by chunk of DATA_LIMIT rows. Default is enabled, it uses LOB locators.

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 shown that the best performances can be reach with higher value like 512K or 4Mb.

A quick benchmark with 30120 rows with different size of BLOB (200x5Mb, 19800x212k, 10000x942K, 100x17Mb, 20x156Mb), with DATA_LIMIT=100, LONGREADLEN=170Mb and a total table size of 20GB gives:

no lob locator  : 22m46,218s (1365 sec., avg: 22 recs/sec)
chunk size 8k   : 15m50,886s (951 sec., avg: 31 recs/sec)
chunk size 512k : 1m28,161s (88 sec., avg: 342 recs/sec)
chunk size 4Mb  : 1m23,717s (83 sec., avg: 362 recs/sec)

In conclusion it can be more than 10 time faster with LOB_CHUNK_SIZE set to 4Mb. Depending on the size of most BLOB you may want to adjust the value here. For example, if you have a majority of small lobs bellow 8K, using 8192 is better to not waste space. Default value for LOB_CHUNK_SIZE is 512000.

Force the use getStringVal() instead of getClobVal() for XML data export. Default is 1, enabled for backward compatibility. Set it to 0 to use extract method as CLOB. Note that XML value extracted with getStringVal() must not exceed VARCHAR2 size limit (4000), otherwise it will return an error.

Set it to O if you want to disable export of millisecond from Oracle timestamp columns. By default milliseconds are exported with the use of the following format:

'YYYY-MM-DD HH24:MI:SS.FF'

Disabling will force the use of the following Oracle format:

to_char(..., 'YYYY-MM-DD HH24:MI:SS')

By default milliseconds are exported.

Set this to 1 if you do not want to export comment associated to tables and columns definition. Default is enabled.