doc::Ora2Pg(3pm)

📦

version 25.0 · 2 versions

25.0 · debian trixie (latest)
23.2 · debian bookworm

💖 doc::Ora2Pg(3pm) 💖

okay bestie, this is the User Contributed Perl Documentation for doc::Ora2Pg(3pm) — and no cap, it's giving migration main character energy. ✨

NAME 🎀

Ora2Pg — Oracle to PostgreSQL database schema converter. periodt. 💅

DESCRIPTION 🦋

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scans it automatically and extracts its structure or data, then generates SQL scripts that you can load into your PostgreSQL database. fr fr, that's the whole vibe. 💖

Ora2Pg can be used for anything from reverse engineering 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. lowkey iconic. 🌈

FEATURES ✨

Ora2Pg consist 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 Penthalo 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 purpose. 🧪
MySQL/MariaDB and Microsoft SQL Server migration. 🐬

Ora2Pg does its best to automatically convert your Oracle database to PostgreSQL but there's still manual works 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 a 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 brings you the page for download. 🧚

Releases of Ora2Pg stay at SF.net (https://sourceforge.net/projects/ora2pg/). 💖

Under Windows you should install Strawberry Perl (http://strawberryperl.com/) and the OSes corresponding Oracle clients. Since version 5.32 this Perl distribution include pre-compiled driver of DBD::Oracle and DBD::Pg. 🪟

Requirement 📋

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 and more). To connect to a database and proceed to his migration you need the DBI Perl module > 1.614. To migrate an Oracle database you need the DBD::Oracle Perl modules 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 distribution 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 export 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 export 'on the fly', the perl module DBD::Pg is required. 🐘

Ora2Pg allows you to dump all output in a compressed gzip file, to do that 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

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 relative 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 it is 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 know 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/bin/ and ora2pg.conf into /etc/ora2pg/. 🎀

On Windows(tm) OSes 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 the binary package for your preferred Linux distribution take a look at the packaging/ directory of the source tarball. There is everything to build RPM, Slackware and Debian packages. See README file in that directory. 🐧

Installing DBD::Oracle 🐪

Ora2Pg needs the Perl module DBD::Oracle for connectivity to an Oracle database from perl DBI. To get DBD::Oracle get it from CPAN a perl module repository. 🔗

After setting ORACLE_HOME and LD_LIBRARY_PATH environment variables as root user, install DBD::Oracle. Proceed as follow:

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 give 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 require 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 ⚙️

Ora2Pg configuration can be as simple as choosing the Oracle database to export and choose 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 column for export.
Rename some tables and/or column 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.

okay so how do you actually steer all of this? Bestie, it's all through one single config file called ora2pg.conf 💖 The format is literally just: a directive name in UPPERCASE, then a tab character, then a value. And comments? Any line that starts with #. That's it, that's the whole language. No cap. 🎀

There's no specific order you have to put the config directives in, because they get set the moment they're read in the config file. So you can vibe freely ✨

Now here's the tea on repeat directives, and this matters 🫶:

For directives that take just a single value, you can totally use them multiple times in the config file — but only the last occurrence found in the file wins. So don't be shocked when your earlier ones get ghosted. 💀
For directives that allow a list of values, using them multiple times appends to the list. Slay.
If you use the IMPORT directive to load a custom configuration file, the directives defined in that file get stored from the place the IMPORT directive is found — so lowkey just put it at the end of the configuration file. Trust. 🙈

And the big one: values set in command line options will override values from the configuration file. CLI eats config for breakfast, periodt. 👑

🌸 Ora2Pg usage 🌸

First things first bestie — make sure your libraries and binaries path include the Oracle Instant Client installation, or nothing's gonna work 😭

export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
export PATH="/usr/lib/oracle/11.2/client64/bin:$PATH"

By default Ora2Pg looks for the /etc/ora2pg/ora2pg.conf configuration file. If that file exists you can literally just run:

/usr/bin/ora2pg

Or on Windows(tm), run the ora2pg.bat file, which lives in your perl bin directory. Windows(tm) girlies can also find a template configuration file in C:\ora2pg ✨

Wanna call a different config file? Just hand over the path as a command line argument, easy:

/usr/bin/ora2pg -c /etc/ora2pg/new_ora2pg.conf

And here are all the command line parameters available when you're using ora2pg — the full menu, no skips:

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 will be created under project base dir. (and ngl bestie, the manpage literally prints this one twice — once in a slightly cursed half-rendered form. Here's exactly what it says, verbatim, so you can see the whole thing 👇)

--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. 🦋

Exit statuses, bestie — write these on your hand:

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 occurs during data export. Please check what's happen."

Most of the time this is an OOM issue, 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. ✨

And psst — performance might be improved by updating stats on oracle, like so:

BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS
DBMS_STATS.GATHER_DATABASE_STATS
DBMS_STATS.GATHER_DICTIONARY_STATS
END;

🌸 Generate a migration template 🌸

okay bestie, this is the part where ora2pg basically sets up your whole entire workspace for you 💅 the two options --project_base and --init_project, when used together, tell ora2pg that he has to create a project template with a work tree, a configuration file and a script to export all objects from the Oracle database. here's a lil 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 create a generic config file where you just have to define the Oracle database connection, plus a shell script called export_schema.sh. the sources/ directory will contains the Oracle code, the schema/ will contains the code ported to PostgreSQL, and the reports/ directory will contains the html and json reports with the migration cost assessment. so organized, so iconic ✨

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. no notes 🫶

Once you have set the connection to the Oracle Database you can execute the script export_schema.sh that will export all object type 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 will be done and verified.

You can choose to load the DDL files generated manually, or use the second script import_all.sh to import those file interactively. If this kind of migration is not something current for you, it's recommended you to use those scripts.

🔌 Oracle database connection 🔌

There's 5 configuration directives to control the access to the Oracle database. (yes, and then some more show up, we don't question it, we just read them all 💀)

`ORACLE_HOME`

Used to set ORACLE_HOME environment variable to the Oracle libraries required by the DBD::Oracle Perl module.

`ORACLE_DSN`

This directive is used to set the data source name in the form standard DBI DSN. For example:

dbi:Oracle:host=oradb_host.myhost.com;sid=DB_SID;port=1521

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 lool 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` et `ORACLE_PWD`

These two directives are used to define the user and password for the Oracle database connection. Note that if you can, it is better to login as Oracle super admin to avoid grants problem during the database scan and be sure that nothing is missing. trust, future you will be so grateful 🙏

If you do not supply a credential 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 be asked interactively too.

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 the Oracle database as simple user and do not have enough grants to extract things from the DBA_... tables. It will use tables ALL_... instead.

Warning: if you use export type GRANT, you must set this configuration option to 0 or it will not work. not "might not", not "probably won't" — it will NOT work, periodt 🚨

`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 case this was breaking data consistency, so now default is set to SERIALIZABLE. glow up 💅

`INPUT_FILE`

This directive did not control the Oracle database connection or unless 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 his conversion tool to the content of the file. This can be used with the most of 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. spam it, she doesn't mind 🎀

🔐 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 you extract the 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 talk to the database will be encrypted if you setup session encryption like above. 🔒

For example, Perl's DBI uses DBD-Oracle, which uses the Oracle client for actually handling database communication. If the installation of Oracle client 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 take place here, the other configuration steps are more technical. this is your vibe check, do not skip it 🦋

😭 Troubleshooting 😭

If the output.sql file has not exported anything other than the Pg transaction header and footer there's two possible reasons. The perl script ora2pg dump an ORA-XXX error, that mean that your DSN or login information are wrong, check the error and your settings and try again. The perl script says nothing and the output file is empty: the user lacks permission to extract something from the database. Try to connect to Oracle as super user or take a look at directive USER_GRANTS above and at next section, especially the SCHEMA directive.

`LOGFILE`

By default all messages are sent to the standard output. If you give a file path to that 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 can be mandatory following 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 to the APPS schema.

When no schema name is provided and EXPORT_SCHEMA is enabled, Ora2Pg will export all objects from all schema 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 top of 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 starting of the output file. It is enable by default and concern on 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 recompile invalid object in a specific schema, set COMPILE_SCHEMA to the schema name you want to recompile. recompile era, let's go 🔥

Okay bestie, we're back in the config directives and there's still SO much good stuff to get through, buckle up 🎀

This one makes Ora2Pg go ask Oracle to validate the PL/SQL that could've gotten invalidated after, say, an export/import. The VALID or INVALID status applies to functions, procedures, packages and user defined types. It also concerns disabled triggers. 💅

EXPORT_INVALID — if the config directive right above this one isn't enough to validate your PL/SQL code, turn this on and it'll allow export of all PL/SQL code even when it's marked as invalid. Same deal as before: the VALID or INVALID status applies to functions, procedures, packages, triggers and user defined types.
PG_SCHEMA — lets you define/force which PostgreSQL schema gets used. By default, if you set EXPORT_SCHEMA to 1, the PostgreSQL search_path gets set to the schema name exported, set as the value of the SCHEMA directive.

The value can be a comma delimited list of schema names, but not when you're using the TABLE export type, because in that case it generates the CREATE SCHEMA statement and that doesn't support multiple schema names. So like, if you set PG_SCHEMA to something like "user_schema, public", the search path gets set like this:

SET search_path = user_schema, public;

which forces the use of another schema (here user_schema) instead of the one from the Oracle schema set in the SCHEMA directive.

You can also just set the default search_path for the PostgreSQL user you're connecting with to the destination database, like:

ALTER ROLE username SET search_path TO user_schema, public;

and in that case you don't have to set PG_SCHEMA at all. iconic, honestly. ✨

SYSUSERS — without an explicit schema, Ora2Pg will export all objects that don't belong to a system schema or role:

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 might have several other system roles defined. To append those 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. easy as that 🫶

FORCE_OWNER — by default the owner of the database objects is whoever you're using to connect to PostgreSQL with the psql command. If you use a different user (like postgres), you can force Ora2Pg to set the object owner to the one used in the Oracle database by setting this 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 as set in Oracle, and it's 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, indexes, constraints and indexes using the tablespace name defined in the Oracle database. Heads up though: this only works with tablespaces that are not TEMP, USERS and SYSTEM. 💀
WITH_OID — activating this directive forces Ora2Pg to add WITH (OIDS) when creating tables or views as tables. Default is the same as PostgreSQL: disabled.
LOOK_FORWARD_FUNCTION — a list of schemas to get functions/procedures meta information that are used in the current schema export. When replacing calls to a function with OUT parameters, if a function is declared in another package then the function call rewriting can't be done, because Ora2Pg only knows about functions declared in the current schema. By setting a comma separated list of schemas as the value of this directive, Ora2Pg will look forward in these packages for all functions/procedures/packages declarations before proceeding to the current schema export. lookahead but make it SQL ✨
NO_FUNCTION_METADATA — forces Ora2Pg to not look for function declarations. Note that this will prevent Ora2Pg from rewriting function replacement calls if needed. Don't enable it unless looking forward at functions breaks another export. fr fr, leave it alone unless you have to 🙈

🌈 Export type 🌈

The export action gets performed following a single configuration directive, TYPE, and a few others add more control over what should actually 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 sequence and their last position.
- TABLESPACE: Extract storage spaces for tables and indexes (Pg >= v8).
- TRIGGER: Extract triggers defined following actions.
- FUNCTION: Extract functions.
- PROCEDURE: Extract procedures.
- PACKAGE: Extract packages and package bodies.
- INSERT: Extract data as INSERT statement.
- COPY: Extract data as COPY statement.
- PARTITION: Extract range and list Oracle partitions with subpartitions.
- TYPE: Extract user defined Oracle type.
- FDW: Export Oracle tables as foreign table for Oracle, MySQL and SQL Server FDW.
- MVIEW: Export materialized view.
- QUERY: Try to automatically convert Oracle SQL queries.
- KETTLE: Generate XML ktr template files to be used 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 database.
- TEST_COUNT: perform a row count diff between Oracle and PostgreSQL table.
- TEST_VIEW: perform a count on both side of number of rows returned by views.
- TEST_DATA: perform data validation check on rows at both sides.
- SEQUENCE_VALUES: export DDL to set the last values of sequences

Only one type of export can be performed at the same time, so the TYPE directive must be unique. If you have more than one, only the last one found in the file gets registered. not me putting five TYPE lines and wondering why only one worked 😭

Some export types can't or shouldn't be loaded directly into the PostgreSQL database and still require a little manual editing. That's the case for GRANT, TABLESPACE, TRIGGER, FUNCTION, PROCEDURE, TYPE, QUERY and PACKAGE export types, especially if you have PLSQL code or Oracle specific SQL in there.

For TABLESPACE you must make sure the file path exists on the system, and for SYNONYM you may want to make sure the object's owners and schemas correspond to the new PostgreSQL database design.

Note that you can chain multiple exports by giving 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, trigger and functions. See the doc on the Pg site: 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 the Oracle user type as-is, otherwise 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's three new export types:

SHOW_VERSION : display Oracle version
SHOW_SCHEMA  : display the list of schema 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's PostgreSQL reserved
        words in Oracle object names.

Here's an example of the SHOW_COLUMN output:

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

Those extraction keywords are used to only display the requested information and then exit. This lets you quickly know what you're going to be working on. such a vibe check on your own database 💅

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 lets you export your Oracle, MySQL or MSSQL table definition 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 follow:

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

and just like that bestie, that table is now yours to query like any regular PostgreSQL table 💅 no special treatment, no extra steps, it just works.

Release 10 also dropped a whole new export type whose entire job is to look at your database and tell you what you're actually working with — objects and the cost to finish the migration. It's giving ✨assessment era✨:

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

Here's a sample of report: http://ora2pg.darold.net/report.html

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

🤑 `ESTIMATE_COST`

Turn on the migration cost evaluation. Heads up though — it can only be used with SHOW_REPORT, FUNCTION, PROCEDURE, PACKAGE and QUERY export type. Default is disabled. If you'd rather flip it on from the command line, the --estimate_cost option is right there waiting for you. Also, tiny but important: enabling this directive will force PLSQL_PGSQL activation. Like, it just does that. 🫶

💸 `COST_UNIT_VALUE`

Sets the value, in minutes, of the migration cost evaluation unit. Default is five minutes per unit. Wanna change the unit value from the command line instead? That's --cost_unit_value's whole thing.

🌸 `DUMP_AS_HTML`

Normally, when you use SHOW_REPORT, the migration report comes out as plain simple text. Flip this directive on and ora2pg will be forced to make the report in HTML format instead. Fancy.

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

🦋 `DUMP_AS_JSON`

Same energy as above but make it JSON 🎀 By default, when you use SHOW_REPORT, the migration report is generated as simple text — enabling this directive forces ora2pg to create the report in JSON format. See http://ora2pg.darold.net/report.html for a sample report.

🍓 `DUMP_AS_CSV`

You already know the pattern bestie — by default with SHOW_REPORT the report is simple text, and enabling this directive forces ora2pg to create the 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 goes to stdout. Enable this directive alongside the DUMP_AS_* directives and ora2pg will be forced to create report files with the given extensions and formats instead. The whole point of this option? It lets you combine multiple DUMP_AS_* formats at once. Obsessed honestly.

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 has to switch from B to C. Default is set to 10 person-days.

🧚 `JOBS`

This config directive adds multiprocess support to COPY, FUNCTION and PROCEDURE export type — the value is literally the number of processes to use. Default is multiprocess disable.

You use it to set the number of cores used to parallelize data import into PostgreSQL. During FUNCTION or PROCEDURE export type, each function gets translated to plpgsql using a new process, and the performance gain can be very important when you've got tons of functions to convert. Like, the way that scales 😭

There's no limitation on parallel processing beyond the number of cores and the PostgreSQL I/O performance capabilities.

⚠️ Doesn't work under Windows Operating System — it's simply disabled there. 💀

👑 `ORACLE_COPIES`

This config directive adds multiprocess support to extracting data from Oracle. The value is the number of processes used to parallelize the select query. Default is parallel query disable.

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

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

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

⚠️ Doesn't work under Windows Operating System — it's simply disabled. 💀

🔑 `DEFINED_PK`

This directive defines the technical key to use to split the query between the number of cores set with the ORACLE_COPIES variable. For example:

DEFINED_PK      EMPLOYEES:employee_id

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

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

where N is the current process forked starting from 0.

🌈 `PARALLEL_TABLES`

This directive defines 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. When this directive is upper than 1, it will invalidate ORACLE_COPIES but not JOBS — so the real number of processes that will be used is PARALLEL_TABLES * JOBS.

Also note: when set upper than 1, it will automatically enable the FILE_PER_TABLE directive if you're exporting to files. That's what lets you export tables and views in separate files.

Use PARALLEL_TABLES to use parallelism with COPY, INSERT and TEST_DATA actions. It's 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 the /*+ PARALLEL(tbname, degree) */ hint in each query used to export data from Oracle by setting a value upper than 1 to this directive. A value of 0 or 1 disables the use of the parallel hint. Default is disabled.

🛰️ `FDW_SERVER`

This directive sets the name of the foreign data server used in the CREATE SERVER name FOREIGN DATA WRAPPER <fdw_extension> ... command. That name then gets used in the CREATE FOREIGN TABLE ... SQL commands and to import data using oracle_fdw. Default is no foreign server defined. This only concerns export type FDW, COPY and INSERT. For export type FDW the default value is orcl.

🗂️ `FDW_IMPORT_SCHEMA`

The schema where foreign tables for data migration will be created. If you run 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 behaviour 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 the oracle_fdw documentation for the current default. iykyk 💅

🔀 `ORACLE_FDW_COPY_MODE`

When using Ora2Pg COPY with oracle_fdw, it's possible to use two different modes:

"local" — uses psql on the host running Ora2Pg for the "TO" binary stream.
"server" — 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 aren't expected to support use of "server" mode due to permissions. The default is "local" since it's compatible with more configurations. 🫶

📦 `ORACLE_FDW_COPY_FORMAT`

When using Ora2Pg COPY with oracle_fdw, you can use either BINARY or CSV data format. BINARY provides better performance, however it requires exact data type matching between the FDW and the 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 you to export 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 get extracted in this format: 01-JAN-77 12.00.00.000000 AM. It's impossible to know the exact century that must be used, so by default any year below 49 will be added to 2000 and the others to 1900. You can use this directive to change that default value of 49. This is only relevant if you have a user defined type with a column timestamp. Bestie, time is a construct 🫠

🕵️ `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 only used 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 BLOB takes time, and in some circumstances you might want to export all the data except the BLOB columns. In that case, disable this directive and the BLOB columns won't be included in the data export. Take care that the target bytea column doesn't have a NOT NULL constraint though — that would be a bad time 😭

🫧 `ENABLE_CLOB_EXPORT`

Same behavior as ENABLE_BLOB_EXPORT but for CLOB.

📊 `DATA_EXPORT_ORDER`

By default the data export order is done by sorting on table name. If you've got huge tables at the end of the alphabetic order and you're 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 that case, set this directive to size. Possible values are name and size. Note that export type SHOW_TABLE and SHOW_COLUMN will use this sort order too, not only COPY or INSERT export type. And if you want to give your own custom export order, just give a filename as the value — it contains the ordered list of tables to export. It must be a list of one table per line, in uppercase for Oracle.

🚫 Limiting objects to export 🚫

Okay bestie, sometimes you don't want the WHOLE Oracle database, you just want a lil slice of it 🍰. Here's your control panel for deciding exactly which parts get exported and which parts get left on read.

ALLOW

This directive lets you set a list of objects that the export must be limited to, excluding all other objects in 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 name EMPLOYEES, COUNTRIES, all objects beginning with 'SALE_' and all objects with a name ending by '_GEOM_SEQ'. The object depends of the export type. Note that regex will not works with 8i database, you must use the % placeholder instead, Ora2Pg will use the LIKE operator.

This is the manner to declare global filters that will be used with the current export type. You can also use extended filters that will be applied on 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 trigger 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 index beginning with 'emp_' and the CHECK constraint called 'emp_salary_min'.

When exporting partition 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 year 1980 to 1999 from the export but not the main partition table. The trigger will also be adapted to exclude those table.

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'

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. iykyk 💅

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

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 allow you to define a space or comma-separated list of object name to exclude from the export. You can include valid regex into the list. For example:

EXCLUDE         EMPLOYEES TMP_.* COUNTRIES

will exclude object with name EMPLOYEES, COUNTRIES and all tables beginning with 'tmp_'.

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

EXCLUDE         write_to_.* send_mail_.*

this example will exclude all functions, procedures or functions in a package with the name beginning with those regex. Note that regex will not work with 8i database, you must use the % placeholder instead, Ora2Pg will use the NOT LIKE operator.

See above (directive 'ALLOW') for the extended syntax.

NO_EXCLUDED_TABLE

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

VIEW_AS_TABLE

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

See chapter "Exporting views as PostgreSQL table" for more details.

MVIEW_AS_TABLE

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

NO_VIEW_ORDERING

By default Ora2Pg try to order views to avoid error 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 privilege will be exported. Default is export for all objects. Here are the possibles values TABLE, VIEW, MATERIALIZED VIEW, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE BODY, TYPE, SYNONYM, DIRECTORY. Only one object type is allowed at a time. For example set it to TABLE if you just want to export privilege on tables. You can use the -g option to overwrite it.

When used this directive prevent the export of users unless it is set to USER. In this case only users definitions are exported.

WHERE

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

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

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

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

Any where clause not included into a table name bracket clause will be applied to all exported table including the tables defined in the where clause. These WHERE clauses are very useful if you want to archive some data or at the opposite 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 is the same.

TOP_MAX

This directive is used to limit the number of item 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 received 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

Sometime 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 do 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 table to extract by replacing the Ora2Pg query, you can define multiple REPLACE_QUERY lines.

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

🔍 Control of Full Text Search export 🔍

Several directives can be used to control the way Ora2Pg will export the Oracle's Text search indexes. By default CONTEXT indexes will be exported to PostgreSQL FTS indexes but CTXCAT indexes will be exported as indexes using the pg_trgm extension. slay ✨

CONTEXT_AS_TRGM

Force Ora2Pg to translate Oracle Text indexes into PostgreSQL indexes using pg_trgm extension. Default is to translate CONTEXT indexes into FTS indexes and CTXCAT indexes using pg_trgm. Most of the time using pg_trgm is enough, this is why this directive stand for. You need to create the pg_trgm extension into 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 CONTAIN() clause using to_tsvector(), example:

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

So if you want Ora2Pg to make an extra tsvector column with its own dedicated triggers for FTS indexes, just turn this directive off. In that case Ora2Pg adds the column like this:

ALTER TABLE t_document ADD COLUMN tsv_title tsvector;

Then it updates the column to compute FTS vectors, if the data was already loaded before:

UPDATE t_document
SET tsv_title =
to_tsvector('pg_catalog.french', coalesce(title,''));

And so the column auto-updates whenever something changes in the title column, 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 a weight in the order of the column declaration. weights in declaration order, no cap 💅

FTS_CONFIG

Use this directive to force the text search configuration to use. When it's not set, Ora2Pg autodetects the stemmer Oracle used for each index, and defaults to pg_catalog.english if that info isn't found. autodetect queen ✨

USE_UNACCENT

If you wanna do your text search in an accent-insensitive way, enable this directive. Ora2Pg will create a helper function over unaccent() and build the pg_trgm indexes using that function. With FTS, Ora2Pg will redefine your text search configuration, like 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. lowkey important detail bestie 🎀

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 follow:

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%';

yes literally the same function, or the index just sits there ignored 😭

USE_LOWER_UNACCENT

Same as above but it calls lower() inside 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 usages of Ora2Pg is its flexibility to replicate an Oracle database into a PostgreSQL database with a different structure or schema. There's three configuration directives that let you map those differences. three little icons doing the most 🫶

REORDERING_COLUMNS

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

MODIFY_STRUCT

This directive lets you limit the columns to extract for a given table. The value is a space-separated list of table names with a set of columns between parenthesis, like this:

MODIFY_STRUCT   NOM_TABLE(nomcol1,nomcol2,...) ...

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, the create table DDL will respect the new list of columns, and all indexes or foreign key pointing to or from a removed column will not be exported. poof, gone 💀

EXCLUDE_COLUMNS

Instead of redefining the table structure with MODIFY_STRUCT you may want to exclude some columns from the table export. The value is a space-separated list of table names with a set of columns between parenthesis, like this:

EXCLUDE_COLUMNS NOM_TABLE(nomcol1,nomcol2,...) ...

for example:

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

This will exclude from the export 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, the create table DDL will respect the new list of columns, and all indexes or foreign key pointing to or from a removed column will not be exported. same energy as MODIFY_STRUCT but the opposite vibe 🦋

REPLACE_TABLES

This directive lets you remap a list of Oracle table names to 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 into DEST_TBNAME1 and DEST_TBNAME2. glow up by name change 💅

REPLACE_COLS

Like table names, the name of a column can be remapped to a different name 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 into the new names 'dictionary' and 'folder'. rebranding era 🎀

REPLACE_AS_BOOLEAN

If you want to change the type of some Oracle columns into PostgreSQL boolean during the export you can define here a list of tables and columns separated by space as follow.

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 the 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. original names only, bestie, don't get cute 🙈

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 a precision to automatically convert all fields of that type as a 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. blanket conversion, iconic ✨

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. additive, never destructive 🫶

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. -INFINITY is such a main character move 👑

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 so common but can help. niche but valid 🦋

INDEXES_RENAMING

Enable this directive to rename all indexes using tablename_columns_names. Could be very useful for databases that have multiple times the same index name or that use the same name as a table, which is not allowed by PostgreSQL. Disabled by default. Postgres said no duplicates, periodt 💅

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 the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the database does not use the standard "C" locale. If you enable it, 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 or equal than this value. For example, set it to 128 to create these kind of indexes on columns of type varchar2(N) where N >= 128. character-by-character comparison, no collation drama ✨

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

unique names or Postgres will simply not hear it 😭

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. one big table energy vs. partition girlie behavior, pick your vibe 💜

PARTITION_BY_REFERENCE

okay so this one is literally just "how do we handle partition by reference" 💅 and it takes like three flavors of value: none, duplicate, or a number (specifically, how many HASH partitions you wanna create). Default is none, which means we do not export the partitions by reference. periodt.

none — no translation, no export of partition by reference, same as the old behavior. nothing changes, she's just vibing.
duplicate — this will duplicate the referenced column inside the partitioned table, and then apply the same partitioning from the referenced table onto the partitioned table. twin energy fr fr.
number — the table gets partitioned with the HASH method, and your number is the modulo. so like, if you set it to 4, bestie, you get 4 HASH partitions. math is math 🎀

DISABLE_UNLOGGED

so by default Ora2Pg exports Oracle tables that have the NOLOGGING attribute as UNLOGGED tables. cute, right? 🙈 but you might wanna fully disable this feature because — and I cannot stress this enough — you will lose all your data from unlogged tables if PostgreSQL crashes. like, gone gone. 😭

Set it to 1 to export all tables as normal tables. safety first, bestie 🫶

DOUBLE_MAX_VARCHAR

this one increases the varchar max character constraints so PostgreSQL's two-byte character encoding is supported — specifically for when your source database applies the length constraint on characters and not bytes. Default: disabled. 🦋

Oracle Spatial to PostGis

Ora2Pg fully exports Spatial objects from your Oracle database ✨ no crumbs left. There's a lil handful of configuration directives you can use to control that export. let's get into it:

AUTODETECT_SPATIAL_TYPE

so by default Ora2Pg goes snooping through your indexes to see the spatial constraint type and dimensions defined under Oracle. those constraints get passed along at index creation, like this:

CREATE INDEX ... INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('sdo_indx_dims=2, layer_gtype=point');

if those Oracle constraint parameters aren't set, the default is to export those columns as a generic GEOMETRY type, so it can receive literally any spatial type. flexible queen 💅

AUTODETECT_SPATIAL_TYPE lets you force Ora2Pg to autodetect the real spatial type and dimension used in a spatial column — otherwise you're stuck with a non-constrained geometry type. enabling this makes Ora2Pg scan a sample of 50000 columns to look at the GTYPE being used 👀 and yes, you can bump that sample size up or down by setting the value of AUTODETECT_SPATIAL_TYPE to however many lines you want scanned. The directive is enabled by default.

for example: say you've got a column named shape, defined with Oracle type SDO_GEOMETRY. With AUTODETECT_SPATIAL_TYPE disabled it gets 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. slay 🌈

CONVERT_SRID

this directive lets you 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 SRID. Enabled by default.

if the SDO_SRID returned by Oracle is NULL, it gets replaced by the default value 8307, converted to its EPSG value: 4326 (peep DEFAULT_SRID for that one).

if the value is upper than 1, all SRID will be forced to this value — in that case DEFAULT_SRID will not be used when Oracle returns a null value, and the value will instead be forced to CONVERT_SRID.

also ngl, it's possible to set the EPSG value on the Oracle side when sdo_cs.map_oracle_srid_to_epsg() returns NULL, if you wanna 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 be used: 4326. Can be overwritten by CONVERT_SRID, see above 👆

GEOMETRY_EXTRACT_TYPE

this directive can take three values: WKT (default), WKB and INTERNAL. let's break it down bestie:

when it's set to WKT, Ora2Pg uses SDO_UTIL.TO_WKTGEOMETRY() to extract the geometry data.
when it's set to WKB, Ora2Pg uses the binary output via SDO_UTIL.TO_WKBGEOMETRY().
if those two extract types are called on the Oracle side, they're slow and you can easily hit Out Of Memory when you have a lot of rows. also WKB is not able to export 3D geometry, and it can't do some geometries like CURVEPOLYGON either. 😭 in that case you might wanna use the INTERNAL extraction type — it uses a Pure Perl library to convert the SDO_GEOMETRY data into a WKT representation, and the translation happens on the Ora2Pg side instead.

Heads up: INTERNAL 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 so PostGis functions can be found. 🎀

ST_SRID_FUNCTION

the Oracle function to use for extracting 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

the Oracle function to use for extracting the dimension from ST_Geometry meta information. Default: ST_DIMENSION. for example, it should be set to sde.st_dimention for ArcSDE.

ST_GEOMETRYTYPE_FUNCTION

the Oracle function to use for extracting 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

the Oracle function to use for converting 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

the Oracle function to use for converting 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 gets written to the file output.sql. then the command:

psql mydb < output.sql

will import the content of the file output.sql into your PostgreSQL mydb database. easy peasy 🌸

DATA_LIMIT

when you're doing an INSERT/COPY export, Ora2Pg proceeds by chunks of DATA_LIMIT tuples for speed improvement ⚡ tuples get stored in memory before being written to disk, so if you want speed and you've got enough system resources, you can grow this limit to an upper value — for example: 100000 or 1000000.

before release 7.0, a value of 0 meant no limit, so all tuples were stored in memory before being flushed to disk. in the 7.x branch this has been removed, and the chunk will be set to the default: 10000. rip to a real one 💀

BLOB_LIMIT

when Ora2Pg detects a table with some BLOB in it, 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 yourself. exporting BLOB uses a LOT of resources, so setting it to a too-high value can produce OOM. be careful bestie 😭

CLOB_AS_BLOB

applies the same behavior on CLOB as BLOB with the 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 a .gz or .bz2 extension, 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 file will be written. the directory must exist. no autocreate, she's not doing that for you 💅

BZIP2

this directive lets you specify the full path to the bzip2 program, for when it can't be found in the PATH environment variable. 🔍

FILE_PER_CONSTRAINT

lets object constraints 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 a .gz xor a .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, so you can parallelize their creation over multiple (-j or JOBS) connections. multitasking icon 👑

FILE_PER_INDEX

lets indexes 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 a .gz xor a .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's used to write ALTER INDEX ... TABLESPACE ... into a separate file named TBSP_INDEXES_OUTPUT, which 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, so you can parallelize their creation over multiple (-j or JOBS) connections.

FILE_PER_FKEYS

lets foreign key declarations 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

lets data export 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 a .gz xor a .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

lets functions, procedures and triggers 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 a .gz xor a .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 — and heads up, the package body export has a special behavior 👀

okay so when your export type is PACKAGE and you've flipped this directive on, Ora2Pg makes a directory per package — named with the lowercase name of the package — and drops one file per function/procedure inside it. If it's not enabled, you just get one file per package called packagename_OUTPUT, where OUTPUT is whatever value that matching directive has. 💅

TRUNCATE_TABLE — set it to 1 and a TRUNCATE TABLE instruction gets added before loading data. This only works during INSERT or COPY export type, bestie. And when it's on, that instruction only gets added if there's no global DELETE clause and not one specific to the current table either (peep below 👀).
DELETE — this one lets you include a DELETE FROM ... WHERE clause filter before importing data, so you delete some lines instead of nuking whole tables with a truncate. The value is built like this: TABLE_NAME[DELETE_WHERE_CLAUSE], or — if you've got just one where clause for all the tables — you can just put the delete clause as a single value. And yes, both at once is allowed too. Here's the vibe:

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']

That last one is iconic: it applies two different delete where clauses on TABLE_TEST and TABLE_INFO, plus a generic delete where clause on DATE_CREATE for all the other tables. And if TRUNCATE_TABLE is enabled, it gets applied to all tables not covered by the DELETE definition. These DELETE clauses are lowkey useful with regular "updates". 🫶

STOP_ON_ERROR — set this to 0 and Ora2Pg will not include the call to \set ON_ERROR_STOP ON in any of the SQL scripts it generates. By default this order is always there, so the script instantly aborts the second it hits an error.
COPY_FREEZE — enable this and you get COPY FREEZE instead of a plain COPY, exporting data with rows already frozen. It's a performance option for initial data loading. Heads up though: rows only get frozen if the table being loaded was created or truncated in the current sub-transaction. And it only works with export to file, and when -J or ORACLE_COPIES is not set or defaults to 1. You can also use it with direct import into PostgreSQL under the same conditions, but then -j or JOBS must also be unset or default to 1. no cap.
CREATE_OR_REPLACE — by default Ora2Pg uses CREATE OR REPLACE in functions and views DDL. If you need it to not override existing functions or views, disable this directive and the DDL just won't include OR REPLACE. simple as that 💅
DROP_IF_EXISTS — want a DROP <OBJECT> IF EXISTS slapped in before the object gets created? Enable this. It's handy for iterative work. Default is disabled.
EXPORT_GTT — PostgreSQL doesn't support Global Temporary Table natively, but you can use the pgtt extension to emulate that behavior. Enable this directive to export global temporary tables.
PGTT_NOSUPERUSER — by default the pgtt extension is loaded using superuser privilege. Enable this if you're running the generated SQL scripts as a non-superuser. It'll use:

LOAD '$libdir/plugins/pgtt';

instead of the default:

LOAD 'pgtt';

NO_HEADER — enabling this stops Ora2Pg from printing its header into output files. Only the translated code gets written. clean. ✨
PSQL_RELATIVE_PATH — by default Ora2Pg uses the \i psql command to execute generated SQL files. If you'd rather use a relative path following the script execution file, enable this option and it'll use \ir instead. Check psql help for the deets.
DATA_VALIDATION_ROWS — the 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 the rows.
DATA_VALIDATION_ORDERING — once data has been modified, the order of rows between both sides is different. In that case, data has to be ordered using a primary key or a unique index — which means a table without such an object can't be compared. But if the validation happens right after the data migration with no modification at all, you can validate on all tables with no ordering. 🦋
DATA_VALIDATION_ERROR — stops validating data from a table after a certain amount of row mismatch. Default is to stop after 10 rows validation errors.
TRANSFORM_VALUE — use this to specify exactly which transformation gets applied to a column when exporting data. The value must be a semicolon separated list of:

TABLE[COLUMN_NAME, <replace code in SELECT target list>]

For example, to replace the string 'Oracle' by 'PostgreSQL' in a varchar2 column, do this:

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 gets applied in the SQL statement used to extract data from the source database. 💜

When you're using Ora2Pg export type INSERT or COPY to dump data to file, and FILE_PER_TABLE is enabled, you'll get warned that Ora2Pg will not export data again if the file already exists. This is on purpose — it's to stop you from downloading a table with a huge amount of data twice. To force the download of data from those tables you have to remove the existing output file first. 😭

If you want to import data on the fly into the PostgreSQL database, you've got three configuration directives to set the PostgreSQL database connection. This is only possible with COPY or INSERT export type — for database schema there's genuinely no real interest in doing that.

PG_DSN — use this to set the PostgreSQL data source namespace using the DBD::Pg Perl module, like so:

dbi:Pg:dbname=pgdb;host=localhost;port=5432

That'll connect to database 'pgdb' on localhost at tcp port 5432.

Heads up: 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 an SSL encrypted connection you must add sslmode=require to the connection string, like follow:

dbi:Pg:dbname=pgdb;host=localhost;port=5432;sslmode=require

PG_USER and PG_PWD — these two set the login user and password.

If you don't supply a credential with PG_PWD and you've installed the Term::ReadKey Perl module, Ora2Pg will ask for the password interactively. And if PG_USER isn't set, it'll 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 the equivalent of setting the synchronous_commit directive of the postgresql.conf file. It's only used when you load data directly to PostgreSQL, and the default is off so synchronous commit is disabled to gain speed at writing data. Some modified versions of PostgreSQL, like greenplum, don't 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 one lets you send an initial command to PostgreSQL, right after the connection. For example, to set some session parameters. This directive can be used multiple times.
INSERT_ON_CONFLICT — when enabled, this tells 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, replaces the portable numeric type with the PostgreSQL internal type. Oracle data type NUMBER(p,s) is approximately converted to real and float PostgreSQL data type. If you've got monetary fields or you just don't want rounding issues from the extra decimals, you should preserve the same numeric(p,s) PostgreSQL data type. Only do that if you actually need exactness though, because using numeric(p,s) is slower than using real or double. 🐢
PG_INTEGER_TYPE — if set to 1, replaces the portable numeric type with the PostgreSQL internal type. Oracle data type NUMBER(p) or NUMBER gets converted to smallint, integer or bigint PostgreSQL data type, following the value of the precision. If NUMBER has no precision, it's 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 overwrite this value to any PG type, like integer or float.
DATA_TYPE — if you're experiencing any problem in data type schema conversion, this directive lets you take full control of the correspondence between Oracle and PostgreSQL types, redefining the data type translation used in Ora2Pg. The syntax is a comma-separated list of "Oracle datatype:Postgresql datatype". Here's 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.

Now, note that when a RAW(16) or RAW(32) column is found, or when a RAW column has "SYS_GUID()" as its default value, Ora2Pg will automatically translate the column's type into uuid — which is probably the right translation in most cases. When that happens, data gets 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) into bigint instead of numeric(38), add this:

DATA_TYPE       NUMBER(*\,0):bigint

And bestie, you do not have to recopy all the default type conversions — just the one you want to rewrite. 🦋

There's a lil special case with BFILE, bestie, and it depends on where you're sending it. If they get converted to type TEXT, they'll just contain the full path to the external file. If you set the destination type to BYTEA (which is the default, jsyk), Ora2Pg will export the content of the BFILE as a bytea. And the third case? That's when you set the destination type to EFILE — then Ora2Pg exports it as an EFILE record: (DIRECTORY, FILENAME). Also you can use the DIRECTORY export type to export the existing directories as well as privileges on those directories. Slay. 💅

Okay so there's literally no SQL function available to retrieve the path to the BFILE. So Ora2Pg has to create one using the DBMS_LOB package. Iconic of it honestly.

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 the destination type is TEXT. The function gets dropped at the end of the export. This concerns both COPY and INSERT export type, fr fr.

Now, there's also no SQL function available to retrieve BFILE as an EFILE record, so Ora2Pg has to create one using the DBMS_LOB package too.

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 found a table with a BFILE column and the destination type is EFILE. Dropped at the end of the export, same deal. Concerns both COPY and INSERT export type.

To set the destination type, you 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 — it's a port of the BFILE Oracle type to PostgreSQL, no cap.

And guess what — there's no SQL function available to retrieve the content of a BFILE either. Ora2Pg has to create one using the DBMS_LOB package. Again. 🙈

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 found a table with a BFILE column and the destination type is bytea (the default). It's dropped at the end of the export. Concerns both COPY and INSERT export type. 💖

Okay so about ROWID and UROWID — they're converted into OID by "logical" default, but this will throw an error at 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 by a bigserial (autoincremented sequence), text or uuid data type. That's your sign, bestie. 🫶

✨ `MODIFY_TYPE` ✨

Sometimes you need to force the destination type, like a column exported as timestamp by Ora2Pg can be forced into type date. The value is a comma-separated list of TABLE:COLUMN:TYPE structure. And if you need to use a comma or a space inside the type definition, you'll have to backslash them. 💅

MODIFY_TYPE     TABLE1:COL3:varchar,TABLE1:COL4:decimal(9\,6)

Type of table1.col3 will be replaced by a varchar and table1.col4 by a decimal with precision and scale. Simple as.

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 array of a native type, and in this case you may want to transform this column into 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 follow:

MODIFY_TYPE     CLUB:MEMBERS:text[]

Ora2Pg will take care to transform all data of this column in the correct format. Only arrays of characters and numerics types are supported. Periodt. 👑

🌸 `TO_NUMBER_CONVERSION` 🌸

By default, Oracle 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 it is not numeric, integer or bigint will be taken as a mask format. If set to none, no conversion will be done. Lowkey flexible. ✨

🦋 `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 column 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 column, then disable this directive.

🎀 `TO_CHAR_NOTIMEZONE` 🎀

If you want Ora2Pg to remove any timezone information into 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. Main character directives only. 💅

⭐ `SKIP` ⭐

For TABLE export you may not want to export all schema constraints, so the SKIP configuration directive lets you 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. Bye besties. 👋

🌈 `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 key 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 a ON DELETE CASCADE is defined — or always. Oracle does not support this feature, you have to use a trigger to operate the ON UPDATE CASCADE. Since PostgreSQL has this feature, you can choose how to add the foreign key option. There are three values to this directive:

never — the default, which means foreign keys will be declared exactly like in Oracle.
delete — means the ON UPDATE CASCADE option will be added only if the ON DELETE CASCADE is already defined on the foreign keys.
always — will force all foreign keys to be defined using the update option.

Three vibes, pick your fighter. 💅

💀 `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. Heads up bestie, this one matters. 😭

🧚 `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. Obsessed with the transactional drama of it all. ✨

No cap: if your foreign keys aren't deferrable, or you're doing a direct import to PostgreSQL with PG_DSN defined, this whole thing just won't work. Set it up right or it's giving nothing. 🙈

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 (the TABLE export type). ✨

DROP_FKEY 🔑

If deferring foreign keys just isn't happening for you — maybe there's too much data in a single transaction, maybe you didn't export them as deferrable, maybe you're doing a direct import into PostgreSQL — then bestie, this is your directive. 🫶

It drops all foreign keys before all data import and recreates them at the end of the import. Clean, simple, no cap.

DROP_INDEXES 🗂️

Okay so this one is a whole speedrun hack: it removes all indexes that are not an automatic index (so like, indexes of primary keys) and recreates them at the end of data import. The result? lots of speed improvement during data import. 🏎️✨

And of course, it's far better to not import indexes and constraints before you've imported all the data. Obvi.

DISABLE_TRIGGERS 🔕

This directive disables triggers on all tables in COPY or INSERT export modes. The available values are:

USER — disable user-defined triggers only
ALL — includes RI system triggers

Default is 0: don't add SQL statements to disable triggers before data import.

If you want to disable triggers during data migration, set it to USER if you're connected as a non superuser, and ALL if you're connected as a PostgreSQL superuser. And heads up: a value of 1 is equal to USER. 💅

DISABLE_SEQUENCE 🔢

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

NOESCAPE 🚫

By default, all data that isn't of type date or time gets escaped. If you're experiencing any problem with that, set this 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. iykyk 💜

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 was escaped first — but 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 ✂️

So if you want to convert CHAR(n) from Oracle into varchar(n) or text on PostgreSQL using the DATA_TYPE directive, 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 position. 🌬️

Set the value to LEADING if you just want to remove the leading character set.
Set the value to TRAILING if you just want to remove the trailing character.

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, just comment this directive out. That's 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 on all your SQL scripts. fr fr, don't do this to yourself. 😭

ORA_RESERVED_WORDS 📝

Allow escaping of column names using Oracle reserved words. The 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 a reserved word for PostgreSQL. Ora2Pg will double quote the name of the object. Slay. ✨

GEN_USER_PWD 🎲

Set this directive to 1 to replace the default password with a random password for all extracted users during a GRANT export. Security queen behavior. 👑

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 — and it's enabled by default. 💅

If you want to use the old style with a 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 a value of 0 will prevent Ora2Pg 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 the current major version at time of a new release.

This replaces the old and deprecated PG_SUPPORTS_* configuration directives described below. glow-up 💫

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 more, 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 export. Until PostgreSQL 8.4, such a default value was not supported — this feature is now enabled by default. 🌸

PG_SUPPORTS_WHEN (Deprecated) 💀

Add support for the WHEN clause on triggers, as PostgreSQL v9.0 now supports it. This directive is enabled by default; set it to 0 to disable this feature.

PG_SUPPORTS_INSTEADOF (Deprecated) 💀

Add 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, export 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 it needs to be replaced by a call to substring(). In this case, disable it.

PG_SUPPORTS_NAMED_OPERATOR ➡️

Disable this directive if you're using PG < 9.5. The PL/SQL operator used in named parameter => will be replaced by the PostgreSQL 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 type. 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're exported as IDENTITY columns like:

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

If there is 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 the sequence will be kept to its start value. 😭

Enabled by default.

PG_SUPPORTS_PROCEDURE 📜

PostgreSQL v11 adds support of PROCEDURE — enable it if you use such a version.

BITMAP_AS_GIN 🌈

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

create extension btree_gin;

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

PG_BACKGROUND 🌌

Use the 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 the 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 as follows 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 a 'ORA-24345: A Truncation' error. Default: 1023*1024 bytes.

Take a look at this page to learn more: http://search.cpan.org/~pythian/DBD-Oracle-1.22/Oracle.pm#Data_Interface_for_Persistent_LOBs

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

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`

Turn this off if you want to load the full content of BLOBs and CLOBs and just... not use LOB locators at all. Bestie, if you do that, you have to set LONGREADLEN to the right value, no skipping. Heads up tho: this will not make BLOB export faster, because most of the time is always eaten up by the bytea escaping anyway — and in this mode the export happens line by line instead of in chunks of DATA_LIMIT rows. Wanna know how it actually works under the hood? Go peek at http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm#Data_Interface_for_LOB_Locators 💅

Default is enabled, so it uses LOB locators.

`LOB_CHUNK_SIZE`

So Oracle itself recommends reading from and writing to a LOB in batches using a multiple of the LOB chunk size. That chunk size defaults to 8k (8192). But recent tests have shown that the best performance shows up with higher values, like 512K or 4Mb. 👑

A quick benchmark with 30120 rows of different BLOB sizes (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 of your 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 so you don't 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 the extract method à la CLOB. Note that an XML value extracted with getStringVal() must not exceed the VARCHAR2 size limit (4000), otherwise it will return an error. 🫠

`ENABLE_MICROSECOND`

Set it to O if you want to disable export of the millisecond from Oracle timestamp columns. By default milliseconds are exported using this 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 the comment associated with table and column definitions. 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 — only use it if Ora2Pg fails at auto-detecting this behavior. iykyk.

`MYSQL_INTERNAL_EXTRACT_FORMAT`

Enable this directive if you want the EXTRACT() replacement to use the internal format returned as an integer. For example DD HH24:MM:SS will be replaced with the format DDHH24MMSS::bigint — this depends on your app's usage.

Control SQL Server export behavior 🧊

`DROP_ROWVERSION`

PostgreSQL has no equivalent to the 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`

Emulates the same behavior as MSSQL with case insensitive search. If the value is citext it will use the citext data type instead of char/varchar/text in table DDL (and 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 the 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 those settings, but in some cases it could be useful. Using your own settings with those 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 I/O 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, and it will respectively use:

use open ':locale';
use open ':encoding(iso-8859-7)';

If you have changed NLS_LANG to a non-UTF8 encoding, you might want to set this directive. See http://perldoc.perl.org/5.14.2/open.html for more info. Most of the time, leave this directive commented out, fr fr. 🙈

`CLIENT_ENCODING`

By default the 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`

To force utf8 encoding of the exported PL/SQL code, enable this directive. Could be helpful in some rare conditions.

PLSQL to PLPGSQL conversion 🔄

Automatic code conversion from Oracle PLSQL to PostgreSQL PLPGSQL is a work in progress in Ora2Pg, and bestie, you will surely always have manual work to do. The Perl code used for the automatic conversion is all stored in a specific Perl Module named Ora2Pg/PLSQL.pm — feel free to modify/add your own code and send patches. The main work is on function, procedure, package and package body headers and parameters rewrite. 💖

`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 a call to 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 be sure your application will have the same behavior — but if you have control over your application, a better way is to change it to transform empty strings into NULL, because PostgreSQL makes the difference. 👀

`EMPTY_LOB_NULL`

Forces empty_clob() and empty_blob() to be exported as NULL instead of as 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 a lot of empty lobs. Default is to preserve the exact data from Oracle.

`PACKAGE_AS_SCHEMA`

If you don't want to export a package as a schema 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 the package.

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 on the 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 the simple form of right outer join, for the moment.

`UUID_FUNCTION`

By default Ora2Pg will convert a call to the 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. Defaults to uuid_generate_v4.

Note that when a RAW(16) and RAW(32) column is found, or when the RAW column has "SYS_GUID()" as its 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, since they can not modify data unless when used in PL/SQL with variable assignment or as a conditional expression. 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 the Oracle source code, or if you want to comment out 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 PLPGSQL 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, so the parser doesn't break. For example:

STRING_CONSTANT_REGEXP         <placeholder value=".*">

The list of regexps must use the semicolon 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, so with 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 — bestie listen</p> 💖 if you wanna use the functions that live in the Orafce library and you want Ora2Pg to stop translating calls to them, flip this directive on. The Orafce library hangs out here: https://github.com/orafce/orafce

By default Ora2Pg rewrites add_month(), add_year(), date_trunc() and to_char(), but lowkey you might prefer the orafce versions of those functions since they need zero code transformation ✨

AUTONOMOUS_TRANSACTION — turns autonomous transactions into a wrapper function using the dblink or pg_background extension. If that translation is not your vibe and you just want the function exported as a normal one with no pragma call, disable this directive 🙈

Materialized view 🌸

Materialized views get exported as snapshot "Snapshot Materialized Views", because PostgreSQL only supports full refresh. That's just the rules, no cap.

If you want to import the materialized views into PostgreSQL versions before 9.3 you gotta set the configuration directive PG_SUPPORTS_MVIEW to 0. In that case Ora2Pg will export all materialized views exactly as explained in this document:

http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views

When it's exporting a materialized view, Ora2Pg first drops in 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
);

Every single materialized view gets an entry in this table 🎀 Then it adds the plpgsql code to create tree 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 used for the index);

The first argument is the name of the materialized view, the second is the name of the view that the materialized view is based on, and the third is the column name the index should be built on (aka most of the time, the primary key 👑). This column is not automatically deduced, so you need to replace its name yourself, bestie. Don't forget 💅

Like we said, Ora2Pg only supports snapshot materialized views, so the table gets entirely refreshed by first truncating it and then loading all the data from the view again:

refresh_full_materialized_view('mviewname');

To drop the materialized view you literally just call the drop_materialized_view() function with the name of the materialized view as the parameter. Periodt.

Other configuration directives 🦋

DEBUG — set it to 1 and you get verbose output. it's giving main character who wants to see everything 😭
IMPORT — you can define common Ora2Pg configuration directives inside a single file, and that file can be imported into other configuration files with the IMPORT configuration directive, like this:

IMPORT  commonfile.conf

which imports all the configuration directives defined inside commonfile.conf into the current configuration file. reuse slay ✨

Exporting views as PostgreSQL tables 🌈

You can export any Oracle view as a PostgreSQL table, simply by setting the TYPE configuration option to TABLE so you get the corresponding create table statement. Or use type COPY or INSERT to export the corresponding data. To allow that, 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, and 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 the 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 directive in addition, to filter other objects to export. iykyk 💅

Export as Kettle transformation XML files 🔥

The KETTLE export type is useful if you want to use Penthalo Data Integrator (Kettle) to import data into 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 the Oracle with the -J command line option, like this:

ora2pg -c ora2pg.conf -t KETTLE -J 4 -j 12 -a EMPLOYEES -o load_mydata.sh

This is only possible if there's a unique key defined on a numeric column, or if you have defined the technical key to use to split the query between cores in the DEFINED_PKEY configuration directive. For example:

DEFINED_PK      EMPLOYEES:employee_id

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 of Marc Cousin. 🫶

Migration cost assessment 💀

Estimating the cost of a migration process from Oracle to PostgreSQL is not easy. To get a good assessment of that migration cost, Ora2Pg will inspect all database objects, all functions and stored procedures, to detect if there's still some 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 can not be exported. 👑

To activate the "analysis and report" mode, you have to use the export type SHOW_REPORT, like in the following command:

ora2pg -t SHOW_REPORT

Here's 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 can be analysed, Ora2Pg — being the icon it is with that whole "convert SQL and PL/SQL from Oracle syntax to PostgreSQL" talent — can go even further and estimate how hard your code is gonna be, plus roughly how long a full database migration is gonna take. main character energy fr. 💅

To estimate the migration cost in person-days, Ora2Pg gives you a config directive called ESTIMATE_COST — and yes bestie, you can also just flip it on from the command line:

--estimate_cost

Heads up tho: this feature can only be used with the SHOW_REPORT, FUNCTION, PROCEDURE, PACKAGE and QUERY export types. no cap.

ora2pg -t SHOW_REPORT  --estimate_cost

The report it spits out is the same as before, except now there's a whole new 'Estimated cost' column. slay:

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

That very last line? It's showing you the total estimated migration cost in person-days, based on how many migration units got estimated for each object. And like, one migration unit ≈ five minutes for a PostgreSQL expert. obsessed. If this is your first migration tho, you can bump that number up with the COST_UNIT_VALUE config directive or the --cost_unit_value command line option:

ora2pg -t SHOW_REPORT  --estimate_cost --cost_unit_value 10

Ora2Pg can also give you a full-on migration difficulty level assessment. here's a sample, bestie:

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

So this assessment is a letter — A or B — telling you whether the migration needs manual rewriting or not, plus a number from 1 up to 5 giving you a technical difficulty level. ✨

You've also got an extra option, --human_days_limit, to set the person-days limit where the migration level gets bumped to C — which is basically the "this needs a huge amount of work and full project management with migration support" flag. Default is 10 person-days. And you can use the HUMAN_DAYS_LIMIT config directive to change that default permanently.

This whole feature was built to help you — or your boss 🙈 — decide which database to migrate first, and which team needs to be mobilized to actually pull it off.

🌈 Global Oracle and MySQL migration assessment

Ora2Pg ships with a script called ora2pg_scanner, and it's your girl when you've got 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 the directory where the ora2pg binary lives. Might be useful only on Windows OS.
-c, --config FILE — set a 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, which describes the format that must be used:

"type","schema/database","dsn","user","password"

-o, --outdir DIR — (optional) by default all reports get dumped to a directory named output, and it'll be created automatically. If you wanna change the name of that directory, set the name as the second argument.
-t, --test — just try all connections by retrieving the required schema or database name. Super useful for validating 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's 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. periodt.

Notable: if you wanna scan all schemas from an Oracle instance, you literally just leave the schema field empty — Ora2Pg will automatically detect all available schemas and generate a report for each one. Of course you need a connection user with enough privileges to be able to scan all schemas. Like so:

"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"

That'll generate a report for all schemas in the XE instance. And heads up — 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, plus a detailed HTML report for each database scanned. 💖

Hint: use the -t | --test option first 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 stays, otherwise the ora2pg command calls will fail.

Oh and one more thing about the migration assessment — when it comes to functions, Ora2Pg always includes per default 2 migration units for TEST and 1 unit for SIZE per 1000 characters in the code. Which means that by default it'll add 15 minutes to the migration assessment per function. Obviously, if you've got unitary tests or super simple functions, that won't represent the real migration time. ngl.

🔍 Migration assessment method

The migration unit scores given to each type of Oracle database object are defined in the Perl library lib/Ora2Pg/PLSQL.pm inside the %OBJECT_SCORE variable definition. 👑

The number of PL/SQL lines associated to a migration unit is also defined in that same file, in the $SIZE_SCORE variable value.

And the number of migration units associated to each PL/SQL code difficulty can be found in that same Perl library, lib/Ora2Pg/PLSQL.pm, in the hash %UNCOVERED_SCORE initialization. 🎀

...and honestly bestie, this assessment method is a work in progress, so the author is begging for feedbacks on migration experiences to polish the scores/units attributed in those variables. So if you try it, tell them how it went 🫶

✨ Improving indexes and constraints creation speed ✨

So here's the slay: use the LOAD export type plus a file stuffed with SQL orders to perform, and you can dispatch those orders over multiple PostgreSQL connections at once. Iconic.

To unlock this feature, 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 indexes creation over 4 simultaneous PostgreSQL connections. 🚀

This will considerably accelerate this part of the migration process when you're dealing with huge data size. Periodt.

🦋 Exporting LONG RAW 🦋

Okay so if you still have columns defined as LONG RAW, sad news: Ora2Pg will not be able to export these kind of data. The OCI library fail to export them and always return the same first record. 😭 Like, it just gives up and hands you the same row over and over.

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

need to be "translated" into a table using BLOB as follow:

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 renamed the new temporary table on the fly using the REPLACE_TABLES configuration directive. Chef's kiss, table swapped, no cap. 💅

💜 Global variables 💜

Oracle allow 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 variables assignment are exported as call 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 have 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 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 lot of time for the migration. You can use TOAD Query Builder can 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 slide 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 TEST allow 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 respect the schema name limitation if EXPORT_SCHEMA and SCHEMA or PG_SCHEMA are defined. If only EXPORT_SCHEMA is set all schemes 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 schema. To test a list of schema you will have to repeat the calls to Ora2Pg by specifying a single schema each time. Annoying but that's the vibe check result. 😤

For example command:

ora2pg -t TEST -c config/ora2pg.conf > migration_diff.txt

Will create a file containing the report of all object and row count on both side, Oracle and PostgreSQL, with an error section giving you the detail 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 in comparing data retrieved from a foreign table pointing to the source Oracle table and a local PostgreSQL table resulting from the data export. It's giving detective energy fr fr. 🕵️

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 ior tds_fdw extension provided that FDW_SERVER and PG_DSN configuration directives are set.

By default Ora2Pg will extract the 10000 first rows from both side, 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 columns 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. Don't skip this, bestie, or your diff is meaningless.

Ora2Pg will stop comparing two tables after DATA_VALIDATION_ROWS is reached or that 10 errors has been encountered, result is dumped in a file named "data_validation.log" written in the current directory by default. The number of error before stopping the diff between rows can be controlled using the configuration directive DATA_VALIDATION_ERROR. All rows in errors are printed to the output file for your analyze.

It is possible to parallelize data validation by using -P option or the corresponding configuration directive PARALLEL_TABLES in ora2pg.conf. Go faster, go iconic. 🏃‍♀️💨

🔮 Use of System Change Number (SCN) 🔮

okay bestie, listen up, because this one is lowkey magic. Ora2Pg can export your data as of a specific SCN ✨ — like, time-travel but for Oracle. You set it from the command line with the -S or --scn option.

You can hand it a specific SCN, OR — and this is the fun part — if you want to use the current SCN at first connection time, set the value to 'current'. In that last case, the connection user needs the "SELECT ANY DICTIONARY" role or the "SELECT_CATALOG_ROLE" role, and the current SCN gets looked up in the v$database view. no cap.

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 flex Oracle's 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"

iconic behavior honestly 💅

📸 Change Data Capture (CDC) 📸

so, real talk: Ora2Pg does not have such a feature which allows you to import data and only apply changes after the first import. sorry bestie, it's just not that girl. 😭

BUT 🫶 you can use the --cdc_ready option to export data with registration of the SCN at the time of the table export. All SCN per tables are written to a file named TABLES_SCN.log by default, and you can change that using the -C | --cdc_file option.

These SCN 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. clean, simple, slay. ✨

🧚 Importing BLOB as large objects 🧚

By default, Ora2Pg imports Oracle BLOB as bytea — the destination column is created using the bytea data type. That's the standard vibe.

If you want to use a large object instead of bytea, just add the --blob_to_lo option to the ora2pg command. It will:

create the destination column as data type Oid
save the BLOB as a large object using the lo_from_bytea() function
insert the Oid returned by the call to lo_from_bytea() into the destination column instead of a bytea

⚠️ Because of the use of that function, this option can only be used with actions SHOW_COLUMN, TABLE and INSERT. Action COPY is not allowed. periodt.

If you want to use COPY, or you have a huge size BLOB (> 1GB) that can not be imported using lo_from_bytea(), you can add the option --lo_import to the ora2pg command. This lets you import data in two passes.

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 the 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.
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 to rows where the BLOB is > 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 request 🌈

If you need new features, let me know at <gilles AT darold DOT net>. This help a lot to develop a better/useful tool. (yes bestie, asking directly is how the good stuff happens ✨)

🎀 How to contribute ? 🎀

Any contribution to build a better tool is welcome — you just have to send me your ideas, features request or patches, and there will be applied. literally that easy. 💅

👑 LICENSE 👑

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/>.

🌸 ACKNOWLEDGEMENT 🌸

I must thanks a lot all the great contributors, see changelog for all acknowledgments. 🫶

and that's the whole page bestie, we made it to the very end 🎉 this gem was doc::Ora2Pg(3pm) from perl v5.40.1, dated 2025-05-01 — go forth and migrate those databases, main character energy only 👑✨💜

📦 where this page comes from

debian trixie: ora2pg 25.0-1 · GPL-3.0-or-later
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on 2026-10-14: a language model's rewrite of the original, under its terms