Export Type

Export type

The export action is performed following a single configuration directive 'TYPE', some others add more control over what should be exported.

• TYPE

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

- TABLE: Extract all tables with indexes, primary keys, unique keys,
  foreign keys and check constraints.
- VIEW: Extract only views.
- GRANT: Extract roles converted to Pg groups, users and grants on all
  objects.
- SEQUENCE: Extract all sequences and their last positions.
- TABLESPACE: Extract storage spaces for tables and indexes (Pg >= v8).
- TRIGGER: Extract triggers defined on actions.
- FUNCTION: Extract functions.
- PROCEDURE: Extract procedures.
- PACKAGE: Extract packages and package bodies.
- INSERT: Extract data as INSERT statements.
- COPY: Extract data as COPY statements.
- PARTITION: Extract range and list Oracle partitions with subpartitions.
- TYPE: Extract user defined Oracle types.
- FDW: Export Oracle tables as foreign tables for Oracle, MySQL and SQL Server FDW.
- MVIEW: Export materialized views.
- QUERY: Try to automatically convert Oracle SQL queries.
- KETTLE: Generate XML ktr template files for use by Kettle.
- DBLINK: Generate Oracle foreign data wrapper server to use as dblink.
- SYNONYM: Export Oracle's synonyms as views on other schema's objects.
- DIRECTORY: Export Oracle's directories as external_file extension objects.
- LOAD: Dispatch a list of queries over multiple PostgreSQL connections.
- TEST: Perform a diff between Oracle and PostgreSQL databases.
- TEST_COUNT: Perform a row count diff between Oracle and PostgreSQL tables.
- TEST_VIEW: Perform a row count diff between Oracle and PostgreSQL views.
- TEST_DATA: Perform data validation check on rows on both sides.
- SEQUENCE_VALUES: Export DDL to set the last values of sequences

Only one type of export can be performed at a 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 types cannot or should not be loaded directly into the PostgreSQL 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 them.

For TABLESPACE you must ensure that file paths exist on the system and for SYNONYM you may need to ensure that the object's owners and schemas correspond to the new PostgreSQL database design.

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

Ora2Pg will convert Oracle partitions using table inheritance, triggers and functions. See documentation at PostgreSQL: http://www.postgresql.org/docs/current/interactive/ddl-partitioning.html

The TYPE export allows export of user defined Oracle types. If you don't use the --plsql command line parameter it simply dumps Oracle user type as-is else Ora2Pg will try to convert it to PostgreSQL syntax.

The KETTLE export type requires that the Oracle and PostgreSQL DNS are defined.

Since Ora2Pg v8.1 there are three new export types:

SHOW_VERSION : display Oracle version
SHOW_SCHEMA  : display the list of schemas available in the database.
SHOW_TABLE   : display the list of tables available.
SHOW_COLUMN  : display the list of tables columns available and the
        Ora2PG conversion type from Oracle to PostgreSQL that will be
        applied. It will also warn you if there are PostgreSQL reserved
        words in Oracle object names.

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)

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

The SHOW_COLUMN allows another ora2pg 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 Ora2Pg will use and the real encoding of the Oracle database with the corresponding client encoding that could be used with PostgreSQL.

Ora2Pg allows you to export your Oracle, MySQL or MSSQL table definitions to be used with the oracle_fdw, mysql_fdw or tds_fdw foreign data wrapper. By using type FDW your 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 PostgreSQL table.

Release 10 adds a new export type designed to evaluate the content of the database to migrate, in terms of objects and cost to complete the migration:

SHOW_REPORT  : show a detailed report of the Oracle database content.

Here is a sample report: http://ora2pg.darold.net/report.html

There is also a more advanced report with migration cost. See the dedicated chapter about Migration Cost Evaluation.

• ESTIMATE_COST

  Activate the migration cost evaluation. Must only be used with SHOW_REPORT, FUNCTION, PROCEDURE, PACKAGE and QUERY export types. 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.

• COST_UNIT_VALUE

  Sets 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 at command line.

• DUMP_AS_HTML

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

  See http://ora2pg.darold.net/report.html for a sample report.

• DUMP_AS_JSON

  By default when using SHOW_REPORT the migration report is generated as simple text. Enabling this directive will force ora2pg to create a report in JSON format.

  See http://ora2pg.darold.net/report.html for a sample report.

• DUMP_AS_CSV

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

  See http://ora2pg.darold.net/report.html for a sample report.

• DUMP_AS_FILE_PREFIX

  By default when using SHOW_REPORT the migration report is generated to stout. Enabling this directive in conjunction with DUMP_AS_* directives will force ora2pg to create a report files with the given extensions and formats. This option allows you to combine multiple DUMP_AS_* formats.

  See http://ora2pg.darold.net/report.html for a sample report.

• HUMAN_DAYS_LIMIT

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

• JOBS

  This configuration directive adds multiprocess support to COPY, FUNCTION and PROCEDURE export types. The value is the number of processes to use. Default is to disable multiprocessing.

  This directive is used to set the number of cores to use to parallelize data import into PostgreSQL. During FUNCTION or PROCEDURE export type each function will be translated to plpgsql using a new process. The performance gain can be very important when you have tons of functions to convert.

  There's no limitation in parallel processing other than the number of cores and the PostgreSQL I/O performance capabilities.

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

• ORACLE_COPIES

  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. Default is parallel query disabled.

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

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

where 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 using the DEFINED_PK configuration directive.

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

• DEFINED_PK

  This directive is used to define the technical key to use 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

where N is the current process forked starting from 0.

• PARALLEL_TABLES

  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. Ora2Pg will open one database connection for each parallel table extraction. This directive, when higher than 1, will invalidate ORACLE_COPIES but not JOBS, so the real number of processes that will be used is PARALLEL_TABLES * JOBS.

  Note that this directive when set higher than 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.

• DEFAULT_PARALLELISM_DEGREE

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

• FDW_SERVER

  This directive is used to set the name of the foreign data server that is used in the "CREATE SERVER name FOREIGN DATA WRAPPER <fdw_extension> ..." command. This name will then be used in the "CREATE FOREIGN TABLE ..." SQL 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.

• FDW_IMPORT_SCHEMA

  Schema where foreign tables for data migration will be created. If you use several instances of ora2pg 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

• ORACLE_FDW_PREFETCH

  The default behavior of Ora2Pg is to NOT set the "prefetch" option for oracle_fdw when used for COPY and INSERT. This directive allows the prefetch to be set. See oracle_fdw documentation for the current default.

• ORACLE_FDW_COPY_MODE

  When using Ora2Pg COPY with oracle_fdw, it is possible to use two different modes: 1) "local", which uses psql on the host running Ora2Pg for the "TO" binary stream; 2) "server", which uses PostgreSQL server-side COPY for the "TO" binary stream. Both modes use psql for the "FROM STDIN BINARY". However, "local" runs the psql "FROM STDIN BINARY" on the host Ora2Pg is run from, whereas "server" runs the psql "FROM STDIN BINARY" on the PostgreSQL server. "local" mode should work on any PostgreSQL-based system, including managed offerings, which are not expected to support use of "server" mode due to permissions. The default is "local" as this is compatible with more configurations.

• ORACLE_FDW_COPY_FORMAT

  When using Ora2Pg COPY with oracle_fdw, it is possible to use either BINARY or CSV data format. BINARY provides better performance, however, requires exact data type matching between the FDW and destination table. CSV provides greater flexibility with respect to data type matching: if the FDW and destination data types are functionally-compatible, the columns can be copied. The default is "binary".

• DROP_FOREIGN_SCHEMA

  By default, Ora2Pg 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.

• EXTERNAL_TO_FDW

  This directive, enabled by default, allows exporting Oracle's External Tables as file_fdw foreign tables. To not export these tables at all, set the directive to 0.

• INTERNAL_DATE_MAX

  Internal timestamps retrieved from custom types 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 timestamp column.

• AUDIT_USER

  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 type 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 or INPUT directive.

• FUNCTION_CHECK

  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, which enables it by default.

• ENABLE_BLOB_EXPORT

  Exporting BLOBs 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 does not have a NOT NULL constraint.

• ENABLE_CLOB_EXPORT

  Same behavior as ENABLE_BLOB_EXPORT but for CLOB.

• DATA_EXPORT_ORDER

  By default, data export order will be done by sorting on table name. 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 types SHOW_TABLE and SHOW_COLUMN will use this sort order too, not only COPY or INSERT export type. If you want to give your custom export order, just give a filename as value that contains the ordered list tables to export. Must be a list of one table per line, in uppercase for Oracle.