Export Control

Taking export under control

The following other configuration directives interact directly with the export process and give you fine granularity in database export control.

• SKIP

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

- fkeys: turn off foreign key constraints
- pkeys: turn off primary keys
- ukeys: turn off unique column constraints
- indexes: turn off all other index types
- checks: turn off check constraints

For example:

SKIP    indexes,checks

will remove indexes and check constraints from export.

• PKEY_IN_CREATE

  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. Enable it if you are exporting to GreenPlum PostgreSQL database.

• KEEP_PKEY_NAMES

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

• FKEY_ADD_UPDATE

  This directive allows you to add an ON UPDATE CASCADE option to a foreign key when an ON DELETE CASCADE is defined or always. Oracle does not support this feature, you have to use triggers to operate the ON UPDATE CASCADE. As PostgreSQL has this feature, you can choose how to add the foreign key option. There are three values to this directive: never, the default that means that foreign keys will be declared exactly like in Oracle. The second value is delete, that mean that the ON UPDATE CASCADE option will be added only if the ON DELETE CASCADE is already defined on the foreign Keys. The last value, always, will force all foreign keys to be defined using the update option.

• FKEY_DEFERRABLE

  When exporting tables, Ora2Pg 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 Pg. The FKEY_DEFERRABLE option set to 1 will cause all foreign key constraints to be exported as deferrable.

• DEFER_FKEY

  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 PostgreSQL (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).

• DROP_FKEY

  If deferring foreign keys is not possible due to the amount of data in a single transaction, you've not exported foreign keys as deferrable or you are using direct import to PostgreSQL, 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.

• DROP_INDEXES

  This directive allows you to gain a lot of speed 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. Of course it is far better to not import indexes and constraints before having imported all data.

• DISABLE_TRIGGERS

  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 triggers before data import.

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

• DISABLE_SEQUENCE

  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 sequences during data migration. Default is 0, alter sequences.

• NOESCAPE

  By default, all data that are not of type date or time are escaped. If you experience any problems 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.

• STANDARD_CONFORMING_STRINGS

  This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard. This was the default before Ora2Pg v8.5 so that all strings were escaped first; now this is currently on, causing Ora2Pg 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 PostgreSQL. This directive is only used during data export to build INSERT statements. See NOESCAPE for enabling/disabling escape in COPY statements.

• TRIM_TYPE

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

• TRIM_CHAR

  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 leading - in the char(n) field. To use space as trimming character, comment this directive, this is the default value.

• PRESERVE_CASE

  If you want to preserve the case of Oracle object names, set this directive to 1. By default, Ora2Pg will convert all Oracle object names to lower case. I do not recommend enabling this unless you will always have to double-quote object names in all your SQL scripts.

• ORA_RESERVED_WORDS

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

• USE_RESERVED_WORDS

  Enable this directive if you have table or column names that are reserved words for PostgreSQL. Ora2Pg will double quote the name of the object.

• GEN_USER_PWD

  Set this directive to 1 to replace default passwords with random passwords for all extracted users during a GRANT export.

• PG_SUPPORTS_MVIEW

  Since PostgreSQL 9.3, materialized views are supported with the SQL syntax 'CREATE MATERIALIZED VIEW'. To force Ora2Pg to use the native PostgreSQL support, you must enable this configuration - enabled by default. If you want to use the old style with table and a set of functions, you should disable it.

• PG_SUPPORTS_IFEXISTS

  PostgreSQL versions below 9.x do not support IF EXISTS in DDL statements. Disabling the directive with value 0 will prevent Ora2Pg to from adding those keywords in all generated statements. Default value is 1, enabled.

• PG_VERSION

  Set the PostgreSQL major version number of the target database. Ex: 9.6 or 13. Default is current major version at time of a new release. This replaces the old and deprecated PG_SUPPORTS_* configuration directives described below.

• PG_SUPPORTS_ROLE (Deprecated)

  This option is deprecated since Ora2Pg release v7.3.

  By default Oracle roles are translated into PostgreSQL groups. If you have PostgreSQL 8.1 or higher, consider the use of ROLES and set this directive to 1 to export roles.

• PG_SUPPORTS_INOUT (Deprecated)

  This option is deprecated since Ora2Pg release v7.3.

  If set to 0, all IN, OUT or INOUT parameters will not be used in the generated PostgreSQL function declarations (disable it for PostgreSQL database versions lower than 8.1). This is now enabled by default.

• PG_SUPPORTS_DEFAULT

  This directive enables or disables the use of default parameter values in function exports. Prior to PostgreSQL 8.4, such default values were not supported. This feature is now enabled by default.

• PG_SUPPORTS_WHEN (Deprecated)

  Adds support for WHEN clauses on triggers as PostgreSQL v9.0 now supports them. This directive is enabled by default; set it to 0 to disable this feature.

• PG_SUPPORTS_INSTEADOF (Deprecated)

  Adds support for INSTEAD OF usage on triggers (used with PG >= 9.1). If this directive is disabled, the INSTEAD OF triggers will be rewritten as Pg rules.

• PG_SUPPORTS_CHECKOPTION

  When enabled, exports views with CHECK OPTION. Disable it if you have a PostgreSQL version prior to 9.4. Default: 1, enabled.

• PG_SUPPORTS_IFEXISTS

  If disabled, do not export objects with IF EXISTS statements. Enabled by default.

• PG_SUPPORTS_PARTITION

  PostgreSQL versions prior to 10.0 do not have native partitioning. Enable this directive if you want to use declarative partitioning. Enabled by default.

• PG_SUPPORTS_SUBSTR

  Some versions of PostgreSQL like Redshift don't support substr() and need to be replaced by a call to substring(). In this case, disable it.

• PG_SUPPORTS_NAMED_OPERATOR

  Disable this directive if you are using PG < 9.5. PL/SQL operators used in named parameters => will be replaced by PostgreSQL's proprietary operator := Enabled by default.

• PG_SUPPORTS_IDENTITY

  Enable this directive if you have PostgreSQL >= 10 to use IDENTITY columns instead of serial or bigserial data types. If PG_SUPPORTS_IDENTITY is disabled and there is an IDENTITY column in the Oracle table, they are exported as serial or bigserial columns. When it is enabled they are exported as IDENTITY columns like:

CREATE TABLE identity_test_tab (
        id bigint GENERATED ALWAYS AS IDENTITY,
        description varchar(30)
) ;

If there are non-default sequence options set in Oracle, they will be appended after the IDENTITY keyword. Additionally in both cases, Ora2Pg will create a file AUTOINCREMENT_output.sql with an embedded function to update the associated sequences with the restart value set to "SELECT max(colname)+1 FROM tablename". Of course this file must be imported after data import otherwise sequence will be kept at start value. Enabled by default.

• PG_SUPPORTS_PROCEDURE

  PostgreSQL v11 adds support for PROCEDURE, enable it if you use such version.

• BITMAP_AS_GIN

  Use btree_gin extension to create bitmap-like index with pg >= 9.4. You will need to create the extension yourself:

create extension btree\_gin;

Default is to create GIN index, when disabled, a btree index will be created.

• PG_BACKGROUND

  Use pg_background extension to create an autonomous transaction instead of using a dblink wrapper. With pg >= 9.5 only. Default is to use dblink. See https://github.com/vibhorkum/pg\_background about this extension.

• DBLINK_CONN

  By default if you have an autonomous transaction translated using dblink extension instead of pg_background, the connection is defined using the values set with PG_DSN, PG_USER and PG_PWD. If you want to fully override the connection string, use this directive to set the connection in the autonomous transaction wrapper function. For example:

DBLINK_CONN    port=5432 dbname=pgdb host=localhost user=pguser password=pgpass

• LONGREADLEN

  Use this directive to set the database handle's '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 an 'ORA-24345: A Truncation' error. Default: 1023*1024 bytes.

  Take a look at this page to learn more: http://search.cpan.org/~pythian/DBD-Oracle-1.22/Oracle.pm#Data\_Interface\_for\_Persistent\_LOBs

  Important note: If you increase the value of this directive take care that DATA_LIMIT will probably need to be reduced. Even if you only have a 1MB blob, trying to read 10000 of them (the default DATA_LIMIT) all at once will require 10GB of memory. You may extract data from those tables separately and set a DATA_LIMIT to 500 or lower, otherwise you may experience some out of memory issues.

• LONGTRUNKOK

  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.

• USE_LOB_LOCATOR

  Disable this if you want to load the full content 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 the 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. For more information on how it works, see http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm#Data\_Interface\_for\_LOB\_Locators

  Default is enabled; it uses LOB locators.

• LOB_CHUNK_SIZE

  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 have shown that the best performance can be reached with higher values 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 times faster with LOB_CHUNK_SIZE set to 4Mb. Depending on the size of most BLOBs, you may want to adjust the value here. For example, if you have a majority of small lobs below 8K, using 8192 is better to not waste space. Default value for LOB_CHUNK_SIZE is 512000.

• XML_PRETTY

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

• ENABLE_MICROSECOND

  Set it to 0 if you want to disable export of milliseconds from Oracle timestamp columns. By default, milliseconds are exported by using 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.

• DISABLE_COMMENT

  Set this to 1 if you don't want to export comments associated with tables and columns definition. Default is enabled.