NAME
 Ora2Pg - Oracle to PostgreSQL database schema converter
DESCRIPTION
 Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL
 compatible schema. It connects to your Oracle database, scans it
 automatically and extracts its structure or data, then generates SQL
 scripts that you can load into your PostgreSQL database.
 Ora2Pg can be used for anything from reverse engineering an Oracle
 database to huge enterprise database migration or simply replicating
 some Oracle data into a PostgreSQL database. It is really easy to use
 and doesn't require any Oracle database knowledge other than providing
 the parameters needed to connect to the Oracle database.
FEATURES
 Ora2Pg consists of a Perl script (ora2pg) and a Perl module (Ora2Pg.pm).
 The only thing you have to modify is the configuration file ora2pg.conf
 by setting the DSN to the Oracle database and optionally the name of a
 schema. Once that's done, you just have to set the type of export you
 want: TABLE with constraints, VIEW, MVIEW, TABLESPACE, SEQUENCE,
 INDEXES, TRIGGER, GRANT, FUNCTION, PROCEDURE, PACKAGE, PARTITION, TYPE,
 INSERT or COPY, FDW, QUERY, KETTLE, SYNONYM.
 By default, Ora2Pg exports to a file that you can load into PostgreSQL
 with the psql client, but you can also import directly into a PostgreSQL
 database by setting its DSN into the configuration file. With all
 configuration options of ora2pg.conf, you have full control of what
 should be exported and how.
 Features included:
 - Export full database schema (tables, views, sequences, indexes), with
 unique, primary, foreign key and check constraints.
 - Export grants/privileges for users and groups.
 - Export range/list partitions and sub partitions.
 - Export a table selection (by specifying the table names).
 - Export Oracle schema to a PostgreSQL 8.4+ schema.
 - Export predefined functions, triggers, procedures, packages and
 package bodies.
 - Export full data or following a WHERE clause.
 - Full support of Oracle BLOB object as PG BYTEA.
 - Export Oracle views as PG tables.
 - Export Oracle user defined types.
 - Provide some basic automatic conversion of PLSQL code to PLPGSQL.
 - Works on any platform.
 - Export Oracle tables as foreign data wrapper tables.
 - Export materialized view.
 - Show a report of an Oracle database content.
 - Migration cost assessment of an Oracle database.
 - Migration difficulty level assessment of an Oracle database.
 - Migration cost assessment of PL/SQL code from a file.
 - Migration cost assessment of Oracle SQL queries stored in a file.
 - Generate XML ktr files to be used with Pentaho Data Integrator (Kettle)
 - Export Oracle locator and spatial geometries into PostGIS.
 - Export DBLINK as Oracle FDW.
 - Export SYNONYMS as views.
 - Export DIRECTORY as external table or directory for external_file extension.
 - Dispatch a list of SQL orders over multiple PostgreSQL connections
 - Perform a diff between Oracle and PostgreSQL database for test purposes.
 - MySQL/MariaDB and Microsoft SQL Server migration.
 Ora2Pg does its best to automatically convert your Oracle database to
 PostgreSQL but there's still manual work to do. The Oracle specific
 PL/SQL code generated for functions, procedures, packages and triggers
 has to be reviewed to match the PostgreSQL syntax. You will find some
 useful recommendations on porting Oracle PL/SQL code to PostgreSQL
 PL/PGSQL at "Converting from other Databases to PostgreSQL", section:
 Oracle (http://wiki.postgresql.org/wiki/Main_Page).
 See http://ora2pg.darold.net/report.html for an HTML sample of an Oracle
 database migration report.
INSTALLATION
 All Perl modules can always be found at CPAN (http://search.cpan.org/).
 Just type the full name of the module (ex: DBD::Oracle) into the search
 input box, it will bring you to the page for download.
 Releases of Ora2Pg are published at SF.net
 (https://sourceforge.net/projects/ora2pg/).
 On Windows(TM) you should install Strawberry Perl
 (http://strawberryperl.com/) and the OSes corresponding Oracle clients.
 Since version 5.32, the Perl distribution includes pre-compiled driver
 for DBD::Oracle and DBD::Pg.
 Required
 The Oracle Instant Client or a full Oracle installation must be
 installed on the system. You can download the RPM from Oracle download
 center:
 rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
 rpm -ivh oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
 rpm -ivh oracle-instantclient12.2-jdbc-12.2.0.1.0-1.x86_64.rpm
 rpm -ivh oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
 or simply download the corresponding ZIP archives from Oracle download
 center and install them where you want, for example:
 /opt/oracle/instantclient_12_2/
 You also need a modern Perl distribution (Perl 5.10 or later). To
 connect to a database and proceed with its migration, you need the DBI
 Perl module > 1.614. To migrate an Oracle database, you need the
 DBD::Oracle Perl module to be installed.
 To install DBD::Oracle and have it working, you need to have the Oracle
 client libraries installed and the ORACLE_HOME environment variable must
 be defined.
 If you plan to export a MySQL database, you need to install the Perl
 module DBD::MySQL which requires that the MySQL client libraries are
 installed.
 If you plan to export a SQL Server database, you need to install the
 Perl module DBD::ODBC which requires that the unixODBC package is
 installed.
 On some Perl distributions, you may need to install the Time::HiRes Perl
 module.
 If your distribution doesn't include these Perl modules, you can install
 them using CPAN:
 perl -MCPAN -e 'install DBD::Oracle'
 perl -MCPAN -e 'install DBD::MySQL'
 perl -MCPAN -e 'install DBD::ODBC'
 perl -MCPAN -e 'install Time::HiRes'
 otherwise, use the packages provided by your distribution.
 Optional
 By default, Ora2Pg dumps exports to flat files. To load them into your
 PostgreSQL database, you need the PostgreSQL client (psql). If you don't
 have it on the host running Ora2Pg, you can always transfer these files
 to a host with the psql client installed. If you prefer to load exports
 'on the fly', the Perl module DBD::Pg is required.
 Ora2Pg allows you to dump all output in a compressed gzip file. To do
 this, you need the Compress::Zlib Perl module or, if you prefer using
 bzip2 compression, the program bzip2 must be available in your PATH.
 If your distribution doesn't include these Perl modules, you can install
 them using CPAN:
 perl -MCPAN -e 'install DBD::Pg'
 perl -MCPAN -e 'install Compress::Zlib'
 otherwise, use the packages provided by your distribution.
 Instruction for SQL Server
 For SQL Server, you need to install the unixodbc package and the Perl
 DBD::ODBC driver:
 sudo apt install unixodbc
 sudo apt install libdbd-odbc-perl
 or
 sudo yum install unixodbc
 sudo yum install perl-DBD-ODBC
 sudo yum install perl-DBD-Pg
 Then install the Microsoft ODBC Driver for SQL Server. Follow the
 instructions for to your operating system from here:
 https://docs.microsoft.com/fr-fr/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16
 Once done, set the following in the /etc/odbcinst.ini file by adjusting
 the SQL Server ODBC driver version:
 [msodbcsql18]
 Description=Microsoft ODBC Driver 18 for SQL Server
 Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
 UsageCount=1
 See ORACLE_DSN to learn how to use the driver to connect to your MSSQL
 database.
 Installing Ora2Pg
 Like any other Perl Module, Ora2Pg can be installed with the following
 commands:
 tar xjf ora2pg-x.x.tar.bz2
 cd ora2pg-x.x/
 perl Makefile.PL
 make && make install
 This will install Ora2Pg.pm into your site Perl repository, ora2pg into
 /usr/local/bin/ and ora2pg.conf into /etc/ora2pg/.
 On Windows(TM), you may use instead:
 perl Makefile.PL
 gmake && gmake install
 This will install scripts and libraries into your Perl site installation
 directory and the ora2pg.conf file as well as all documentation files
 into C:\ora2pg\
 To install ora2pg in a different directory than the default one, simply
 use this command:
 perl Makefile.PL PREFIX=<your_install_dir>
 make && make install
 then set PERL5LIB to the path to your installation directory before
 using Ora2Pg.
 export PERL5LIB=<your_install_dir>
 ora2pg -c config/ora2pg.conf -t TABLE -b outdir/
 Packaging
 If you want to build binary packages for your preferred Linux
 distribution, take a look at the packaging/ directory of the source
 tarball. It contains everything needed to build RPM, Slackware and
 Debian packages. See the README file in that directory.
 Installing DBD::Oracle
 Ora2Pg needs the Perl module DBD::Oracle for connectivity to an Oracle
 database from Perl DBI. You can get DBD::Oracle from CPAN, a Perl module
 repository.
 After setting ORACLE_HOME and LD_LIBRARY_PATH environment variables as
 root user, install DBD::Oracle. Proceed as follows:
 export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib
 export ORACLE_HOME=/usr/lib/oracle/12.2/client64
 perl -MCPAN -e 'install DBD::Oracle'
 If you are running for the first time, it will ask many questions; you
 can keep defaults by pressing ENTER key, but you need to provide one
 appropriate mirror site for CPAN to download the modules. Install
 through CPAN manually if the above doesn't work:
 #perl -MCPAN -e shell
 cpan> get DBD::Oracle
 cpan> quit
 cd ~/.cpan/build/DBD-Oracle*
 export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
 export ORACLE_HOME=/usr/lib/oracle/11.2/client64
 perl Makefile.PL
 make
 make install
 Installing DBD::Oracle requires that the three Oracle packages:
 instant-client, SDK and SQLplus are installed as well as the libaio1
 library.
 If you are using Instant Client from ZIP archives, the LD_LIBRARY_PATH
 and ORACLE_HOME will be the same and must be set to the directory where
 you have installed the files. For example:
 /opt/oracle/instantclient_12_2/
CONFIGURATION
 Configuring Ora2Pg can be as simple as choosing the Oracle database to
 export and choosing the export type. This can be done in a minute.
 By reading this documentation you will also be able to:
 - Select only certain tables and/or columns for export.
 - Rename some tables and/or columns during export.
 - Select data to export following a WHERE clause per table.
 - Delay database constraints during data loading.
 - Compress exported data to save disk space.
 - and much more.
 The Oracle database migration is fully controlled through a single
 configuration file named ora2pg.conf. The format of this file consists
 of a directive name in upper case followed by a tab character and a
 value. Comments are lines beginning with a #.
 There's no specific order to place the configuration directives, they
 are set at the time they are read in the configuration file.
 For configuration directives that just take a single value, you can use
 them multiple times in the configuration file but only the last
 occurrence found in the file will be used. For configuration directives
 that allow a list of values, you can use them multiple times, the values
 will be appended to the list. If you use the IMPORT directive to load a
 custom configuration file, directives defined in this file will be
 stored from the place the IMPORT directive is found, so it is better to
 put it at the end of the configuration file.
 Values set in command line options will override values from the
 configuration file.
 Ora2Pg usage
 First of all be sure that libraries and binaries paths include the
 Oracle Instant Client installation:
 export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
 export PATH="/usr/lib/oracle/11.2/client64/bin:$PATH"
 By default, Ora2Pg will look for /etc/ora2pg/ora2pg.conf configuration
 file. If the file exists, you can simply execute:
 /usr/local/bin/ora2pg
 or under Windows(TM) run ora2pg.bat file, located in your Perl bin
 directory. Windows(TM) users may also find a template configuration file
 in C:\ora2pg
 If you want to call another configuration file, just give the path as a
 command line argument:
 /usr/local/bin/ora2pg -c /etc/ora2pg/new_ora2pg.conf
 Here are all command line parameters available when using ora2pg:
 Usage: ora2pg [-dhpqv --estimate_cost --dump_as_html] [--option value]
 -a | --allow str : Comma separated list of objects to allow from export.
 Can be used with SHOW_COLUMN too.
 -b | --basedir dir: Set the default output directory, where files
 resulting from exports will be stored.
 -c | --conf file : Set an alternate configuration file other than the
 default /etc/ora2pg/ora2pg.conf.
 -C | --cdc_file file: File used to store/read SCN per table during export.
 default: TABLES_SCN.log in the current directory. This
 is the file written by the --cdc_ready option.
 -d | --debug : Enable verbose output.
 -D | --data_type str : Allow custom type replacement at command line.
 -e | --exclude str: Comma separated list of objects to exclude from export.
 Can be used with SHOW_COLUMN too.
 -h | --help : Print this short help.
 -g | --grant_object type : Extract privilege from the given object type.
 See possible values with GRANT_OBJECT configuration.
 -i | --input file : File containing Oracle PL/SQL code to convert with
 no Oracle database connection initiated.
 -j | --jobs num : Number of parallel process to send data to PostgreSQL.
 -J | --copies num : Number of parallel connections to extract data from Oracle.
 -l | --log file : Set a log file. Default is stdout.
 -L | --limit num : Number of tuples extracted from Oracle and stored in
 memory before writing, default: 10000.
 -m | --mysql : Export a MySQL database instead of an Oracle schema.
 -M | --mssql : Export a Microsoft SQL Server database.
 -n | --namespace schema : Set the Oracle schema to extract from.
 -N | --pg_schema schema : Set PostgreSQL's search_path.
 -o | --out file : Set the path to the output file where SQL will
 be written. Default: output.sql in running directory.
 -O | --options : Used to override any configuration parameter, it can
 be used multiple time. Syntax: -O "PARAM_NAME=value"
 -p | --plsql : Enable PLSQL to PLPGSQL code conversion.
 -P | --parallel num: Number of parallel tables to extract at the same time.
 -q | --quiet : Disable progress bar.
 -r | --relative : use \ir instead of \i in the psql scripts generated.
 -s | --source DSN : Allow to set the Oracle DBI datasource.
 -S | --scn SCN : Allow to set the Oracle System Change Number (SCN) to
 use to export data. It will be used in the WHERE clause
 to get the data. It is used with action COPY or INSERT.
 -t | --type export: Set the export type. It will override the one
 given in the configuration file (TYPE).
 -T | --temp_dir dir: Set a distinct temporary directory when two
 or more ora2pg are run in parallel.
 -u | --user name : Set the Oracle database connection user.
 ORA2PG_USER environment variable can be used instead.
 -v | --version : Show Ora2Pg Version and exit.
 -w | --password pwd : Set the password of the Oracle database user.
 ORA2PG_PASSWD environment variable can be used instead.
 -W | --where clause : Set the WHERE clause to apply to the Oracle query to
 retrieve data. Can be used multiple time.
 --forceowner : Force ora2pg to set tables and sequences owner like in
 Oracle database. If the value is set to a username this one
 will be used as the objects owner. By default it's the user
 used to connect to the Pg database that will be the owner.
 --nls_lang code: Set the Oracle NLS_LANG client encoding.
 --client_encoding code: Set the PostgreSQL client encoding.
 --view_as_table str: Comma separated list of views to export as table.
 --estimate_cost : Activate the migration cost evaluation with SHOW_REPORT
 --cost_unit_value minutes: Number of minutes for a cost evaluation unit.
 default: 5 minutes, corresponds to a migration conducted by a
 PostgreSQL expert. Set it to 10 if this is your first migration.
 --dump_as_html : Force ora2pg to dump report in HTML, used only with
 SHOW_REPORT. Default is to dump report as simple text.
 --dump_as_csv : As above but force ora2pg to dump report in CSV.
 --dump_as_json : As above but force ora2pg to dump report in JSON.
 --dump_as_sheet : Report migration assessment with one CSV line per database.
 --init_project name: Initialise a typical ora2pg project tree. Top directory
 dump_as_* selected switches, suffixes
 will be .html, .csv, .json.
 --init_project name: Initialise a typical ora2pg project tree. Top directory
 will be created under project base dir.
 --project_base dir : Define the base dir for ora2pg project trees. Default
 is current directory.
 --print_header : Used with --dump_as_sheet to print the CSV header
 especially for the first run of ora2pg.
 --human_days_limit num : Set the number of person-days limit where the migration
 assessment level switch from B to C. Default is set to
 5 person-days.
 --audit_user list : Comma separated list of usernames to filter queries in
 the DBA_AUDIT_TRAIL table. Used only with SHOW_REPORT
 and QUERY export type.
 --pg_dsn DSN : Set the datasource to PostgreSQL for direct import.
 --pg_user name : Set the PostgreSQL user to use.
 --pg_pwd password : Set the PostgreSQL password to use.
 --count_rows : Force ora2pg to perform a real row count in TEST,
 TEST_COUNT and SHOW_TABLE actions.
 --no_header : Do not append Ora2Pg header to output file
 --oracle_speed : Use to know at which speed Oracle is able to send
 data. No data will be processed or written.
 --ora2pg_speed : Use to know at which speed Ora2Pg is able to send
 transformed data. Nothing will be written.
 --blob_to_lo : export BLOB as large objects, can only be used with
 action SHOW_COLUMN, TABLE and INSERT.
 --cdc_ready : use current SCN per table to export data and register
 them into a file named TABLES_SCN.log per default. It
 can be changed using -C | --cdc_file.
 --lo_import : use psql \lo_import command to import BLOB as large
 object. Can be use to import data with COPY and import
 large object manually in a second pass. It is recquired
 for BLOB > 1GB. See documentation for more explanation.
 --mview_as_table str: Comma separated list of materialized views to export
 as regular table.
 --drop_if_exists : Drop the object before creation if it exists.
 --delete clause : Set the DELETE clause to apply to the Oracle query to
 be applied before importing data. Can be used multiple
 time.
 --oracle_fdw_prefetch: Set the oracle_fdw prefetch value. Larger values
 generally result in faster data transfer at the cost
 of greater memory utilisation at the destination.
 See full documentation at https://ora2pg.darold.net/ for more help or
 see manpage with 'man ora2pg'.
 ora2pg will return 0 on success, 1 on error. It will return 2 when a
 child process has been interrupted and you've gotten the warning
 message: "WARNING: an error occured during data export. Please check
 what's happened." Most of the time this is an OOM issue, so first try
 reducing DATA_LIMIT value.
 For developers, it is possible to add your own custom option(s) in the
 Perl script ora2pg as any configuration directive from ora2pg.conf can
 be passed in lower case to the new Ora2Pg object instance. See ora2pg
 code on how to add your own option.
 Note that performance might be improved by updating stats on Oracle:
 BEGIN
 DBMS_STATS.GATHER_SCHEMA_STATS
 DBMS_STATS.GATHER_DATABASE_STATS 
 DBMS_STATS.GATHER_DICTIONARY_STATS
 END;
 Generate a migration template
 The two options --project_base and --init_project indicate to ora2pg
 that it should create a project template with a work tree, a
 configuration file and a script to export all objects from the Oracle
 database. Here is a sample of the command usage:
 ora2pg --project_base /app/migration/ --init_project test_project
 Creating project test_project.
 /app/migration/test_project/
 schema/
 dblinks/
 directories/
 functions/
 grants/
 mviews/
 packages/
 partitions/
 procedures/
 sequences/
 synonyms/
 tables/
 tablespaces/
 triggers/
 types/
 views/
 sources/
 functions/
 mviews/
 packages/
 partitions/
 procedures/
 triggers/
 types/
 views/
 data/
 config/
 reports/
 Generating generic configuration file
 Creating script export_schema.sh to automate all exports.
 Creating script import_all.sh to automate all imports.
 It creates a generic config file where you just have to define the
 Oracle database connection and a shell script called export_schema.sh.
 The sources/ directory will contain the Oracle code, the schema/
 directory will contain the code ported to PostgreSQL. The reports/
 directory will contain the HTML and JSON reports with the migration cost
 assessment.
 If you want to use your own default config file, use the -c option to
 give the path to that file. Rename it with .dist suffix if you want
 ora2pg to apply the generic configuration values; otherwise, the
 configuration file will be copied untouched.
 Once you have set the connection to the Oracle Database you can execute
 the script export_schema.sh that will export all object types from your
 Oracle database and output DDL files into the schema's subdirectories.
 At end of the export it will give you the command to export data later
 when the import of the schema is done and verified.
 You can choose to load the DDL files generated manually or use the
 second script import_all.sh to import those files interactively. If this
 kind of migration is not something common for you, it's recommended that
 you use those scripts.
 Oracle database connection
 There are 5 configuration directives to control the access to the Oracle
 database.
 ORACLE_HOME
 Used to set the ORACLE_HOME environment variable for the Oracle
 libraries required by the DBD::Oracle Perl module.
 ORACLE_DSN
 This directive is used to set the data source name in the standard
 DBI DSN form. For example:
 dbi:Oracle:host=oradb_host.myhost.com;sid=DB_SID;port=1521
 or
 dbi:Oracle:DB_SID
 On 18c this could be for example:
 dbi:Oracle:host=192.168.1.29;service_name=pdb1;port=1521
 For the second notation, the SID should be declared in the
 well-known file $ORACLE_HOME/network/admin/tnsnames.ora or in the
 path given to the TNS_ADMIN environment variable.
 For MySQL the DSN will look like this:
 dbi:mysql:host=192.168.1.10;database=sakila;port=3306
 The 'sid' part is replaced by 'database'.
 For MS SQL Server it will look like this:
 dbi:ODBC:driver=msodbcsql18;server=mydb.database.windows.net;database=testdb;TrustServerCertificate=yes
 ORACLE_USER and ORACLE_PWD
 These two directives are used to define the user and password for
 the Oracle database connection. Note that if possible, it is better
 to login as Oracle super admin to avoid grant problems during the
 database scan and ensure nothing is missing.
 If you do not supply credentials with ORACLE_PWD and you have
 installed the Term::ReadKey Perl module, Ora2Pg will ask for the
 password interactively. If ORACLE_USER is not set it will also be
 asked interactively.
 To connect to a local ORACLE instance with connections "as sysdba"
 you have to set ORACLE_USER to "/" and an empty password.
 To make a connection using an Oracle Secure External Password Store
 (SEPS), first configure the Oracle Wallet and then set both the
 ORACLE_USER and ORACLE_PWD directives to the special value of
 "__SEPS__" (without the quotes but with the double underscore).
 USER_GRANTS
 Set this directive to 1 if you connect to Oracle database as a
 simple user and do not have enough grants to extract things from the
 DBA_... tables. It will use ALL_... tables instead.
 Warning: if you use export type GRANT, you must set this
 configuration option to 0 or it will not work.
 TRANSACTION
 This directive may be used if you want to change the default
 isolation level of the data export transaction. Default is now to
 set the level to a serializable transaction to ensure data
 consistency. The allowed values for this directive are:
 readonly: 'SET TRANSACTION READ ONLY',
 readwrite: 'SET TRANSACTION READ WRITE',
 serializable: 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'
 committed: 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED',
 Releases before 6.2 used to set the isolation level to READ ONLY
 transaction but in some cases this was breaking data consistency so
 now default is set to SERIALIZABLE.
 INPUT_FILE
 This directive does not control the Oracle database connection but
 rather it purely disables the use of any Oracle database by
 accepting a file as argument. Set this directive to a file
 containing PL/SQL Oracle Code like function, procedure or full
 package body to prevent Ora2Pg from connecting to an Oracle database
 and just apply its conversion tool to the content of the file. This
 can be used with most export types: TABLE, TRIGGER, PROCEDURE, VIEW,
 FUNCTION or PACKAGE, etc.
 ORA_INITIAL_COMMAND
 This directive can be used to send an initial command to Oracle,
 just after the connection. For example to unlock a policy before
 reading objects or to set some session parameters. This directive
 can be used multiple times.
 Data encryption with Oracle server
 If your Oracle Client config file already includes the encryption
 method, then DBD::Oracle uses those settings to encrypt the connection
 while extracting data. For example, if you have configured the Oracle
 Client config file (sqlnet.ora or .sqlnet) with the following
 information:
 # Configure encryption of connections to Oracle
 SQLNET.ENCRYPTION_CLIENT = required
 SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256, RC4_256)
 SQLNET.CRYPTO_SEED = 'should be 10-70 random characters'
 Any tool that uses the Oracle client to communicate with the database
 will have encrypted connections if you setup session encryption as shown
 above.
 For example, Perl's DBI uses DBD::Oracle, which uses the Oracle client
 for actual database communication. If the Oracle client installation
 used by Perl is setup to request encrypted connections, then your Perl
 connection to an Oracle database will also be encrypted.
 Full details at
 https://kb.berkeley.edu/jivekb/entry.jspa?externalID=1005
 Testing connection
 Once you have set the Oracle database DSN, you can execute ora2pg to see
 if it works:
 ora2pg -t SHOW_VERSION -c config/ora2pg.conf
 will show the Oracle database server version. Take some time here to
 test your installation as most problems occur here. The other
 configuration steps are more technical.
 Troubleshooting
 If the output.sql file hasn't exported anything other than the
 PostgreSQL transaction header and footer, there are two possible
 reasons: 1) The perl script ora2pg dumps an ORA-XXX error, which means
 that your DSN or login information is wrong - check the error and your
 settings and try again. 2) The perl script says nothing and the output
 file is empty: the user lacks permissions to extract something from the
 database. Try to connect to Oracle as super user or review the
 USER_GRANTS directive above and the next section, especially the SCHEMA
 directive.
 LOGFILE
 By default, all messages are sent to the standard output. If you
 provide a file path to this directive, all output will be appended
 to this file.
 Oracle schema to export
 The Oracle database export can be limited to a specific Schema or
 Namespace; this may be mandatory depending on the database connection
 user.
 SCHEMA
 This directive is used to set the schema name to use during export.
 For example:
 SCHEMA APPS
 will extract objects associated with the APPS schema.
 When no schema name is provided and EXPORT_SCHEMA is enabled, Ora2Pg
 will export all objects from all schemas of the Oracle instance with
 their names prefixed with the schema name.
 EXPORT_SCHEMA
 By default, the Oracle schema is not exported into the PostgreSQL
 database and all objects are created under the default Pg namespace.
 If you want to also export this schema and create all objects under
 this namespace, set the EXPORT_SCHEMA directive to 1. This will set
 the schema search_path at the top of the export SQL file to the
 schema name set in the SCHEMA directive with the default pg_catalog
 schema. If you want to change this path, use the directive
 PG_SCHEMA.
 CREATE_SCHEMA
 Enable/disable the CREATE SCHEMA SQL order at the start of the
 output file. It is enabled by default and concerns the TABLE export
 type.
 COMPILE_SCHEMA
 By default, Ora2Pg will only export valid PL/SQL code. You can force
 Oracle to compile again the invalidated code to get a chance to have
 it obtain the valid status and then be able to export it.
 Enable this directive to force Oracle to compile schema before
 exporting code. When this directive is enabled and SCHEMA is set to
 a specific schema name, only invalid objects in this schema will be
 recompiled. If SCHEMA is not set then all schema will be recompiled.
 To force recompilation of invalid object in a specific schema, set
 COMPILE_SCHEMA to the schema name you want to recompile.
 This will ask Oracle to validate the PL/SQL that could have been
 invalidated after an export/import for example. The 'VALID' or
 'INVALID' status applies to functions, procedures, packages and user
 defined types. It also concerns disabled triggers.
 EXPORT_INVALID
 If the above configuration directive is not enough to validate your
 PL/SQL code, enable this configuration directive to allow export of
 all PL/SQL code even if it is marked as invalid. The 'VALID' or
 'INVALID' status applies to functions, procedures, packages,
 triggers and user defined types.
 PG_SCHEMA
 Allows you to define/force the PostgreSQL schema to use. By default,
 if you set EXPORT_SCHEMA to 1, the PostgreSQL search_path will be
 set to the schema name exported set as value of the SCHEMA
 directive.
 The value can be a comma-delimited list of schema names but not when
 using TABLE export type because in this case it will generate the
 CREATE SCHEMA statement and it doesn't support multiple schema
 names. For example, if you set PG_SCHEMA to something like
 "user_schema, public", the search path will be set like this:
 SET search_path = user_schema, public;
 forcing the use of an other schema (here user_schema) than the one
 from Oracle schema set in the SCHEMA directive.
 You can also set the default search_path for the PostgreSQL user you
 are using to connect to the destination database by using:
 ALTER ROLE username SET search_path TO user_schema, public;
 in this case you don't have to set PG_SCHEMA.
 SYSUSERS
 Without explicit schema, Ora2Pg will export all objects that do not
 belong to system schemas or roles:
 SYSTEM,CTXSYS,DBSNMP,EXFSYS,LBACSYS,MDSYS,MGMT_VIEW,
 OLAPSYS,ORDDATA,OWBSYS,ORDPLUGINS,ORDSYS,OUTLN,
 SI_INFORMTN_SCHEMA,SYS,SYSMAN,WK_TEST,WKSYS,WKPROXY,
 WMSYS,XDB,APEX_PUBLIC_USER,DIP,FLOWS_020100,FLOWS_030000,
 FLOWS_040100,FLOWS_010600,FLOWS_FILES,MDDATA,ORACLE_OCM,
 SPATIAL_CSW_ADMIN_USR,SPATIAL_WFS_ADMIN_USR,XS$NULL,PERFSTAT,
 SQLTXPLAIN,DMSYS,TSMSYS,WKSYS,APEX_040000,APEX_040200,
 DVSYS,OJVMSYS,GSMADMIN_INTERNAL,APPQOSSYS,DVSYS,DVF,
 AUDSYS,APEX_030200,MGMT_VIEW,ODM,ODM_MTR,TRACESRV,MTMSYS,
 OWBSYS_AUDIT,WEBSYS,WK_PROXY,OSE$HTTP$ADMIN,
 AURORA$JIS$UTILITY,ドルAURORA$ORB$UNAUTHENTICATED,
 DBMS_PRIVILEGE_CAPTURE,CSMIG,MGDSYS,SDE,DBSFWUSER
 Depending on your Oracle installation, you may have several other
 system roles defined. To append these users to the schema exclusion
 list, just set the SYSUSERS configuration directive to a
 comma-separated list of system users to exclude. For example:
 SYSUSERS INTERNAL,SYSDBA,BI,HR,IX,OE,PM,SH
 will add users INTERNAL and SYSDBA to the schema exclusion list.
 FORCE_OWNER
 By default, the owner of the database objects is the one you're
 using to connect to PostgreSQL using the psql command. If you use an
 other user (postgres for example), you can force Ora2Pg to set the
 object owner to be the one used in the Oracle database by setting
 the directive to 1, or to a completely different username by setting
 the directive value to that username.
 FORCE_SECURITY_INVOKER
 Ora2Pg uses the function's security privileges set in Oracle and it
 is often defined as SECURITY DEFINER. If you want to override those
 security privileges for all functions and use SECURITY DEFINER
 instead, enable this directive.
 USE_TABLESPACE
 When enabled this directive forces ora2pg to export all tables and
 indexes using the tablespace name defined in Oracle database. This
 works only with tablespaces that are not TEMP, USERS or SYSTEM.
 WITH_OID
 Activating this directive will force Ora2Pg to add WITH (OIDS) when
 creating tables or views as tables. Default is same as PostgreSQL,
 disabled.
 LOOK_FORWARD_FUNCTION
 List of schemas to get functions/procedures meta information that
 are used in the current schema export. When replacing calls to
 functions with OUT parameters, if a function is declared in an other
 package, then the function call rewriting can not be done because
 Ora2Pg only knows about functions declared in the current schema. By
 setting a comma-separated list of schemas as value of this
 directive, Ora2Pg will look forward in these packages for all
 functions/procedures/packages declarations before proceeding to
 current schema export.
 NO_FUNCTION_METADATA
 Forces Ora2Pg to not look for function declarations. Note that this
 will prevent Ora2Pg from rewriting function replacement calls if
 needed. Do not enable it unless looking forward at functions breaks
 other exports.
 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.
 Limiting objects to export
 You may want to export only a part of an Oracle database. Here is a set
 of configuration directives that will allow you to control which parts
 of the database should be exported.
 ALLOW
 This directive allows you to set a list of objects to which the
 export must be limited, excluding all other objects of the same type
 of export. The value is a space or comma-separated list of object
 names to export. You can include valid regex into the list. For
 example:
 ALLOW EMPLOYEES SALE_.* COUNTRIES .*_GEOM_SEQ
 will export objects with names EMPLOYEES, COUNTRIES, all objects
 beginning with 'SALE_' and all objects with a name ending in
 '_GEOM_SEQ'. The object depends of the export type. Note that regex
 will not work with 8i database, you must use the % placeholder
 instead, Ora2Pg will use the LIKE operator.
 This is the way 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:
 ora2pg -p -c ora2pg.conf -t TRIGGER -a 'TABLE[employees]'
 will limit export of triggers to those defined on table employees.
 If you want to extract all triggers but not some INSTEAD OF
 triggers:
 ora2pg -c ora2pg.conf -t TRIGGER -e 'VIEW[trg_view_.*]'
 Or a more complex form:
 ora2pg -p -c ora2pg.conf -t TABLE -a 'TABLE[EMPLOYEES]' \
 -e 'INDEX[emp_.*];CKEY[emp_salary_min]'
 This command will export the definition of the employee table but
 will exclude all indexes beginning with 'emp_' and the CHECK
 constraint called 'emp_salary_min'.
 When exporting partitions you can exclude some partition tables by
 using
 ora2pg -p -c ora2pg.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:
 ora2pg -p -c ora2pg.conf -t GRANT -a 'USER1 USER2'
 or
 ora2pg -p -c ora2pg.conf -t GRANT -a 'GRANT[USER1 USER2]'
 will limit export grants to users USER1 and USER2. But if you don't
 want to export grants on some functions for these users, for
 example:
 ora2pg -p -c ora2pg.conf -t GRANT -a 'USER1 USER2' -e 'FUNCTION[adm_.*];PROCEDURE[adm_.*]'
 Advanced filters may need some learning.
 Oracle doesn't allow the use of lookahead expressions so you may
 want to exclude some objects that match the ALLOW regexp you have
 defined. For example if you want to export all tables 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 objects matching the regexp given
 just after. Our previous example can be written as follows:
 ALLOW E.* !EXP.*
 it will be translated into:
 REGEXP_LIKE(..., '^E.*$') AND NOT REGEXP_LIKE(..., '^EXP.*$')
 in the object search expression.
 EXCLUDE
 This directive is the opposite of the previous. It allows you to
 define a space or comma-separated list of object names to exclude
 from the export. You can include valid regex in the list. For
 example:
 EXCLUDE EMPLOYEES TMP_.* COUNTRIES
 will exclude objects with names EMPLOYEES, COUNTRIES and all tables
 beginning with 'tmp_'.
 For example, you can ban some unwanted functions from export with
 this directive:
 EXCLUDE write_to_.* send_mail_.*
 This example will exclude all functions, procedures or functions in
 a package with names beginning with those regex. Note that regex
 will not work with 8i database, you must use the % placeholder
 instead, Ora2Pg will use the NOT LIKE operator.
 See above (directive 'ALLOW') for the extended syntax.
 NO_EXCLUDED_TABLE
 By default, Ora2Pg excludes from export some Oracle "garbage" tables
 from export that should never be part of an export. This behavior
 generates a lot of REGEXP_LIKE expressions which slow down the
 export when looking at tables. To disable this behavior enable this
 directive, you will have to exclude or clean up later by yourself
 the unwanted tables. The regexps used to exclude the tables are
 defined in the array @EXCLUDED_TABLES in lib/Ora2Pg.pm. Note this
 behavior is independent of the EXCLUDE configuration directive.
 VIEW_AS_TABLE
 Set which views to export as tables. By default none. Value must be
 a list of view names or regexps 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 chapter "Exporting views as PostgreSQL table" for more details.
 MVIEW_AS_TABLE
 Set which materialized views to export as tables. By default none.
 Value must be a list of materialized view names or regexps 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.
 NO_VIEW_ORDERING
 By default, Ora2Pg tries to order views to avoid errors at 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.
 GRANT_OBJECT
 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 possible 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.
 When used this directive prevents the export of users unless it is
 set to USER. In this case only user definitions are exported.
 WHERE
 This directive allows you to specify a WHERE clause filter when
 dumping the contents of tables. The value is constructed as follows:
 TABLE_NAME[WHERE_CLAUSE], or if you have only one where clause for
 all tables 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 clauses 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 in a table name bracket clause will be
 applied to all exported tables including the tables defined in the
 WHERE clause. These WHERE clauses are very useful if you want to
 archive some data or only export some recent data.
 To be able to quickly test data import it is useful to limit data
 export to the first thousand tuples of each table. For Oracle define
 the following clause:
 WHERE ROWNUM < 1000
 and for MySQL, use the following:
 WHERE 1=1 LIMIT 1,1000
 This can also be restricted to some tables' data export.
 Command line option -W or --where will override this directive for
 the global part and per table if the table names are the same.
 TOP_MAX
 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.
 LOG_ON_ERROR
 Enable this directive if you want to continue direct data import on
 error. When Ora2Pg receives an error in the COPY or INSERT statement
 from PostgreSQL it will log the statement to a file called
 TABLENAME_error.log in the output directory and continue to 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.
 REPLACE_QUERY
 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
 Ora2Pg does but a more complex query. This directive allows you to
 overwrite the query used by Ora2Pg to extract data. The format is
 TABLENAME[SQL_QUERY]. If you have multiple tables to extract by
 replacing the Ora2Pg query, you can define multiple REPLACE_QUERY
 lines.
 REPLACE_QUERY EMPLOYEES[SELECT e.id,e.firstname,lastname FROM EMPLOYEES e JOIN EMP_UPDT u ON (e.id=u.id AND u.cdate>'2014-08-01 00:00:00')]
 Control of Full Text Search export
 Several directives can be used to control how Ora2Pg exports the
 Oracle's Text search indexes. By default, CONTEXT indexes will be
 exported to PostgreSQL FTS indexes, while CTXCAT indexes will be
 exported as indexes using the pg_trgm extension.
 CONTEXT_AS_TRGM
 Forces Ora2Pg to translate Oracle Text indexes into PostgreSQL
 indexes using the pg_trgm extension. By default, CONTEXT indexes are
 translated into FTS indexes and CTXCAT indexes use pg_trgm. Most of
 the time using pg_trgm is sufficient, which is why this directive
 exists. You need to create the pg_trgm extension in the destination
 database before importing the objects:
 CREATE EXTENSION pg_trgm;
 FTS_INDEX_ONLY
 By default, Ora2Pg 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 CONTAINS() clause using to_tsvector(),
 for example:
 SELECT id,title FROM t_document
 WHERE to_tsvector(title) @@ to_tsquery('search_word');
 To force Ora2Pg to create an extra tsvector column with dedicated
 triggers for FTS indexes, disable this directive. In this case,
 Ora2Pg will add the column as follows: ALTER TABLE t_document ADD
 COLUMN tsv_title tsvector; Then update the column to compute FTS
 vectors if 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 occurs, Ora2Pg 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, Ora2Pg
 will use setweight() to set weights in the order of the column
 declarations.
 FTS_CONFIG
 Use this directive to force which text search configuration to use.
 When it is not set, Ora2Pg will autodetect the stemmer used by
 Oracle for each index and use pg_catalog.english if the information
 is not found.
 USE_UNACCENT
 If you want to perform text searches in an accent-insensitive way,
 enable this directive. Ora2Pg will create a helper function using
 unaccent() and create the pg_trgm indexes using this function. With
 FTS, Ora2Pg 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 ora2pg.conf directive to fr instead of
 pg_catalog.english.
 When enabled, Ora2pg 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%';
 USE_LOWER_UNACCENT
 Same as above but calls 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;
 Modifying object structure
 One of the great uses of Ora2Pg is its flexibility to replicate an
 Oracle database into a PostgreSQL database with a different structure or
 schema. There are three configuration directives that allow you to map
 these differences.
 REORDERING_COLUMNS
 Enable this directive to reorder columns and minimize the footprint
 on disk, so that more rows fit on a data page, which is the most
 important factor for speed. Default is disabled, meaning the same
 order as in Oracle tables definition, which should be enough for
 most uses. This directive is only used with TABLE export.
 MODIFY_STRUCT
 This directive allows you to limit the columns to extract for a
 given table. The value consists of a space-separated list of table
 names with a set of columns between parentheses as follows:
 MODIFY_STRUCT TABLE_NAME(colname1,colname2,...) ...
 for example:
 MODIFY_STRUCT T_TEST1(id,dossier) T_TEST2(id,fichier)
 This will only extract columns 'id' and 'dossier' from table T_TEST1
 and columns 'id' and 'fichier' from the T_TEST2 table. This
 directive can only be used with TABLE, COPY or INSERT export. With
 TABLE export create table DDL will respect the new list of columns
 and all indexes or foreign keys pointing to or from a removed column
 will not be exported.
 EXCLUDE_COLUMNS
 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
 column between parentheses as follows:
 EXCLUDE_COLUMNS TABLE_NAME(colname1,colname2,...) ...
 for example:
 EXCLUDE_COLUMNS T_TEST1(id,dossier) T_TEST2(id,fichier)
 This will exclude columns 'id' and 'dossier' from table T_TEST1 and
 columns 'id' and 'fichier' from the T_TEST2 table from the export.
 This directive can only be used with TABLE, COPY or INSERT export.
 With TABLE export create table DDL will respect the new list of
 columns and all indexes or foreign keys pointing to or from a
 removed column will not be exported.
 REPLACE_TABLES
 This directive allows you to remap a list of Oracle table names to a
 PostgreSQL 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
 REPLACE_COLS
 Like table names, column names can be remapped to different names
 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)
 will rename Oracle columns 'dico' and 'dossier' from table T_TEST to
 new names 'dictionary' and 'folder'.
 REPLACE_AS_BOOLEAN
 If you want to change the type of some Oracle columns to PostgreSQL
 boolean during the export you can define here a list of tables and
 columns separated by spaces 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 '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's 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 automatically convert all
 fields of that type to boolean. For example:
 REPLACE_AS_BOOLEAN NUMBER:1 CHAR:1 TB_NAME1:COL_NAME1 TB_NAME1:COL_NAME2
 will also replace any field of type number(1) or char(1) as a
 boolean in all exported tables.
 BOOLEAN_VALUES
 Use this to add additional definitions 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
 Ora2Pg:
 BOOLEAN_VALUES yes:no y:n 1:0 true:false enabled:disabled
 Any values defined here will be added to the default list.
 REPLACE_ZERO_DATE
 When Ora2Pg finds a "zero" date: 0000-00-00 00:00:00 it is replaced
 by a NULL. This could be a problem if your column is defined with a
 NOT NULL constraint. If you can not remove the constraint, use this
 directive to set an arbitrary date that will be used instead. You
 can also use -INFINITY if you don't want to use a fake date.
 INDEXES_SUFFIX
 Add the given value as suffix to index names. Useful if you have
 indexes with the same name as tables. For example:
 INDEXES_SUFFIX _idx
 will add _idx at the end of all index names. Not very common but
 helpful.
 INDEXES_RENAMING
 Enable this directive to rename all indexes using
 tablename_columns_names. Could be very useful for databases that
 have multiple instances of the same index name or that use the same
 name as a table, which is not allowed by PostgreSQL. Disabled by
 default.
 USE_INDEX_OPCLASS
 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 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 enabled with value
 1, this will force Ora2Pg 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 than or equal to this value.
 For example, set it to 128 to create these kinds of indexes on
 columns of type varchar2(N) where N >= 128.
 RENAME_PARTITION
 Enable this directive if you want your partition tables to be
 renamed. Disabled by default. If you have multiple partitioned
 tables, when exported to PostgreSQL some partitions could have the
 same name but different parent tables. This is not allowed - table
 names must be unique. In this case, enable this directive. A
 partition will be renamed following the rule: "tablename"_part"pos"
 where "pos" is the partition number. For subpartition this is:
 "tablename"_part"pos"_subpart"pos" If this is partition/subpartition
 default: "tablename"_part_default
 "tablename"_part"pos"_subpart_default
 DISABLE_PARTITION
 If you don't want to reproduce the partitioning like in Oracle and
 want to export all partitioned Oracle data into the main single
 table in PostgreSQL, enable this directive. Ora2Pg will export all
 data into the main table name. Default is to use partitioning -
 Ora2Pg will export data from each partition and import them into the
 PostgreSQL dedicated partition table.
 PARTITION_BY_REFERENCE
 How to export partition by reference. Possible values are none,
 duplicate or the number of hash partitions to create. Default is
 none to not export the partitions by reference.
 Value 'none' means no translation and export of partition by
 reference like before. Value 'duplicate' will duplicate the
 referenced column in the partitioned table and apply the same
 partitioning from the referenced table to the partitioned table. If
 the value is a number, the table will be partitioned with the HASH
 method using the value as the modulo. For example, if you set it to
 4 it will create 4 HASH partitions.
 DISABLE_UNLOGGED
 By default, Ora2Pg 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 PostgreSQL crash. Set it to 1 to export all tables as normal
 tables.
 DOUBLE_MAX_VARCHAR
 Increase varchar max character constraints to support PostgreSQL two
 bytes character encoding when the source database applies the length
 constraint on characters not bytes. Default disabled.
 Oracle Spatial to PostGIS
 Ora2Pg fully exports Spatial objects from Oracle database. There are
 some configuration directives that can be used to control the export.
 AUTODETECT_SPATIAL_TYPE
 By default, Ora2Pg looks at indexes to see the spatial constraint
 type and dimensions defined under Oracle. Those constraints are
 passed at index creation using for example:
 CREATE INDEX ... INDEXTYPE IS MDSYS.SPATIAL_INDEX
 PARAMETERS('sdo_indx_dims=2, layer_gtype=point');
 If those Oracle constraint parameters are not set, the default is to
 export those columns as generic type GEOMETRY to be able to receive
 any spatial type.
 The AUTODETECT_SPATIAL_TYPE directive allows Ora2Pg to autodetect
 the real spatial type and dimension used in a spatial column;
 otherwise a non- constrained "geometry" type is used. Enabling this
 feature will force Ora2Pg to scan a sample of 50,000 columns to look
 at the GTYPE used. You can increase or reduce the sample size by
 setting the value of AUTODETECT_SPATIAL_TYPE to the desired number
 of lines to scan. The directive is enabled by default.
 For example, in the case of a column named shape and defined with
 Oracle type SDO_GEOMETRY, with AUTODETECT_SPATIAL_TYPE disabled it
 will be converted as:
 shape geometry(GEOMETRY) or shape geometry(GEOMETRYZ, 4326)
 and if the directive is enabled and the column just contains a
 single geometry type that uses a single dimension:
 shape geometry(POLYGON, 4326) or shape geometry(POLYGONZ, 4326)
 with a two or three dimensional polygon.
 CONVERT_SRID
 This directive allows you to control the automatic conversion of
 Oracle SRID to standard EPSG. If enabled, Ora2Pg will use the Oracle
 function sdo_cs.map_oracle_srid_to_epsg() to convert all SRIDs.
 Enabled by default.
 If the SDO_SRID returned by Oracle is NULL, it will be replaced by
 the default value 8307 converted to its EPSG value: 4326 (see
 DEFAULT_SRID).
 If the value is higher than 1, all SRIDs will be forced to this
 value. In this case, DEFAULT_SRID will not be used when Oracle
 returns a null value, and the value will be forced to CONVERT_SRID.
 Note that it is also possible to set the EPSG value on the Oracle
 side when sdo_cs.map_oracle_srid_to_epsg() returns NULL if you want
 to force the value:
 system@db> UPDATE sdo_coord_ref_sys SET legacy_code=41014 WHERE srid = 27572;
 DEFAULT_SRID
 Use this directive to override the default EPSG SRID to use: 4326.
 Can be overwritten by CONVERT_SRID, see above.
 GEOMETRY_EXTRACT_TYPE
 This directive can take three values: WKT (default), WKB and
 INTERNAL. When it is set to WKT, Ora2Pg will use
 SDO_UTIL.TO_WKTGEOMETRY() to extract the geometry data. When it is
 set to WKB, Ora2Pg will use the binary output using
 SDO_UTIL.TO_WKBGEOMETRY(). If these two extract types are called at
 the Oracle side, they are slow and you can easily reach Out Of
 Memory when you have lots of rows. Also, WKB is not able to export
 3D geometry and some geometries like CURVEPOLYGON. In this case, you
 may use the INTERNAL extraction type. It will use a Pure Perl
 library to convert the SDO_GEOMETRY data into a WKT representation,
 the translation is done on Ora2Pg side. This is a work in progress,
 please validate your exported data geometries before use. Default
 spatial object extraction type is INTERNAL.
 POSTGIS_SCHEMA
 Use this directive to add a specific schema to the search path to
 look for PostGIS functions.
 ST_SRID_FUNCTION
 Oracle function to use to extract the SRID from ST_Geometry meta
 information. Default: ST_SRID, for example it should be set to
 sde.st_srid for ArcSDE.
 ST_DIMENSION_FUNCTION
 Oracle function to use to extract the dimension from ST_Geometry
 meta information. Default: ST_DIMENSION, for example it should be
 set to sde.st_dimension for ArcSDE.
 ST_GEOMETRYTYPE_FUNCTION
 Oracle function to use to extract the geometry type from an
 ST_Geometry column. Default: ST_GEOMETRYTYPE, for example it should
 be set to sde.st_geometrytype for ArcSDE.
 ST_ASBINARY_FUNCTION
 Oracle function used to convert an ST_Geometry value into WKB
 format. Default: ST_ASBINARY, for example it should be set to
 sde.st_asbinary for ArcSDE.
 ST_ASTEXT_FUNCTION
 Oracle function used to convert an ST_Geometry value into WKT
 format. Default: ST_ASTEXT, for example it should be set to
 sde.st_astext for ArcSDE.
 PostgreSQL Import
 By default, conversion to PostgreSQL format is written to a file named
 'output.sql'. The command:
 psql mydb < output.sql
 will import the contents of file output.sql into the PostgreSQL mydb
 database.
 DATA_LIMIT
 When performing INSERT/COPY export, Ora2Pg processes data in 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 increase this limit to a higher
 value, for example: 100000 or 1000000. Before release 7.0, a value
 of 0 meant no limit so that all tuples were stored in memory before
 being flushed to disk. In the 7.x branch this has been removed and
 chunks will be set to the default: 10000
 BLOB_LIMIT
 When Ora2Pg detects a table with BLOB data, 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 BLOBs uses lot of resources; setting it to a
 too high value can produce OOM errors.
 CLOB_AS_BLOB
 Applies same behavior on CLOBs than BLOBs with BLOB_LIMIT settings.
 This is especially useful if you have large CLOB data. Default:
 enabled
 OUTPUT
 The Ora2Pg 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.
 OUTPUT_DIR
 Since release 7.0, you can define a base directory where the files
 will be written. The directory must exist.
 BZIP2
 This directive allows you to specify the full path to the bzip2
 program if it can not be found in the PATH environment variable.
 FILE_PER_CONSTRAINT
 Allows 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 PostgreSQL using the
 LOAD export type to parallelize their creation over multiple (-j or
 JOBS) connections.
 FILE_PER_INDEX
 Allows 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 the end of the migration
 after the indexes creation to move the indexes.
 The indexes can be imported quickly into PostgreSQL using the LOAD
 export type to parallelize their creation over multiple (-j or JOBS)
 connections.
 FILE_PER_FKEYS
 Allows foreign key declarations 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
 FILE_PER_TABLE
 Allows 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.
 FILE_PER_FUNCTION
 Allows 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've enabled this directive,
 Ora2Pg will create a directory per package, named with the lower
 case name of the package, and will create one file per
 function/procedure in 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.
 TRUNCATE_TABLE
 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 types.
 When activated, the instruction will be added only if there's no
 global DELETE clause or no specific one for to the current table
 (see below).
 DELETE
 Supports including a DELETE FROM ... WHERE clause filter before
 importing data to perform a delete 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 example applies two different delete where clauses on
 tables TABLE_TEST and TABLE_INFO and a generic delete where 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".
 STOP_ON_ERROR
 Set this parameter to 0 to not include the call to \set
 ON_ERROR_STOP ON in all SQL scripts generated by Ora2Pg. By default
 this order is always present so that the script will immediately
 abort when an error is encountered.
 COPY_FREEZE
 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 PostgreSQL under the same condition but -j
 or JOBS must also be unset or default to 1.
 CREATE_OR_REPLACE
 By default Ora2Pg 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.
 DROP_IF_EXISTS
 To add a DROP <OBJECT> IF EXISTS before creating the object, enable
 this directive. Can be useful in iterative work. Default is
 disabled.
 EXPORT_GTT
 PostgreSQL does not support Global Temporary Tables natively but you
 can use the pgtt extension to emulate this behavior. Enable this
 directive to export global temporary tables.
 PGTT_NOSUPERUSER
 By default the pgtt extension is loaded using superuser privileges.
 Enable it if you run the SQL scripts generated using a non superuser
 user. It will use:
 LOAD '$libdir/plugins/pgtt';
 instead of default:
 LOAD 'pgtt';
 NO_HEADER
 Enabling this directive will prevent Ora2Pg from printing its header
 into output files. Only the translated code will be written.
 PSQL_RELATIVE_PATH
 By default, Ora2Pg uses \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.
 DATA_VALIDATION_ROWS
 Number of rows that must be retrieved on both sides for data
 validation. Default is to compare the first 10000 rows. A value of 0
 means compare all rows.
 DATA_VALIDATION_ORDERING
 Order of rows between both sides is different once the data has been
 modified. In this case data must be ordered using a primary key or a
 unique index, meaning that a table without such object cannot 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.
 DATA_VALIDATION_ERROR
 Stop validating data from a table after a certain amount of row
 mismatches. Default is to stop after 10 rows validation errors.
 TRANSFORM_VALUE
 Use this directive to specify which transformation should be applied
 to a column when exporting data. Value must be a semicolon-separated
 list of
 TABLE[COLUMN_NAME, <replace code in SELECT target list>]
 For example, to replace the string 'Oracle' with 'PostgreSQL' in a
 varchar2 column, use the following.
 TRANSFORM_VALUE ERROR_LOG_SAMPLE[DBMS_TYPE:regexp_replace("DBMS_TYPE",'Oracle','PostgreSQL')]
 or 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.
 NO_START_SCN
 Enable this directive if you don't want to export all data based on
 current SCN. By default Ora2Pg get first the current SCN and then
 retrieve all table data using this SCN to be consistant in case of
 data modification.
 When using Ora2Pg export type INSERT or COPY to dump data to a file and
 FILE_PER_TABLE is enabled, you will be warned that Ora2Pg will not
 export data again if the file already exists. This is to prevent
 downloading table data twice when dealing with huge amount of data. To
 force the download of data from these tables you have to remove the
 existing output file first.
 If you want to import data on the fly to the PostgreSQL database, you
 have three configuration directives to set the PostgreSQL database
 connection. This is only possible with COPY or INSERT export type as for
 database schema there's no real benefit to do that.
 PG_DSN
 Use this directive to set the PostgreSQL data source namespace using
 DBD::Pg Perl module as follows:
 dbi:Pg:dbname=pgdb;host=localhost;port=5432
 will connect to database 'pgdb' on localhost at tcp port 5432.
 Note that this directive is only used for data export, other exports
 need to be imported manually through the use of psql or any other
 PostgreSQL client.
 To use SSL encrypted connection you must add sslmode=require to the
 connection string as follows:
 dbi:Pg:dbname=pgdb;host=localhost;port=5432;sslmode=require
 PG_USER and PG_PWD
 These two directives are used to set the login user and password.
 If you do not supply credentials with PG_PWD and you have installed
 the Term::ReadKey Perl module, Ora2Pg will ask for the password
 interactively. If PG_USER is not set it will be asked interactively
 too.
 SYNCHRONOUS_COMMIT
 Specifies whether transaction commit will wait for WAL records to be
 written to disk before the command returns a "success" indication to
 the client. This is equivalent to setting the synchronous_commit
 directive in the postgresql.conf file. This is only used when you
 load data directly to PostgreSQL; the default is off to disable
 synchronous commit to gain speed at writing data. Some modified
 versions of PostgreSQL, like Greenplum, do not have this setting, so
 in this case set this directive to 1, and ora2pg will not try to
 change the setting.
 PG_INITIAL_COMMAND
 This directive can be used to send an initial command to PostgreSQL,
 just after the connection. For example to set some session
 parameters. This directive can be used multiple times.
 INSERT_ON_CONFLICT
 When enabled this instructs Ora2Pg to add an ON CONFLICT DO NOTHING
 clause to all INSERT statements generated for this type of data
 export.
 Column type control
 PG_NUMERIC_TYPE
 If set to 1, replace portable numeric type with PostgreSQL internal
 type. Oracle data type NUMBER(p,s) is approximately converted to
 real and float PostgreSQL data types. If you have monetary fields or
 don't want rounding issues with the extra decimals, you should
 preserve the same numeric(p,s) PostgreSQL data type. Do this only if
 you need exactness because using numeric(p,s) is slower than using
 real or double.
 PG_INTEGER_TYPE
 If set to 1, replace portable numeric type with PostgreSQL internal
 type. Oracle data types NUMBER(p) or NUMBER are converted to
 smallint, integer or bigint PostgreSQL data types following the
 value of the precision. If NUMBER without precision is set to
 DEFAULT_NUMERIC (see below).
 DEFAULT_NUMERIC
 NUMBER without precision is converted by default to bigint only if
 PG_INTEGER_TYPE is true. You can override this value to any PG type,
 like integer or float.
 DATA_TYPE
 If you're experiencing any problems in data type schema conversion,
 with this directive you can take full control of the correspondence
 between Oracle and PostgreSQL types to redefine data type
 translation used in Ora2pg. The syntax is a comma-separated list of
 "Oracle datatype:PostgreSQL datatype". Here is the default list
 used:
 DATA_TYPE VARCHAR2:varchar,NVARCHAR2:varchar,NVARCHAR:varchar,NCHAR:char,DATE:timestamp(0),LONG:text,LONG RAW:bytea,CLOB:text,NCLOB:text,BLOB:bytea,BFILE:bytea,RAW(16):uuid,RAW(32):uuid,RAW:bytea,UROWID:oid,ROWID:oid,FLOAT:double precision,DEC:decimal,DECIMAL:decimal,DOUBLE PRECISION:double precision,INT:integer,INTEGER:integer,REAL:real,SMALLINT:smallint,BINARY_FLOAT:double precision,BINARY_DOUBLE:double precision,TIMESTAMP:timestamp,XMLTYPE:xml,BINARY_INTEGER:integer,PLS_INTEGER:integer,TIMESTAMP WITH TIME ZONE:timestamp with time zone,TIMESTAMP WITH LOCAL TIME ZONE:timestamp with time zone
 The directive and the list definition must be a single line.
 Note that when RAW(16) or RAW(32) columns are found or when the RAW
 column has "SYS_GUID()" as default value, Ora2Pg will automatically
 translate the type of the column into uuid which might be the right
 translation in most cases. In this case data will be automatically
 migrated as PostgreSQL uuid data type provided by the "uuid-ossp"
 extension.
 If you want to replace a type with a precision and scale you need to
 escape the comma with a backslash. For example, if you want to
 replace all NUMBER(*,0) with bigint instead of numeric(38) add the
 following:
 DATA_TYPE NUMBER(*,0円):bigint
 You don't have to repeat all default type conversions. Instead just
 specify the ones you want to rewrite.
 There's a special case with BFILE when they are converted to type
 TEXT - they will just contain the full path to the external file. If
 you set the destination type to BYTEA (the default), Ora2Pg will
 export the content of the BFILE as bytea. The third case is when you
 set the destination type to EFILE - in this case, Ora2Pg will export
 it as an EFILE record: (DIRECTORY, FILENAME). Use the DIRECTORY
 export type to export the existing directories as well as privileges
 on those directories.
 There's no SQL function available to retrieve the path to the BFILE.
 Ora2Pg has to create one using the DBMS_LOB package.
 CREATE OR REPLACE FUNCTION ora2pg_get_bfilename( p_bfile IN BFILE )
 RETURN VARCHAR2
 AS
 l_dir VARCHAR2(4000);
 l_fname VARCHAR2(4000);
 l_path VARCHAR2(4000);
 BEGIN
 dbms_lob.FILEGETNAME( p_bfile, l_dir, l_fname );
 SELECT directory_path INTO l_path FROM all_directories
 WHERE directory_name = l_dir;
 l_dir := rtrim(l_path,'/');
 RETURN l_dir || '/' || l_fname;
 END;
 This function is only created if Ora2Pg found a table with a BFILE
 column and that the destination type is TEXT. The function is
 dropped at the end of the export. This concern both, COPY and INSERT
 export type.
 There's no SQL function available to retrieve BFILE as an EFILE
 record, therefore Ora2Pg needs to create one using the DBMS_LOB
 package.
 CREATE OR REPLACE FUNCTION ora2pg_get_efile( p_bfile IN BFILE )
 RETURN VARCHAR2
 AS
 l_dir VARCHAR2(4000);
 l_fname VARCHAR2(4000);
 BEGIN
 dbms_lob.FILEGETNAME( p_bfile, l_dir, l_fname );
 RETURN '(' || l_dir || ',' || l_fnamei || ')';
 END;
 This function is only created if Ora2Pg finds a table with a BFILE
 column and that the destination type is EFILE. The function is
 dropped at the end of the export. This concerns both COPY and INSERT
 export types.
 To set the destination type, use the DATA_TYPE configuration
 directive:
 DATA_TYPE BFILE:EFILE
 for example.
 The EFILE type is a user defined type created by the PostgreSQL
 extension external_file that can be found here:
 https://github.com/darold/external_file This is a port of the BFILE
 Oracle type to PostgreSQL.
 There's no SQL function available to retrieve the content of a
 BFILE. Ora2Pg needs to create one using the DBMS_LOB package.
 CREATE OR REPLACE FUNCTION ora2pg_get_bfile( p_bfile IN BFILE ) RETURN
 BLOB
 AS
 filecontent BLOB := NULL;
 src_file BFILE := NULL;
 l_step PLS_INTEGER := 12000;
 l_dir VARCHAR2(4000);
 l_fname VARCHAR2(4000);
 offset NUMBER := 1;
 BEGIN
 IF p_bfile IS NULL THEN
 RETURN NULL;
 END IF;
 DBMS_LOB.FILEGETNAME( p_bfile, l_dir, l_fname );
 src_file := BFILENAME( l_dir, l_fname );
 IF src_file IS NULL THEN
 RETURN NULL;
 END IF;
 DBMS_LOB.FILEOPEN(src_file, DBMS_LOB.FILE_READONLY);
 DBMS_LOB.CREATETEMPORARY(filecontent, true);
 DBMS_LOB.LOADBLOBFROMFILE (filecontent, src_file, DBMS_LOB.LOBMAXSIZE, offset, offset);
 DBMS_LOB.FILECLOSE(src_file);
 RETURN filecontent;
 END;
 This function is only created if Ora2Pg finds a table with a BFILE
 column and that the destination type is bytea (the default). The
 function is dropped at the end of the export. This applies to both
 COPY and INSERT export types.
 Regarding ROWID and UROWID, they are converted into OID by "logical"
 default, but this will throw an error during data import. There is
 no equivalent data type so you might want to use the DATA_TYPE
 directive to change the corresponding type in PostgreSQL. You should
 consider replacing this data type with a bigserial (autoincremented
 sequence), text, or uuid data type.
 MODIFY_TYPE
 Sometimes you need to force the destination type. For example, a
 column exported as timestamp by Ora2Pg can be forced into type date.
 Value is a comma-separated list of TABLE:COLUMN:TYPE structures. If
 you need to use commas or spaces inside type definitions, you will
 have to escape them with backslashes.
 MODIFY_TYPE TABLE1:COL3:varchar,TABLE1:COL4:decimal(9,6円)
 The type of table1.col3 will be replaced by varchar and table1.col4
 by decimal with precision and scale.
 If the column's type is a user-defined type, Ora2Pg will autodetect
 the composite type and will export its data using ROW(). Some Oracle
 user-defined types are just arrays of a native types. In this case,
 you may want to transform this column into a simple array of a
 PostgreSQL native type. To do so, just redefine the destination type
 as wanted, and Ora2Pg will also transform the data as an array. For
 example, with the following definition in Oracle:
 CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15);
 CREATE TABLE club (Name VARCHAR2(10),
 Address VARCHAR2(20),
 City VARCHAR2(20),
 Phone VARCHAR2(8),
 Members mem_type
 );
 custom type "mem_type" is just a string array and can be translated
 into the following in PostgreSQL:
 CREATE TABLE club (
 name varchar(10),
 address varchar(20),
 city varchar(20),
 phone varchar(8),
 members text[]
 ) ;
 To do so, just use the directive as follows:
 MODIFY_TYPE CLUB:MEMBERS:text[]
 Ora2Pg will take care to transform all data of this column into the
 correct format. Only arrays of characters and numeric types are
 supported.
 TO_NUMBER_CONVERSION
 By default, Oracle's call to function TO_NUMBER will be translated
 as a cast into numeric. For example, TO_NUMBER('10.1234') is
 converted into PostgreSQL call to_number('10.1234')::numeric. If you
 want, you can cast the call to integer or bigint by changing the
 value of the configuration directive. If you need better control of
 the format, just set it as value, for example: TO_NUMBER_CONVERSION
 99999999999999999999.9999999999 will convert the code above as:
 TO_NUMBER('10.1234', '99999999999999999999.9999999999') Any value of
 the directive that is not numeric, integer or bigint will be taken
 as a mask format. If set to none, no conversion will be done.
 VARCHAR_TO_TEXT
 By default varchar2 without size constraint are translated into
 text. If you want to keep the varchar name, disable this directive.
 FORCE_IDENTITY_BIGINT
 Usually identity columns must be bigint to correspond to an auto
 increment sequence so Ora2Pg always forces it to be a bigint. If,
 for any reason you want Ora2Pg to respect the DATA_TYPE you have set
 for identity columns then disable this directive.
 TO_CHAR_NOTIMEZONE
 If you want Ora2Pg to remove any timezone information from the
 format part of the TO_CHAR() function, enable this directive.
 Disabled by default.
 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_Inter
 face_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#Da
 ta_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.
 Control MySQL export behavior
 MYSQL_PIPES_AS_CONCAT
 Enable this if double pipe and double ampersand (|| and &&) should
 not be taken as equivalent to OR and AND. It depends on the variable
 @sql_mode. Use it only if Ora2Pg fails on auto-detecting this
 behavior.
 MYSQL_INTERNAL_EXTRACT_FORMAT
 Enable this directive if you want EXTRACT() replacement to use the
 internal format returned as an integer, for example DD HH24:MM:SS
 will be replaced with format; DDHH24MMSS::bigint, this depends on
 your apps usage.
 Control SQL Server export behavior
 DROP_ROWVERSION
 PostgreSQL has no equivalent to rowversion datatype and feature. If
 you want to remove these useless columns, enable this directive.
 Columns of datatype 'rowversion' or 'timestamp' will not be
 exported.
 CASE_INSENSITIVE_SEARCH
 Emulate the same behavior of MSSQL with case-insensitive search. If
 the value is citext, it will use the citext data type instead of
 char/varchar/text in tables DDL (Ora2Pg will add a CHECK constraint
 for columns with a precision). Instead of citext, you can also set a
 collation name that will be used in the column definitions. To
 disable case-insensitive search set it to: none.
 SELECT_TOP
 Appends a TOP N clause to the SELECT command used to extract the
 data from SQL Server. This is equivalent to a WHERE ROWNUM < 1000
 clause for Oracle.
 Special options to handle character encoding
 NLS_LANG and NLS_NCHAR
 By default, Ora2Pg will set NLS_LANG to AMERICAN_AMERICA.AL32UTF8
 and NLS_NCHAR to AL32UTF8. It is not recommended to change these
 settings, but in some cases it could be useful. Using your own
 settings with these configuration directives will change the client
 encoding on the Oracle side by setting the environment variables
 $ENV{NLS_LANG} and $ENV{NLS_NCHAR}.
 BINMODE
 By default, Ora2Pg will force Perl to use UTF8 encoding. This is
 done through a call to the Perl pragma:
 use open ':utf8';
 You can override this encoding by using the BINMODE directive. For
 example, you can set it to :locale to use your locale or iso-8859-7.
 It will respectively use:
 use open ':locale';
 use open ':encoding(iso-8859-7)';
 If you have changed the NLS_LANG to non-UTF8 encoding, you might
 want to set this directive. See
 http://perldoc.perl.org/5.14.2/open.html for more information. Most
 of the time, leave this directive commented.
 CLIENT_ENCODING
 By default, PostgreSQL client encoding is automatically set to UTF8
 to avoid encoding issues. If you have changed the value of NLS_LANG,
 you might have to change the encoding of the PostgreSQL client.
 You can take a look at the PostgreSQL supported character sets here:
 http://www.postgresql.org/docs/9.0/static/multibyte.html
 FORCE_PLSQL_ENCODING
 Enable this directive to force UTF8 encoding of the PL/SQL code
 exported. Could be helpful in some rare conditions.
 PLSQL to PLPGSQL conversion
 Automatic code conversion from Oracle PL/SQL to PostgreSQL PL/PGSQL is a
 work in progress in Ora2Pg and you will likely have manual work. The
 Perl code used for automatic conversion is stored in a specific Perl
 Module named Ora2Pg/PLSQL.pm. Feel free to modify/add your own code and
 send me patches. The main work is on function, procedure, package and
 package body headers and parameter rewrites.
 PLSQL_PGSQL
 Enable/disable PLSQL to PLPGSQL conversion. Enabled by default.
 NULL_EQUAL_EMPTY
 Ora2Pg can replace all conditions with a test on NULL by calling the
 coalesce() function to mimic the Oracle behavior where empty strings
 are considered equal to NULL.
 (field1 IS NULL) is replaced by (coalesce(field1::text, '') = '')
 (field2 IS NOT NULL) is replaced by (field2 IS NOT NULL AND field2::text <> '')
 You might want this replacement to ensure your application will have
 the same behavior, but if you have control over your application, a
 better way is to transform empty strings into NULL because
 PostgreSQL differentiates between them.
 EMPTY_LOB_NULL
 Force empty_clob() and empty_blob() to be exported as NULL instead
 of an empty string for the first one and '\x' for the second. If
 NULL is allowed in your column, this might improve data export speed
 if you have lots of empty lobs. Default is to preserve the exact
 data from Oracle.
 PACKAGE_AS_SCHEMA
 If you don't want to export packages as schemas but as simple
 functions, you might also want to replace all calls to
 package_name.function_name. If you disable the PACKAGE_AS_SCHEMA
 directive then Ora2Pg will replace all calls to
 package_name.function_name() with package_name_function_name().
 Default is to use a schema to emulate packages.
 The replacement will be done in all kinds of DDL or code that is
 parsed by the PLSQL to PLPGSQL converter. PLSQL_PGSQL must be
 enabled or -p used in command line.
 REWRITE_OUTER_JOIN
 Enable this directive if the rewrite of Oracle native syntax (+) of
 OUTER JOIN is broken. This will force Ora2Pg to not rewrite such
 code. Default is to try to rewrite simple forms of right outer joins
 for now.
 UUID_FUNCTION
 By default, Ora2Pg will convert calls to SYS_GUID() Oracle function
 with a call to uuid_generate_v4 from the uuid-ossp extension. You
 can redefine it to use the gen_random_uuid function from the
 pgcrypto extension by changing the function name. Default is
 uuid_generate_v4.
 Note that when RAW(16) or RAW(32) columns are found or when the RAW
 column has "SYS_GUID()" as default value, Ora2Pg will automatically
 translate the type of the column into uuid which might be the right
 translation in most cases. In this case data will be automatically
 migrated as PostgreSQL uuid data type provided by the "uuid-ossp"
 extension.
 FUNCTION_STABLE
 By default, Oracle functions are marked as STABLE as they can not
 modify data unless when used in PL/SQL with variable assignment or
 as conditional expressions. You can force Ora2Pg to create these
 functions as VOLATILE by disabling this configuration directive.
 COMMENT_COMMIT_ROLLBACK
 By default, calls to COMMIT/ROLLBACK are kept untouched by Ora2Pg to
 force the user to review the logic of the function. Once it is fixed
 in Oracle source code or you want to comment these calls, enable the
 following directive.
 COMMENT_SAVEPOINT
 It is common to see SAVEPOINT calls inside PL/SQL procedures
 together with a ROLLBACK TO savepoint_name. When
 COMMENT_COMMIT_ROLLBACK is enabled, you may want to also comment
 SAVEPOINT calls; in this case enable it.
 STRING_CONSTANT_REGEXP
 Ora2Pg replaces all string constants during the PL/SQL to PL/PGSQL
 translation. String constants are all text included between single
 quotes. If you have some string placeholders used in dynamic calls
 to queries, you can set a list of regexps to be temporarily replaced
 to not break the parser. For example:
 STRING_CONSTANT_REGEXP <placeholder value=".*">
 The list of regexps must use the semi colon as separator.
 ALTERNATIVE_QUOTING_REGEXP
 To support the Alternative Quoting Mechanism ('Q' or 'q') for String
 Literals, set the regexp with the text capture to use to extract the
 text part. For example, with a variable declared as:
 c_sample VARCHAR2(100 CHAR) := q'{This doesn't work.}';
 the regexp to use must be:
 ALTERNATIVE_QUOTING_REGEXP q'{(.*)}'
 Ora2pg will use the $$ delimiter; for the example the result will
 be:
 c_sample varchar(100) := $$This doesn't work.$$;
 The value of this configuration directive can be a list of regexps
 separated by a semicolon. The capture part (between parentheses) is
 mandatory in each regexp if you want to restore the string constant.
 USE_ORAFCE
 If you want to use functions defined in the Orafce library and
 prevent Ora2Pg from translating calls to these functions, enable
 this directive. The Orafce library can be found here:
 https://github.com/orafce/orafce
 By default, Ora2pg rewrites add_month(), add_year(), date_trunc()
 and to_char() functions, but you may prefer to use the orafce
 version of these functions that do not need any code transformation.
 AUTONOMOUS_TRANSACTION
 Enable translation of autonomous transactions into wrapper functions
 using dblink or pg_background extension. If you don't want to use
 this translation and just want the function to be exported as a
 normal one without the pragma call, disable this directive.
 Materialized View
 Materialized views are exported as snapshot "Snapshot Materialized
 Views" as PostgreSQL only supports full refresh.
 If you want to import materialized views in PostgreSQL prior to 9.3, you
 have to set configuration directive PG_SUPPORTS_MVIEW to 0. In this case
 Ora2Pg will export all materialized views as explained in this document:
 http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views.
 When exporting materialized views, Ora2Pg will first add the SQL code to
 create the "materialized_views" table:
 CREATE TABLE materialized_views (
 mview_name text NOT NULL PRIMARY KEY,
 view_name text NOT NULL,
 iname text,
 last_refresh TIMESTAMP WITH TIME ZONE
 );
 All materialized views will have an entry in this table. It then adds
 the plpgsql code to create three functions:
 create_materialized_view(text, text, text) used to create a materialized view
 drop_materialized_view(text) used to delete a materialized view
 refresh_full_materialized_view(text) used to refresh a view
 Then it adds the SQL code to create the view and the materialized view:
 CREATE VIEW mviewname_mview AS
 SELECT ... FROM ...;
 SELECT create_materialized_view('mviewname','mviewname_mview', change with the name of the column to be used for the index);
 The first argument is the name of the materialized view, the second is
 the name of the view on which the materialized view is based, and the
 third is the column name on which the index should be built (typically
 the primary key). This column is not automatically deduced so you need
 to replace its name.
 As mentioned above, Ora2Pg only supports snapshot materialized views so
 the table will be entirely refreshed by first truncating the table and
 then loading all data again from the view:
 refresh_full_materialized_view('mviewname');
 To drop the materialized view, you just have to call the
 drop_materialized_view() function with the name of the materialized view
 as a parameter.
 Other configuration directives
 DEBUG
 Set it to 1 to enable verbose output.
 IMPORT
 You can define common Ora2Pg configuration directives in a single
 file that can be imported into other configuration files with the
 IMPORT configuration directive as follows:
 IMPORT commonfile.conf
 This will import all configuration directives defined in
 commonfile.conf into the current configuration file.
 Exporting views as PostgreSQL tables
 You can export any Oracle view as a PostgreSQL table simply by setting
 the TYPE configuration option to TABLE to get the corresponding create
 table statement. Or use type COPY or INSERT to export the corresponding
 data. To allow this, you have to specify your views in the VIEW_AS_TABLE
 configuration option.
 Then if Ora2Pg finds the view, it will extract its schema (if
 TYPE=TABLE) into a PG create table form, then it will extract the data
 (if TYPE=COPY or INSERT) following the view schema.
 For example, with the following view:
 CREATE OR REPLACE VIEW product_prices (category_id, product_count, low_price, high_price) AS
 SELECT category_id, COUNT(*) as product_count,
 MIN(list_price) as low_price,
 MAX(list_price) as high_price
 FROM product_information
 GROUP BY category_id;
 Setting VIEW_AS_TABLE to product_prices and using export type TABLE,
 will force Ora2Pg to detect columns' returned types and to generate a
 create table statement:
 CREATE TABLE product_prices (
 category_id bigint,
 product_count integer,
 low_price numeric,
 high_price numeric
 );
 Data will be loaded following the COPY or INSERT export type and the
 view declaration.
 You can use the ALLOW and EXCLUDE directives in addition to filter other
 objects to export.
 Export as Kettle transformation XML files
 The KETTLE export type is useful if you want to use Pentaho Data
 Integrator (Kettle) to import data to PostgreSQL. With this type of
 export, Ora2Pg will generate one XML Kettle transformation file (.ktr)
 per table and add a line to manually execute the transformation in the
 output.sql file. For example:
 ora2pg -c ora2pg.conf -t KETTLE -j 12 -a MYTABLE -o load_mydata.sh
 will generate one file called 'HR.MYTABLE.ktr' and add a line to the
 output file (load_mydata.sh):
 #!/bin/sh
 KETTLE_TEMPLATE_PATH='.'
 JAVAMAXMEM=4096 ./pan.sh -file $KETTLE_TEMPLATE_PATH/HR.MYTABLE.ktr -level Detailed
 The -j 12 option will create a template with 12 processes to insert data
 into PostgreSQL. It is also possible to specify the number of parallel
 queries used to extract data from Oracle with the -J command line option
 as follows:
 ora2pg -c ora2pg.conf -t KETTLE -J 4 -j 12 -a EMPLOYEES -o load_mydata.sh
 This is only possible if there is a unique key defined on a numeric
 column or if you have defined the technical key to be used to split the
 query between cores in the DEFINED_PKEY configuration directive. For
 example:
 DEFINED_PK EMPLOYEES:employee_id
 This will force the number of Oracle connection copies to 4 and define
 the SQL query as follows in the Kettle XML transformation file:
 <sql>SELECT * FROM HR.EMPLOYEES WHERE ABS(MOD(employee_id,${Internal.Step.Unique.Count}))=${Internal.Step.Unique.Number}</sql>
 The KETTLE export type requires that the Oracle and PostgreSQL DSN are
 defined. You can also activate the TRUNCATE_TABLE directive to force a
 truncation of the table before data import.
 The KETTLE export type is an original work by Marc Cousin.
 Migration Cost Assessment
 Estimating the cost of migrating from Oracle to PostgreSQL is not easy.
 To obtain a good assessment of this migration cost, Ora2Pg will inspect
 all database objects, all functions and stored procedures to detect if
 there are any objects and PL/SQL code that can not be automatically
 converted by Ora2Pg.
 Ora2Pg has a content analysis mode that inspects the Oracle database to
 generate a text report on what the Oracle database contains and what
 cannot be exported.
 To activate the "analysis and report" mode, you have to use the export
 type SHOW_REPORT with in the following command:
 ora2pg -t SHOW_REPORT
 Here is a sample report obtained with this command:
 --------------------------------------
 Ora2Pg: Oracle Database Content Report
 --------------------------------------
 Version Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
 Schema HR
 Size 880.00 MB
 
 --------------------------------------
 Object Number Invalid Comments
 --------------------------------------
 CLUSTER 2 0 Clusters are not supported and will not be exported.
 FUNCTION 40 0 Total size of function code: 81992.
 INDEX 435 0 232 index(es) are concerned by the export, others are automatically generated and will
 do so on PostgreSQL. 1 bitmap index(es). 230 b-tree index(es). 1 reversed b-tree index(es)
 Note that bitmap index(es) will be exported as b-tree index(es) if any. Cluster, domain,
 bitmap join and IOT indexes will not be exported at all. Reverse indexes are not exported
 too, you may use a trigram-based index (see pg_trgm) or a reverse() function based index
 and search. You may also use 'varchar_pattern_ops', 'text_pattern_ops' or 'bpchar_pattern_ops'
 operators in your indexes to improve search with the LIKE operator respectively into
 varchar, text or char columns.
 MATERIALIZED VIEW 1 0 All materialized view will be exported as snapshot materialized views, they
 are only updated when fully refreshed.
 PACKAGE BODY 2 1 Total size of package code: 20700.
 PROCEDURE 7 0 Total size of procedure code: 19198.
 SEQUENCE 160 0 Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL
 will be transformed into NEXTVAL('sequence_name') or CURRVAL('sequence_name').
 TABLE 265 0 1 external table(s) will be exported as standard table. See EXTERNAL_TO_FDW configuration
 directive to export as file_fdw foreign tables or use COPY in your code if you just
 want to load data from external files. 2 binary columns. 4 unknown types.
 TABLE PARTITION 8 0 Partitions are exported using table inheritance and check constraint. 1 HASH partitions.
 2 LIST partitions. 6 RANGE partitions. Note that Hash partitions are not supported.
 TRIGGER 30 0 Total size of trigger code: 21677.
 TYPE 7 1 5 type(s) are concerned by the export, others are not supported. 2 Nested Tables.
 2 Object type. 1 Subtype. 1 Type Boby. 1 Type inherited. 1 Varrays. Note that Type
 inherited and Subtype are converted as table, type inheritance is not supported.
 TYPE BODY 0 3 Export of type with member method are not supported, they will not be exported.
 VIEW 7 0 Views are fully supported, but if you have updatable views you will need to use
 INSTEAD OF triggers.
 DATABASE LINK 1 0 Database links will not be exported. You may try the dblink perl contrib module or use
 the SQL/MED PostgreSQL features with the different Foreign Data Wrapper (FDW) extensions.
 
 Note: Invalid code will not be exported unless the EXPORT_INVALID configuration directive is activated.
 Once the database has been analysed, Ora2Pg, through its ability to
 convert SQL and PL/SQL code from Oracle syntax to PostgreSQL, can go
 further by estimating the code complexity and time necessary to perform
 a full database migration.
 To estimate the migration cost in person-days, Ora2Pg allows you to use
 a configuration directive called ESTIMATE_COST that you can also enable
 at command line:
 --estimate_cost
 This feature can only be used with the SHOW_REPORT, FUNCTION, PROCEDURE,
 PACKAGE and QUERY export types.
 ora2pg -t SHOW_REPORT --estimate_cost
 The generated report is the same as above but with a new 'Estimated
 cost' column as follows:
 --------------------------------------
 Ora2Pg: Oracle Database Content Report
 --------------------------------------
 Version Oracle Database 10g Express Edition Release 10.2.0.1.0
 Schema HR
 Size 890.00 MB
 
 --------------------------------------
 Object Number Invalid Estimated cost Comments
 --------------------------------------
 DATABASE LINK 3 0 9 Database links will be exported as SQL/MED PostgreSQL's Foreign Data Wrapper (FDW) extensions
 using oracle_fdw.
 FUNCTION 2 0 7 Total size of function code: 369 bytes. HIGH_SALARY: 2, VALIDATE_SSN: 3.
 INDEX 21 0 11 11 index(es) are concerned by the export, others are automatically generated and will do so
 on PostgreSQL. 11 b-tree index(es). Note that bitmap index(es) will be exported as b-tree
 index(es) if any. Cluster, domain, bitmap join and IOT indexes will not be exported at all.
 Reverse indexes are not exported too, you may use a trigram-based index (see pg_trgm) or a
 reverse() function based index and search. You may also use 'varchar_pattern_ops', 'text_pattern_ops'
 or 'bpchar_pattern_ops' operators in your indexes to improve search with the LIKE operator
 respectively into varchar, text or char columns.
 JOB 0 0 0 Job are not exported. You may set external cron job with them.
 MATERIALIZED VIEW 1 0 3 All materialized view will be exported as snapshot materialized views, they
 are only updated when fully refreshed.
 PACKAGE BODY 0 2 54 Total size of package code: 2487 bytes. Number of procedures and functions found
 inside those packages: 7. two_proc.get_table: 10, emp_mgmt.create_dept: 4,
 emp_mgmt.hire: 13, emp_mgmt.increase_comm: 4, emp_mgmt.increase_sal: 4,
 emp_mgmt.remove_dept: 3, emp_mgmt.remove_emp: 2.
 PROCEDURE 4 0 39 Total size of procedure code: 2436 bytes. TEST_COMMENTAIRE: 2, SECURE_DML: 3,
 PHD_GET_TABLE: 24, ADD_JOB_HISTORY: 6.
 SEQUENCE 3 0 0 Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL
 will be transformed into NEXTVAL('sequence_name') or CURRVAL('sequence_name').
 SYNONYM 3 0 4 SYNONYMs will be exported as views. SYNONYMs do not exists with PostgreSQL but a common workaround
 is to use views or set the PostgreSQL search_path in your session to access
 object outside the current schema.
 user1.emp_details_view_v is an alias to hr.emp_details_view.
 user1.emp_table is an alias to hr.employees@other_server.
 user1.offices is an alias to hr.locations.
 TABLE 17 0 8.5 1 external table(s) will be exported as standard table. See EXTERNAL_TO_FDW configuration
 directive to export as file_fdw foreign tables or use COPY in your code if you just want to
 load data from external files. 2 binary columns. 4 unknown types.
 TRIGGER 1 1 4 Total size of trigger code: 123 bytes. UPDATE_JOB_HISTORY: 2.
 TYPE 7 1 5 5 type(s) are concerned by the export, others are not supported. 2 Nested Tables. 2 Object type.
 1 Subtype. 1 Type Boby. 1 Type inherited. 1 Varrays. Note that Type inherited and Subtype are
 converted as table, type inheritance is not supported.
 TYPE BODY 0 3 30 Export of type with member method are not supported, they will not be exported.
 VIEW 1 1 1 Views are fully supported, but if you have updatable views you will need to use INSTEAD OF triggers.
 --------------------------------------
 Total 65 8 162.5 162.5 cost migration units means approximatively 2 man day(s).
 The last line shows the total estimated migration cost in person-days
 following the number of migration units estimated for each object. Each
 migration unit represents approximately five minutes for a PostgreSQL
 expert. If this is your first migration, you can increase it with the
 configuration directive COST_UNIT_VALUE or the --cost_unit_value command
 line option:
 ora2pg -t SHOW_REPORT --estimate_cost --cost_unit_value 10
 Ora2Pg is also able to give you a migration difficulty level assessment.
 Here's a sample:
 Migration level: B-5
 Migration levels:
 A - Migration that might be run automatically
 B - Migration with code rewrite and a person-days cost up to 5 days
 C - Migration with code rewrite and a person-days cost above 5 days
 Technical levels:
 1 = trivial: no stored functions and no triggers
 2 = easy: no stored functions but with triggers, no manual rewriting
 3 = simple: stored functions and/or triggers, no manual rewriting
 4 = manual: no stored functions but with triggers or views with code rewriting
 5 = difficult: stored functions and/or triggers with code rewriting
 This assessment consists of a letter A or B to specify whether the
 migration needs manual rewriting or not, and a number from 1 up to 5 to
 indicate a technical difficulty level. You have an additional option
 --human_days_limit to specify the number of person-days limit where the
 migration level should be set to C to indicate that it needs a huge
 amount of work and full project management with migration support.
 Default is 10 person-days. You can use the configuration directive
 HUMAN_DAYS_LIMIT to change this default value permanently.
 This feature has been developed to help you or your boss to decide which
 database to migrate first and the team that must be mobilized to conduct
 the migration.
 Global Oracle and MySQL migration assessment
 Ora2Pg comes with a script ora2pg_scanner that can be used when you have
 a huge number of instances and schemas to scan for migration assessment.
 Usage: ora2pg_scanner -l CSVFILE [-o OUTDIR]
 -b | --binpath DIR: full path to directory where the ora2pg binary resides.
 Might be useful only on Windows OS.
 -c | --config FILE: set custom configuration file to use, otherwise ora2pg
 will use the default: /etc/ora2pg/ora2pg.conf.
 -l | --list FILE : CSV file containing a list of databases to scan with
 all required information. The first line of the file
 can contain the following header that describes the
 format that must be used:
 "type","schema/database","dsn","user","password"
 -o | --outdir DIR : (optional) by default all reports will be dumped to a
 directory named 'output', it will be created automatically.
 If you want to change the name of this directory, set the name
 at second argument.
 -t | --test : just try all connections by retrieving the required schema
 or database name. Useful to validate your CSV list file.
 -u | --unit MIN : redefine globally the migration cost unit value in minutes.
 Default is taken from the ora2pg.conf (default 5 minutes).
 Here is a full example of a CSV databases list file:
 "type","schema/database","dsn","user","password"
 "MYSQL","sakila","dbi:mysql:host=192.168.1.10;database=sakila;port=3306","root","secret"
 "ORACLE","HR","dbi:Oracle:host=192.168.1.10;sid=XE;port=1521","system","manager"
 "MSSQL","HR","dbi:ODBC:driver=msodbcsql18;server=srv.database.windows.net;database=testdb","system","manager"
 The CSV field separator must be a comma.
 Note that if you want to scan all schemas from an Oracle instance, you just
 have to leave the schema field empty. Ora2Pg will automatically detect all
 available schemas and generate a report for each one. Of course, you need to
 use a connection user with enough privileges to be able to scan all schemas.
 For example:
 "ORACLE","","dbi:Oracle:host=192.168.1.10;sid=XE;port=1521","system","manager"
 "MSSQL","","dbi:ODBC:driver=msodbcsql18;server=srv.database.windows.net;database=testdb","usrname","passwd"
 will generate a report for all schemas in the XE instance. Note that in this
 case the SCHEMA directive in ora2pg.conf must not be set.
 It will generate a CSV file with the assessment result, one line per
 schema or database and a detailed HTML report for each database scanned.
 Hint: Use the -t | --test option beforehand to test all your connections
 in your CSV file.
 For Windows users, you must use the -b command line option to set the
 directory where ora2pg_scanner resides, otherwise the ora2pg command
 calls will fail.
 In the migration assessment details about functions, Ora2Pg always
 includes by default 2 migration units for TEST and 1 unit for SIZE per
 1000 characters in the code. This means that by default it will add 15
 minutes to the migration assessment per function. Obviously, if you have
 unit tests or very simple functions this will not represent the actual
 migration time.
 Migration assessment method
 Migration unit scores given to each type of Oracle database object are
 defined in the Perl library lib/Ora2Pg/PLSQL.pm in the %OBJECT_SCORE
 variable definition.
 The number of PL/SQL lines associated with a migration unit is also
 defined in this file in the $SIZE_SCORE variable value.
 The number of migration units associated with each PL/SQL code
 difficulty can be found in the same Perl library lib/Ora2Pg/PLSQL.pm in
 the hash %UNCOVERED_SCORE initialization.
 This assessment method is a work in progress, so I'm expecting feedback
 on migration experiences to refine the scores/units attributed in these
 variables.
 Improving indexes and constraints creation speed
 Using the LOAD export type and a file containing SQL orders to perform,
 it is possible to dispatch those orders over multiple PostgreSQL
 connections. To be able to use this feature, the PG_DSN, PG_USER and
 PG_PWD must be set. Then:
 ora2pg -t LOAD -c config/ora2pg.conf -i schema/tables/INDEXES_table.sql -j 4
 will dispatch index creation over 4 simultaneous PostgreSQL connections.
 This will considerably accelerate this part of the migration process
 with huge data sizes.
 Exporting LONG RAW
 If you still have columns defined as LONG RAW, Ora2Pg will not be able
 to export these kinds of data. The OCI library fails to export them and
 always returns the same first record. To be able to export the data you
 need to transform the field as BLOB by creating a temporary table before
 migrating data. For example, the Oracle table:
 SQL> DESC TEST_LONGRAW
 Name NULL ? Type
 -------------------- -------- ----------------------------
 ID NUMBER
 C1 LONG RAW
 needs to be "translated" into a table using BLOB as follows:
 CREATE TABLE test_blob (id NUMBER, c1 BLOB);
 And then copy the data with the following INSERT query:
 INSERT INTO test_blob SELECT id, to_lob(c1) FROM test_longraw;
 Then you just have to exclude the original table from the export (see
 EXCLUDE directive) and to rename the new temporary table on the fly
 using the REPLACE_TABLES configuration directive.
 Global variables
 Oracle allows the use of global variables defined in packages. Ora2Pg
 will export these variables for PostgreSQL as user-defined custom
 variables available in a session. Oracle variable assignments are
 exported as calls to:
 PERFORM set_config('pkgname.varname', value, false);
 Use of these variables in the code is replaced by:
 current_setting('pkgname.varname')::global_variables_type;
 where global_variables_type is the type of the variable extracted from
 the package definition.
 If the variable is a constant or has a default value assigned at
 declaration, Ora2Pg will create a file global_variables.conf with the
 definition to include in the postgresql.conf file so that their values
 will already be set at database connection. Note that the value can
 always be modified by the user so you can not have exactly a constant.
 Hints
 Converting your queries with Oracle style outer join (+) syntax to ANSI
 standard SQL at the Oracle side can save you a lot of time for the
 migration. You can use TOAD Query Builder to re-write these using the
 proper ANSI syntax, see:
 http://www.toadworld.com/products/toad-for-oracle/f/10/t/9518.aspx
 There's also an alternative with SQL Developer Data Modeler, see
 http://www.thatjeffsmith.com/archive/2012/01/sql-developer-data-modeler-
 quick-tip-use-oracle-join-syntax-or-ansi/
 Toad is also able to rewrite the native Oracle DECODE() syntax into ANSI
 standard SQL CASE statement. You can find some slides about this in a
 presentation given at PgConf.RU:
 http://ora2pg.darold.net/slides/ora2pg_the_hard_way.pdf
 Test the migration
 The type of action called s you to check that all objects from Oracle
 database have been created under PostgreSQL. Of course PG_DSN must be
 set to be able to check PostgreSQL side.
 Note that this feature respects the schema name limitation if
 EXPORT_SCHEMA and SCHEMA or PG_SCHEMA are defined. If only EXPORT_SCHEMA
 is set all schemas from Oracle and PostgreSQL are scanned. You can
 filter to a single schema using SCHEMA and/or PG_SCHEMA but you can not
 filter on a list of schemas. To test a list of schemas you will have to
 repeat the calls to Ora2Pg by specifying a single schema each time.
 For example command:
 ora2pg -t TEST -c config/ora2pg.conf > migration_diff.txt
 Will create a file containing the report of all objects and row count on
 both sides, Oracle and PostgreSQL, with an error section giving you the
 details of the differences for each kind of object. Here is a sample
 result:
 [TEST INDEXES COUNT]
 ORACLEDB:DEPARTMENTS:2
 POSTGRES:departments:1
 ORACLEDB:EMPLOYEES:6
 POSTGRES:employees:6
 [ERRORS INDEXES COUNT]
 Table departments doesn't have the same number of indexes in Oracle (2) and in PostgreSQL (1).
 [TEST UNIQUE CONSTRAINTS COUNT]
 ORACLEDB:DEPARTMENTS:1
 POSTGRES:departments:1
 ORACLEDB:EMPLOYEES:1
 POSTGRES:employees:1
 [ERRORS UNIQUE CONSTRAINTS COUNT]
 OK, Oracle and PostgreSQL have the same number of unique constraints.
 [TEST PRIMARY KEYS COUNT]
 ORACLEDB:DEPARTMENTS:1
 POSTGRES:departments:1
 ORACLEDB:EMPLOYEES:1
 POSTGRES:employees:1
 [ERRORS PRIMARY KEYS COUNT]
 OK, Oracle and PostgreSQL have the same number of primary keys.
 [TEST CHECK CONSTRAINTS COUNT]
 ORACLEDB:DEPARTMENTS:1
 POSTGRES:departments:1
 ORACLEDB:EMPLOYEES:1
 POSTGRES:employees:1
 [ERRORS CHECK CONSTRAINTS COUNT]
 OK, Oracle and PostgreSQL have the same number of check constraints.
 [TEST NOT NULL CONSTRAINTS COUNT]
 ORACLEDB:DEPARTMENTS:1
 POSTGRES:departments:1
 ORACLEDB:EMPLOYEES:1
 POSTGRES:employees:1
 [ERRORS NOT NULL CONSTRAINTS COUNT]
 OK, Oracle and PostgreSQL have the same number of not null constraints.
 [TEST COLUMN DEFAULT VALUE COUNT]
 ORACLEDB:DEPARTMENTS:1
 POSTGRES:departments:1
 ORACLEDB:EMPLOYEES:1
 POSTGRES:employees:1
 [ERRORS COLUMN DEFAULT VALUE COUNT]
 OK, Oracle and PostgreSQL have the same number of column default value.
 [TEST IDENTITY COLUMN COUNT]
 ORACLEDB:DEPARTMENTS:1
 POSTGRES:departments:1
 ORACLEDB:EMPLOYEES:0
 POSTGRES:employees:0
 [ERRORS IDENTITY COLUMN COUNT]
 OK, Oracle and PostgreSQL have the same number of identity column.
 [TEST FOREIGN KEYS COUNT]
 ORACLEDB:DEPARTMENTS:0
 POSTGRES:departments:0
 ORACLEDB:EMPLOYEES:1
 POSTGRES:employees:1
 [ERRORS FOREIGN KEYS COUNT]
 OK, Oracle and PostgreSQL have the same number of foreign keys.
 [TEST TABLE COUNT]
 ORACLEDB:TABLE:2
 POSTGRES:TABLE:2
 [ERRORS TABLE COUNT]
 OK, Oracle and PostgreSQL have the same number of TABLE.
 [TEST TABLE TRIGGERS COUNT]
 ORACLEDB:DEPARTMENTS:0
 POSTGRES:departments:0
 ORACLEDB:EMPLOYEES:1
 POSTGRES:employees:1
 [ERRORS TABLE TRIGGERS COUNT]
 OK, Oracle and PostgreSQL have the same number of table triggers.
 [TEST TRIGGER COUNT]
 ORACLEDB:TRIGGER:2
 POSTGRES:TRIGGER:2
 [ERRORS TRIGGER COUNT]
 OK, Oracle and PostgreSQL have the same number of TRIGGER.
 [TEST VIEW COUNT]
 ORACLEDB:VIEW:1
 POSTGRES:VIEW:1
 [ERRORS VIEW COUNT]
 OK, Oracle and PostgreSQL have the same number of VIEW.
 [TEST MVIEW COUNT]
 ORACLEDB:MVIEW:0
 POSTGRES:MVIEW:0
 [ERRORS MVIEW COUNT]
 OK, Oracle and PostgreSQL have the same number of MVIEW.
 [TEST SEQUENCE COUNT]
 ORACLEDB:SEQUENCE:1
 POSTGRES:SEQUENCE:0
 [ERRORS SEQUENCE COUNT]
 SEQUENCE does not have the same count in Oracle (1) and in PostgreSQL (0).
 [TEST TYPE COUNT]
 ORACLEDB:TYPE:1
 POSTGRES:TYPE:0
 [ERRORS TYPE COUNT]
 TYPE does not have the same count in Oracle (1) and in PostgreSQL (0).
 [TEST FDW COUNT]
 ORACLEDB:FDW:0
 POSTGRES:FDW:0
 [ERRORS FDW COUNT]
 OK, Oracle and PostgreSQL have the same number of FDW.
 [TEST FUNCTION COUNT]
 ORACLEDB:FUNCTION:3
 POSTGRES:FUNCTION:3
 [ERRORS FUNCTION COUNT]
 OK, Oracle and PostgreSQL have the same number of functions.
 [TEST SEQUENCE VALUES]
 ORACLEDB:EMPLOYEES_NUM_SEQ:1285
 POSTGRES:employees_num_seq:1285
 [ERRORS SEQUENCE VALUES COUNT]
 OK, Oracle and PostgreSQL have the same values for sequences
 [TEST ROWS COUNT]
 ORACLEDB:DEPARTMENTS:27
 POSTGRES:departments:27
 ORACLEDB:EMPLOYEES:854
 POSTGRES:employees:854
 [ERRORS ROWS COUNT]
 OK, Oracle and PostgreSQL have the same number of rows.
 Data validation
 Data validation consists of comparing data retrieved from a foreign
 table pointing to the source Oracle table and a local PostgreSQL table
 resulting from the data export.
 To run data validation you can use a direct connection like any other
 Ora2Pg action but you can also use the oracle_fdw, mysql_fdw or tds_fdw
 extension provided that FDW_SERVER and PG_DSN configuration directives
 are set.
 By default, Ora2Pg will extract the first 10000 rows from both sides,
 you can change this value using directive DATA_VALIDATION_ROWS. When it
 is set to zero all rows of the tables will be compared.
 Data validation requires that the table has a primary key or unique
 index and that the key column is not a LOB. Rows will be sorted using
 this unique key. Due to differences in sort behavior between Oracle and
 PostgreSQL, if the collation of unique key columns in PostgreSQL is not
 'C', the sort order can be different compared to Oracle. In this case
 the data validation will fail.
 Data validation must be done before any data is modified.
 Ora2Pg will stop comparing two tables after DATA_VALIDATION_ROWS is
 reached or after 10 errors have been encountered, results are dumped in
 a file named "data_validation.log" written in the current directory by
 default. The number of errors before stopping the diff between rows can
 be controlled using the configuration directive DATA_VALIDATION_ERROR.
 All rows with errors are printed to the output file for your analysis.
 It is possible to parallelize data validation by using -P option or the
 corresponding configuration directive PARALLEL_TABLES in ora2pg.conf.
 Use of System Change Number (SCN)
 Ora2Pg is able to export data as of a specific SCN. You can set it at
 command line using the -S or --scn option. You can give a specific SCN
 or if you want to use the current SCN at first connection time set the
 value to 'current'. In this last case if connection user has the "SELECT
 ANY DICTIONARY" or the "SELECT_CATALOG_ROLE" role, the current SCN is
 looked up in the v$database view.
 Example of use:
 ora2pg -c ora2pg.conf -t COPY --scn 16605281
 This adds the following clause to the query used to retrieve data for
 example:
 AS OF SCN 16605281
 You can also use the --scn option to use the Oracle flashback capability
 by specifying a timestamp expression instead of a SCN. For example:
 ora2pg -c ora2pg.conf -t COPY --scn "TO_TIMESTAMP('2021-12-01 00:00:00', 'YYYY-MM-DD HH:MI:SS')"
 This will add the following clause to the query used to retrieve data:
 AS OF TIMESTAMP TO_TIMESTAMP('2021-12-01 00:00:00', 'YYYY-MM-DD HH:MI:SS')
 or for example to only retrieve yesterday's data:
 ora2pg -c ora2pg.conf -t COPY --scn "SYSDATE - 1"
 Change Data Capture (CDC)
 Ora2Pg does not have a feature which allows importing data and only
 applying changes after the first import. But you can use the --cdc_ready
 option to export data with registration of the SCN at the time of the
 table export. All SCNs per table are written to a file named
 TABLES_SCN.log by default, it can be changed using -C | --cdc_file
 option.
 These SCNs registered per table during COPY or INSERT export can be used
 with a CDC tool. The format of the file is tablename:SCN per line.
 Importing BLOB as large objects
 By default Ora2Pg imports Oracle BLOB as bytea, the destination column
 is created using the bytea data type. If you want to use large objects
 instead of bytea, just add the --blob_to_lo option to the ora2pg
 command. It will create the destination column as data type Oid and will
 save the BLOB as a large object using the lo_from_bytea() function. The
 Oid returned by the call to lo_from_bytea() is inserted in the
 destination column instead of a bytea. Because of the use of the
 function this option can only be used with actions SHOW_COLUMN, TABLE
 and INSERT. Action COPY is not allowed.
 If you want to use COPY or have huge size BLOBs ( > 1GB) than cannot be
 imported using lo_from_bytea() you can add option --lo_import to the
 ora2pg command. This will allow importing data in two passes.
 1) Export data using COPY or INSERT will set the Oid destination column
 for BLOB to value 0 and save the BLOB value into a dedicated file. It
 will also create a Shell script to import the BLOB files into the
 database using psql command \lo_import and to update the table Oid
 column to the returned large object Oid. The script is named
 lo_import-TABLENAME.sh
 2) Execute all scripts lo_import-TABLENAME.sh after setting the
 environment variables PGDATABASE and optionally PGHOST, PGPORT, PGUSER,
 etc. if they do not correspond to the default values for libpq.
 You might also execute manually a VACUUM FULL on the table to remove the
 bloat created by the table update.
 Limitation: the table must have a primary key, it is used to set the
 WHERE clause to update the Oid column after the large object import.
 Importing BLOB using this second method (--lo_import) is very slow so it
 should be reserved for rows where the BLOB > 1GB. For all other rows use
 the option --blob_to_lo. To filter the rows you can use the WHERE
 configuration directive in ora2pg.conf.
SUPPORT
 Author / Maintainer
 Gilles Darold <gilles AT darold DOT net>
 Please report any bugs, patches, help, etc. to <gilles AT darold DOT
 net>.
 Feature Requests
 If you need new features, please let me know at <gilles AT darold DOT
 net>. This helps a lot in developing a better/more useful tool.
 How To Contribute
 Any contribution to build a better tool is welcome. Just send me your
 ideas, features requests or patches and they will be applied.
LICENSE
 Copyright (c) 2000-2026 Gilles Darold - All rights reserved.
 This program is free software: you can redistribute it and/or modify
 it under the terms of the GNU General Public License as published by
 the Free Software Foundation, either version 3 of the License, or
 any later version.
 This program is distributed in the hope that it will be useful,
 but WITHOUT ANY WARRANTY; without even the implied warranty of
 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 GNU General Public License for more details.
 You should have received a copy of the GNU General Public License
 along with this program. If not, see < http://www.gnu.org/licenses/ >.
ACKNOWLEDGEMENTS
 Many thanks to all the great contributors. See changelog for all
 acknowledgments.