Here are the different values of the TYPE directive, default is TABLE:

Only one type of export can be performed at the same time so the TYPE directive must be unique. If you have more than one, only the last found in the file will be registered.

Some export type can not or should not be loaded directly into the Postgres Pro database and still require little manual editing. This is the case for GRANT, TABLESPACE, TRIGGER, FUNCTION, PROCEDURE, TYPE, QUERY, and PACKAGE export types, especially if you have PL/SQL code or Oracle-specific SQL in it.

For TABLESPACE, you must ensure that the file path exists in the system and for SYNONYM, ensure that the object's owners and schemas correspond to the new Postgres Pro database design.

Note that you can chain multiple export by giving to the TYPE directive a comma-separated list of export type, but in this case you must not use COPY or INSERT with other export type.

ora2pgpro will convert Oracle partition using table inheritance, trigger, and functions. For more information, see Table Partitioning.

The TYPE export allows export of user-defined Oracle types. If you do not use the --plsql command-line parameter, it dumps Oracle user type as-is, else ora2pgpro will try to convert it to Postgres Pro syntax.

Here is an example of the SHOW_COLUMN output:

[2] TABLE CURRENT_SCHEMA (1 rows) (Warning: 'CURRENT_SCHEMA' is a reserved word in PostgreSQL)
		CONSTRAINT : NUMBER(22) => bigint (Warning: 'CONSTRAINT' is a reserved word in PostgreSQL)
		FREEZE : VARCHAR2(25) => varchar(25) (Warning: 'FREEZE' is a reserved word in PostgreSQL)
	...
	[6] TABLE LOCATIONS (23 rows)
		LOCATION_ID : NUMBER(4) => smallint
		STREET_ADDRESS : VARCHAR2(40) => varchar(40)
		POSTAL_CODE : VARCHAR2(12) => varchar(12)
		CITY : VARCHAR2(30) => varchar(30)
		STATE_PROVINCE : VARCHAR2(25) => varchar(25)
		COUNTRY_ID : CHAR(2) => char(2)

Those extraction keywords are used only to display the requested information and exit. This allows you to quickly know on what you are going to work with.

The SHOW_COLUMN allows another ora2pgpro command-line option: --allow relname or -a relname to limit the displayed information to the given table.

The SHOW_ENCODING export type will display the NLS_LANG and CLIENT_ENCODING values that ora2pgpro will use, and the real encoding of the Oracle database with the corresponding client encoding that could be used with Postgres Pro.

ora2pgpro allows you to export your Oracle table definition to be used with the oracle_fdw foreign data wrapper. By using type FDW, your Oracle tables will be exported as follows:

CREATE FOREIGN TABLE oratab (
		id        integer           NOT NULL,
		text      character varying(30),
		floating  double precision  NOT NULL
	) SERVER oradb OPTIONS (table 'ORATAB');

Now you can use the table like a regular Postgres Pro table.

There is also a more advanced report with migration cost, see the section called “Migration Cost Assessment”.

Activate the migration cost evaluation. Must only be used with SHOW_REPORT, FUNCTION, PROCEDURE, PACKAGE, and QUERY export type. Default is disabled. You may want to use the --estimate_cost command-line option instead to activate this functionality. Note that enabling this directive will force PLSQL_PGSQL activation.

Set the value in minutes of the migration cost evaluation unit. Default is five minutes per unit. See --cost_unit_value to change the unit value in the command line.

By default, when using SHOW_REPORT, the migration report is generated as simple text, enabling this directive will force ora2pgpro to create a report in HTML format.

Use this directive to redefine the number of man-days limit where the migration assessment level must switch from B to C. Default is set to 10 man-days.

This configuration directive adds multiprocess support to COPY, FUNCTION, and PROCEDURE export type, the value is the number of processes to use. By default multiprocess is disabled.

This directive is used to set the number of cores to be used to parallelize data import into Postgres Pro. During FUNCTION or PROCEDURE export type each function will be translated to PL/pgSQL using a new process, the performances gain can be very important when you have many functions to convert.

There is no limitation in parallel processing other than the number of cores and the Postgres Pro I/O performance capabilities.

Doesn't work under Windows Operating System, it is simply disabled.

This configuration directive adds multiprocess support to extract data from Oracle. The value is the number of processes to use to parallelize the select query. By default parallel query is disabled.

The parallelism is built on splitting the query following of the number of cores given as value to ORACLE_COPIES as follows:

SELECT * FROM MYTABLE WHERE ABS(MOD(COLUMN, ORACLE_COPIES)) = CUR_PROC

Here COLUMN is a technical key like a primary or unique key where split will be based and the current core used by the query (CUR_PROC). You can also force the column name to use with the DEFINED_PK configuration directive.

Doesn't work under Windows Operating System, it is simply disabled.

This directive is used to define the technical key to be used to split the query between number of cores set with the ORACLE_COPIES variable. For example:

DEFINED_PK      EMPLOYEES:employee_id

The parallel query that will be used supposing that -J or ORACLE_COPIES is set to 8:

SELECT * FROM EMPLOYEES WHERE ABS(MOD(employee_id, 8)) = N

Here N is the current process forked starting from 0.

This directive is used to define the number of tables that will be processed in parallel for data extraction. The limit is the number of cores on your machine. ora2pgpro will open one database connection for each parallel table extraction. This directive, when more than 1, will invalidate ORACLE_COPIES but not JOBS, so the real number of process that will be used is PARALLEL_TABLES * JOBS.

Note that this directive when set more that 1 will also automatically enable the FILE_PER_TABLE directive if you are exporting to files. This is used to export tables and views in separate files.

Use PARALLEL_TABLES to use parallelism with COPY, INSERT, and TEST_DATA actions. It is also useful with TEST, TEST_COUNT, and SHOW_TABLE if --count_rows is used for real row count.

You can force ora2pgpro to use /*+ PARALLEL(tbname, degree) */ hint in each query used to export data from Oracle by setting a value more than 1 to this directive. A value of 0 or 1 disables the use of the parallel hint. Default is disabled.

This directive is used to set the name of the foreign data server that is used in the CREATE SERVER name FOREIGN DATA WRAPPER oracle_fdw command. This name will then be used in the CREATE FOREIGN TABLE commands and to import data using oracle_fdw. By default no foreign server is defined. This only concerns export types FDW, COPY, and INSERT. For export type FDW, the default value is orcl.

Schema where foreign tables for data migration will be created. If you use several instances of ora2pgpro for data migration through the foreign data wrapper, you might need to change the name of the schema for each instance. Default: ora2pg_fdw_import.

By default ora2pgpro drops the temporary schema ora2pg_fdw_import used to import the Oracle foreign schema before each new import. If you want to preserve the existing schema because of modifications or the use of a third-party server, disable this directive.

This directive, enabled by default, allows to export Oracle external tables as file_fdw foreign tables. To not export these tables at all, set the directive to 0.

Internal timestamps retrieved from custom type are extracted in the following format: 01-JAN-77 12.00.00.000000 AM. It is impossible to know the exact century that must be used, so by default any year below 49 will be added to 2000 and others to 1900. You can use this directive to change the default value 49. This is only relevant if you have a user-defined type with a column timestamp.

Set the comma-separated list of usernames that must be used to filter queries from the DBA_AUDIT_TRAIL table. Default is to not scan this table and to never look for queries. This parameter is used only with SHOW_REPORT and QUERY export types with no input file for queries. Note that queries will be normalized before output unlike when a file is given at input using the -i option.

Disable this directive if you want to disable check_function_bodies.

SET check_function_bodies = false;

It disables validation of the function body string during CREATE FUNCTION. Default is to use the postgresql.conf setting that enables it by default.

Exporting BLOB takes time, in some circumstances you may want to export all data except the BLOB columns. In this case disable this directive and the BLOB columns will not be included into data export. Take care that the target bytea column do not have a NOT NULL constraint.

By default data export order will be done by sorting on table names. If you have huge tables at the end of alphabetic order and you are using multiprocess, it can be better to set the sort order on size so that multiple small tables can be processed before the largest tables finish. In this case set this directive to size. Possible values are name and size. Note that export type SHOW_TABLE and SHOW_COLUMN will use this sort order too, not only COPY or INSERT export type.

This directive allows you to set a list of objects on which the export must be limited, excluding all other objects in the same type of export. The value is a space or comma-separated list of objects names to export. You can include valid regex into the list. For example:

ALLOW           EMPLOYEES SALE_.* COUNTRIES .*_GEOM_SEQ

This will export objects with name EMPLOYEES, COUNTRIES, all objects beginning with SALE_, and all objects with a name ending by _GEOM_SEQ. The object depends on the export type. Note that regex will not work with 8i database, you must use the % placeholder instead, ora2pgpro will use the LIKE operator.

This is the manner to declare global filters that will be used with the current export type. You can also use extended filters that will be applied to specific objects or only on their related export type. For example:

ora2pgpro -p -c ora2pgpro.conf -t TRIGGER -a 'TABLE[employees]'

This will limit the export of triggers to those defined on table employees. If you want to extract all triggers but not some INSTEAD OF triggers:

ora2pgpro -c ora2pgpro.conf -t TRIGGER -e 'VIEW[trg_view_.*]'

Or a more complex form:

ora2pgpro -p -c ora2pgpro.conf -t TABLE -a 'TABLE[EMPLOYEES]' \
        -e 'INDEX[emp_.*];CKEY[emp_salary_min]'

This command will export the definition of the employees table but will exclude all index beginning with emp_ and the CHECK constraint called emp_salary_min.

When exporting partition, you can exclude some partition tables by using the following command:

ora2pgpro -p -c ora2pgpro.conf -t PARTITION -e 'PARTITION[PART_199.* PART_198.*]'

This will exclude partitioned tables for years 1980 to 1999 from the export but not the main partition table. The trigger will also be adapted to exclude those tables.

With GRANT export, you can use this extended form to exclude some users from the export, or limit the export to some others:

ora2pgpro -p -c ora2pgpro.conf -t GRANT -a 'USER1 USER2'
or
ora2pgpro -p -c ora2pgpro.conf -t GRANT -a 'GRANT[USER1 USER2]'

The latter will limit export grants to users USER1 and USER2. But if you do not want to export grants on some functions for these users, for example:

ora2pgpro -p -c ora2pgpro.conf -t GRANT -a 'USER1 USER2' -e 'FUNCTION[adm_.*];PROCEDURE[adm_.*]'

Oracle does not allow the use of look-ahead expression so you may want to exclude some objects that match the ALLOW regexp you have defined. For example, if you want to export all table starting with E but not those starting with EXP, it is not possible to do that in a single expression. This is why you can start a regular expression with the ! character to exclude object matching the regexp given just after. The previous example can be written as follows:

ALLOW   E.* !EXP.*

It will be translated into the following in the object search expression:

REGEXP_LIKE(..., '^E.*$') AND NOT REGEXP_LIKE(..., '^EXP.*$')

This directive is the opposite of the previous one, it allows you to define a space or comma-separated list of object name to exclude from the export. You can include a valid regular expression into the list. For example:

EXCLUDE         EMPLOYEES TMP_.* COUNTRIES

This will exclude objects with names EMPLOYEES, COUNTRIES, and all tables beginning with tmp_.

For example, you can ban from export unwanted functions with this directive:

EXCLUDE         write_to_.* send_mail_.*

This example will exclude all functions, procedures, or functions in a package with the name beginning with those regex. Note that regex will not work with 8i database, you must use the % placeholder instead, ora2pgpro will use the NOT LIKE operator. See above (directive ALLOW) for the extended syntax.

By default ora2pgpro excludes from export some Oracle “garbage” tables that should never be part of an export. This behavior generates a lot of REGEXP_LIKE expressions which are slowing down the export when looking at tables. To disable this behavior, enable this directive, but you will have to exclude or clean up later by yourself the unwanted tables. The regexp used to exclude the table are defined in the array @EXCLUDED_TABLES in lib/Ora2Pgpro.pm. Note that this behavior is independent to the EXCLUDE configuration directive.

Set which view to export as a table. By default none. The value must be a list of view names or regexp separated by space or comma. If the object name is a view and the export type is TABLE, the view will be exported as a CREATE TABLE statement. If export type is COPY or INSERT, the corresponding data will be exported. See Exporting Views as Postgres Pro Tables for more details.

Set which materialized view to export as a table. By default none. Value must be a list of materialized view names or regexp separated by space or comma. If the object name is a materialized view and the export type is TABLE, the view will be exported as a CREATE TABLE statement. If export type is COPY or INSERT, the corresponding data will be exported.

By default ora2pgpro tries to order views to avoid error at the 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.

When exporting grants, you can specify a comma-separated list of objects for which privileges will be exported. Default is export for all objects. Here are the possibles values TABLE, VIEW, MATERIALIZED VIEW, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE BODY, TYPE, SYNONYM, DIRECTORY. Only one object type is allowed at a time. For example, set it to TABLE if you just want to export privileges on tables. You can use the -g option to overwrite it.

This directive allows you to specify a WHERE clause filter when dumping the contents of tables. Value constructs as follows: TABLE_NAME[WHERE_CLAUSE], or if you have only one where clause for each table just put the where clause as the value. Both are possible too. Here are some examples:

# Global where clause applying to all tables included in the export
WHERE  1=1

# Apply the where clause only on table TABLE_NAME
WHERE  TABLE_NAME[ID1='001']

# Applies two different clause on tables TABLE_NAME and OTHER_TABLE
# and a generic where clause on DATE_CREATE to all other tables
WHERE  TABLE_NAME[ID1='001' OR ID1='002] DATE_CREATE > '2001-01-01' OTHER_TABLE[NAME='test']

Any where clause not included into a table name bracket clause will be applied to all exported table including the tables defined in the WHERE clause. These WHERE clauses are very useful if you want to archive some data or to the opposite only export some recent data.

To be able to test data import quickly, it is useful to limit data export to the first thousand tuples of each table. For Oracle, define the following clause:

WHERE   ROWNUM < 1000

This can also be restricted to some tables data export.

The command-line option -W or --where will override this directive for the global part and per table if the table names are the same.

This directive is used to limit the number of items shown in the top N lists like the top list of tables per number of rows and the top list of largest tables in megabytes. By default it is set to 10 items.

Enable this directive if you want to continue direct data import on error. When ora2pgpro receives an error in the COPY or INSERT statement from Postgres Pro, it will log the statement to a file called TABLENAME_error.log in the output directory and continue to the next bulk of data. Like this you can try to fix the statement and manually reload the error log file. Default is disabled: abort import on error.

Sometimes you may want to extract data from an Oracle table but you need a custom query for that. Not just a SELECT * FROM table like ora2pgpro does but a more complex query. This directive allows you to overwrite the query used by ora2pgpro to extract data. The format is TABLENAME[SQL_QUERY]. If you have multiple table to extract by replacing the ora2pgpro query, you can define multiple REPLACE_QUERY lines.

REPLACE_QUERY   EMPLOYEES[SELECT e.id,e.fisrtname,lastname FROM EMPLOYEES e JOIN EMP_UPDT u ON (e.id=u.id AND u.cdate>'2014-08-01 00:00:00')]

Force ora2pgpro to translate Oracle text indexes into Postgres Pro indexes using the pg_trgm extension. Default is to translate CONTEXT indexes into FTS indexes and CTXCAT indexes using pg_trgm. Most of the time using pg_trgm is enough, this is what this directive is for. You need to create the pg_trgm extension into the destination database before importing the objects.

By default, ora2pgpro creates a function-based index to translate Oracle text indexes.

CREATE INDEX ON t_document
  USING gin(to_tsvector('pg_catalog.french', title));

You will have to rewrite the CONTAIN() clause using to_tsvector(), for example:

SELECT id,title FROM t_document
  WHERE to_tsvector(title) @@ to_tsquery('search_word');

To force ora2pgpro to create an extra tsvector column with dedicated triggers for FTS indexes, disable this directive. In this case, ora2pgpro will add the column as follows:

ALTER TABLE t_document ADD COLUMN tsv_title tsvector;

Then update the column to compute FTS vectors if the data have been loaded before UPDATE t_document SET tsv_title = to_tsvector('pg_catalog.french', coalesce(title,''));. To automatically update the column when a modification in the title column appears, ora2pgpro adds the following trigger:

CREATE FUNCTION tsv_t_document_title() RETURNS trigger AS $$
BEGIN
       IF TG_OP = 'INSERT' OR new.title != old.title THEN
               new.tsv_title :=
               to_tsvector('pg_catalog.french', coalesce(new.title,''));
       END IF;
       return new;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER trig_tsv_t_document_title BEFORE INSERT OR UPDATE
 ON t_document
 FOR EACH ROW EXECUTE PROCEDURE tsv_t_document_title();

When the Oracle text index is defined over multiple columns, ora2pgpro will use setweight() to set a weight in the order of the column declaration.

Use this directive to force text search configuration to use. When it is not set, ora2pgpro will autodetect the stemmer used by Oracle for each index and pg_catalog.english if the information is not found.

If you want to perform your text search in an accent-insensitive way, enable this directive. ora2pgpro will create a helper function over unaccent() and the pg_trgm indexes using this function. With FTS, ora2pgpro will redefine your text search configuration, for example:

CREATE TEXT SEARCH CONFIGURATION fr (COPY = french);
ALTER TEXT SEARCH CONFIGURATION fr
        ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem;

Then set the FTS_CONFIG ora2pgpro.conf directive to fr instead of pg_catalog.english.

When enabled, ora2pgpro will create the wrapper function:

CREATE OR REPLACE FUNCTION unaccent_immutable(text)
RETURNS text AS
$$
    SELECT public.unaccent('public.unaccent', $1);
$$ LANGUAGE sql IMMUTABLE
   COST 1;

The indexes are exported as follows:

CREATE INDEX t_document_title_unaccent_trgm_idx ON t_document
    USING gin (unaccent_immutable(title) gin_trgm_ops);

In your queries, you will need to use the same function in the search to be able to use the function-based index. Example:

SELECT * FROM t_document
        WHERE unaccent_immutable(title) LIKE '%donnees%';

Same as above but call lower() in the unaccent_immutable() function:

CREATE OR REPLACE FUNCTION unaccent_immutable(text)
RETURNS text AS
$$
    SELECT lower(public.unaccent('public.unaccent', $1));
$$ LANGUAGE sql IMMUTABLE;

Enable this directive to reorder columns and minimize the footprint on disc, so that more rows fit on a data page, which is the most important factor for speed. Default is disabled, which means that the same order as in Oracle tables definition is used, that should be enough for most usages. This directive is only used with TABLE export.

This directive allows you to limit the columns extracted for a given table. The value consists of a space-separated list of table names with a set of columns in parenthesis as follows: MODIFY_STRUCT NOM_TABLE(nomcol1,nomcol2,...) .... For example:

MODIFY_STRUCT   T_TEST1(id,dossier) T_TEST2(id,fichier)

This will only extract the columns id and dossier from the table T_TEST1 and columns id and fichier from the table T_TEST2. This directive can only be used with TABLE, COPY, or INSERT export. With TABLE export, the CREATE TABLE DDL statement will respect the new list of columns, and all indexes or foreign keys pointing to or from a column removed will not be exported.

Instead of redefining the table structure with MODIFY_STRUCT, you may want to exclude some columns from the table export. The value consists of a space-separated list of table names with a set of columns in parenthesis as follows: EXCLUDE_COLUMNS NOM_TABLE(nomcol1,nomcol2,...) .... For example:

EXCLUDE_COLUMNS T_TEST1(id,dossier) T_TEST2(id,fichier)

This will exclude from the export the columns id and dossier from the table T_TEST1 and columns id and fichier from the table T_TEST2. This directive can only be used with TABLE, COPY, or INSERT export. With TABLE export, the CREATE TABLE DDL statement will respect the new list of columns, and all indexes or foreign key pointing to or from a column removed will not be exported.

This directive allows you to remap a list of Oracle table names to Postgres Pro table names during export. The value is a list of space-separated values with the following structure:

REPLACE_TABLES  ORIG_TBNAME1:DEST_TBNAME1 ORIG_TBNAME2:DEST_TBNAME2

Oracle tables ORIG_TBNAME1 and ORIG_TBNAME2 will be respectively renamed to DEST_TBNAME1 and DEST_TBNAME2.

Like table names, the names of the columns can be remapped to different ones using the following syntax: REPLACE_COLS ORIG_TBNAME(ORIG_COLNAME1:NEW_COLNAME1,ORIG_COLNAME2:NEW_COLNAME2). For example:

REPLACE_COLS    T_TEST(dico:dictionary,dossier:folder)

This renames the Oracle columns dico and dossier from table T_TEST to dictionary and folder.

If you want to change the type of some Oracle columns to Postgres Pro boolean during the export, you can define here a list of tables and columns separated by space as follows:

REPLACE_AS_BOOLEAN     TB_NAME1:COL_NAME1 TB_NAME1:COL_NAME2 TB_NAME2:COL_NAME2

The values set in the boolean columns list will be replaced with the t and f following the default replacement values and those additionally set in directive BOOLEAN_VALUES.

Note that if you have modified the table name with REPLACE_TABLES and/or the column name, you need to use the name of the original table and/or column.

REPLACE_COLS            TB_NAME1(OLD_COL_NAME1:NEW_COL_NAME1)
REPLACE_AS_BOOLEAN      TB_NAME1:OLD_COL_NAME1

You can also give a type and precision to convert all fields of that type as a boolean automatically. For example:

REPLACE_AS_BOOLEAN      NUMBER:1 CHAR:1 TB_NAME1:COL_NAME1 TB_NAME1:COL_NAME2

This will also replace any field of type number(1) or char(1) as a boolean in all exported tables.

Use this to add additional definition of the possible boolean values used in Oracle fields. You must set a space-separated list of TRUE:FALSE values. By default here are the values recognized by ora2pgpro:

BOOLEAN_VALUES          yes:no y:n 1:0 true:false enabled:disabled

Any values defined here will be added to the default list.

When ora2pgpro finds a “zero” date 0000-00-00 00:00:00, it is replaced by NULL. This could be a problem if your column is defined with the NOT NULL constraint. If you can not remove the constraint, use this directive to set an arbitral date that will be used instead. You can also use -INFINITY if you do not want to use a fake date.

Add the given value as suffix to index names. Useful, if you have indexes with same name as tables. For example:

INDEXES_SUFFIX          _idx

This will add _idx at the end of all index names. Not so common but can help.

Enable this directive to rename all indexes in the following format: tablename_columns_names. Could be very useful for databases that have the same index name multiple times or that use the same name as a table, which is not allowed by Postgres Pro. Disabled by default.

Operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the corresponding types. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern-matching expressions (LIKE or POSIX regular expressions) when the database does not use the standard C locale. If you enable, with value 1, this will force ora2pgpro to export all indexes defined on varchar2() and char() columns using those operators. If you set it to a value greater than 1, it will only change indexes on columns where the character limit is greater or equal than this value. For example, set it to 128 to create these kind of indexes on columns of type varchar2(N) where N >= 128.

Enable this directive if you want that your partition tables will be renamed. Disabled by default. If you have multiple partitioned tables, when exported to Postgres Pro some partitions could have the same name but different parent tables. This is not allowed, a table name must be unique, in this case enable this directive. The following rules are applied by default:

If you do not want to reproduce the partitioning like in Oracle and want to export all partitioned Oracle data into the main single table in Postgres Pro, enable this directive. ora2pgpro will export all data into the main table. Default is to use partitioning, ora2pgpro will export data from each partition and import them into the Postgres Pro dedicated partition table.

By default ora2pgpro exports Oracle tables with the NOLOGGING attribute as UNLOGGED tables. You may want to fully disable this feature because you will lose all data from unlogged tables in case of a Postgres Pro crash. Set it to 1 to export all tables as normal tables.