Это переработанная копия известной утилиты ora2pg (от Gilles Darold)
Включена автопараллельность по аналогии с DEFINED_PK, но не используется подход с defined_pk
Вместо этого таблицы разбиваются по ORA_HASH(rowid, ?)
Параметер DEFINED_PK больше не работает(не нужен)
ORACLE_COPIES - определяет степень параллельности
PARALLEL_MIN_ROWS - порог включения параллельности
NAME
Ora2Pg - Oracle to PostgreSQL database schema converter
DESCRIPTION
Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL
compatible schema. It connects your Oracle database, scans it
automatically and extracts its structure or data, then generates SQL
scripts that you can load into your PostgreSQL database.
Ora2Pg can be used for anything from reverse engineering Oracle database
to huge enterprise database migration or simply replicating some Oracle
data into a PostgreSQL database. It is really easy to use and doesn't
require any Oracle database knowledge other than providing the
parameters needed to connect to the Oracle database.
FEATURES
Ora2Pg 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
or
sudo yum install unixodbc
sudo yum install perl-DBD-ODBC
sudo yum install perl-DBD-Pg
then install the Microsoft ODBC Driver for SQL Server. Follow the
instructions 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/local/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=
make && make install
then set PERL5LIB to the path to your installation directory before
using Ora2Pg.
export PERL5LIB=
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.
The full control of the Oracle database migration is taken though a
single configuration file named ora2pg.conf. The format of this file
consist in a directive name in upper case followed by tab character and
a value. Comments are lines beginning with a #.
There's no specific order to place the configuration directives, they
are set at the time they are read in the configuration file.
For configuration directives that just take a single value, you can use
them multiple time in the configuration file but only the last
occurrence found in the file will be used. For configuration directives
that allow a list of value, you can use it multiple time, the values
will be appended to the list. If you use the IMPORT directive to load a
custom configuration file, directives defined in this file will be
stores from the place the IMPORT directive is found, so it is better to
put it at the end of the configuration file.
Values set in command line options will override values from the
configuration file.
Ora2Pg usage
First of all be sure that libraries and binaries path include the Oracle
Instant Client installation:
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
export PATH="/usr/lib/oracle/11.2/client64/bin:$PATH"
By default Ora2Pg will look for /etc/ora2pg/ora2pg.conf configuration
file, if the file exist you can simply execute:
/usr/local/bin/ora2pg
or under Windows(tm) run ora2pg.bat file, located in your perl bin
directory. Windows(tm) users may also find a template configuration file
in C:\ora2pg
If you want to call another configuration file, just give the path as
command line argument:
/usr/local/bin/ora2pg -c /etc/ora2pg/new_ora2pg.conf
Here are all command line parameters available when using ora2pg:
Usage: ora2pg [-dhpqv --estimate_cost --dump_as_html] [--option value]
-a | --allow str : Comma separated list of objects to allow from export.
Can be used with SHOW_COLUMN too.
-b | --basedir dir: Set the default output directory, where files
resulting from exports will be stored.
-c | --conf file : Set an alternate configuration file other than the
default /etc/ora2pg/ora2pg.conf.
-C | --cdc_file file: File used to store/read SCN per table during export.
default: TABLES_SCN.log in the current directory. This
is the file written by the --cdc_ready option.
-d | --debug : Enable verbose output.
-D | --data_type str : Allow custom type replacement at command line.
-e | --exclude str: Comma separated list of objects to exclude from export.
Can be used with SHOW_COLUMN too.
-h | --help : Print this short help.
-g | --grant_object type : Extract privilege from the given object type.
See possible values with GRANT_OBJECT configuration.
-i | --input file : File containing Oracle PL/SQL code to convert with
no Oracle database connection initiated.
-j | --jobs num : Number of parallel process to send data to PostgreSQL.
-J | --copies num : Number of parallel connections to extract data from Oracle.
-l | --log file : Set a log file. Default is stdout.
-L | --limit num : Number of tuples extracted from Oracle and stored in
memory before writing, default: 10000.
-m | --mysql : Export a MySQL database instead of an Oracle schema.
-M | --mssql : Export a Microsoft SQL Server database.
-n | --namespace schema : Set the Oracle schema to extract from.
-N | --pg_schema schema : Set PostgreSQL's search_path.
-o | --out file : Set the path to the output file where SQL will
be written. Default: output.sql in running directory.
-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.
--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 human-days limit where the migration
assessment level switch from B to C. Default is set to
5 human-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.
See full documentation at https://ora2pg.darold.net/ for more help or
see manpage with 'man ora2pg'.
ora2pg will return 0 on success, 1 on error. It will return 2 when a
child process has been interrupted and you've gotten the warning
message: "WARNING: an error 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.
Note that performance might be improved by updating stats on oracle:
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS
DBMS_STATS.GATHER_DATABASE_STATS
DBMS_STATS.GATHER_DICTIONARY_STATS
END;
Generate a migration template
The two options --project_base and --init_project when used indicate to
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 a sample of the command usage:
ora2pg --project_base /app/migration/ --init_project test_project
Creating project test_project.
/app/migration/test_project/
schema/
dblinks/
directories/
functions/
grants/
mviews/
packages/
partitions/
procedures/
sequences/
synonyms/
tables/
tablespaces/
triggers/
types/
views/
sources/
functions/
mviews/
packages/
partitions/
procedures/
triggers/
types/
views/
data/
config/
reports/
Generating generic configuration file
Creating script export_schema.sh to automate all exports.
Creating script import_all.sh to automate all imports.
It create a generic config file where you just have to define the Oracle
database connection and a shell script called export_schema.sh. The
sources/ directory will contains the Oracle code, the schema/ will
contains the code ported to PostgreSQL. The reports/ directory will
contains the html reports with the migration cost assessment.
If you want to use your own default config file, use the -c option to
give the path to that file. Rename it with .dist suffix if you want
ora2pg to apply the generic configuration values otherwise, the
configuration file will be copied untouched.
Once you have set the connection to the Oracle Database you can execute
the script export_schema.sh that will export all object 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.
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
or
dbi:Oracle:DB_SID
On 18c this could be for example:
dbi:Oracle:host=192.168.1.29;service_name=pdb1;port=1521
for the second notation the SID should be declared in the well known
file $ORACLE_HOME/network/admin/tnsnames.ora or in the path given to
the TNS_ADMIN environment variable.
For MySQL the DSN will 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.
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.
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.
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.
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.
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.
This will ask to Oracle to validate the PL/SQL that could have been
invalidate after a export/import for example. The 'VALID' or
'INVALID' status applies to functions, procedures, packages and user
defined types. It also concern disabled triggers.
EXPORT_INVALID
If the above configuration directive is not enough to validate your
PL/SQL code enable this configuration directive to allow export of
all PL/SQL code even if it is marked as invalid. The 'VALID' or
'INVALID' status applies to functions, procedures, packages and user
defined types.
PG_SCHEMA
Allow you to defined/force the PostgreSQL schema to use. By default
if you set EXPORT_SCHEMA to 1 the PostgreSQL search_path will be set
to the schema name exported set as value of the SCHEMA directive.
The value can be a comma delimited list of schema name but not when
using TABLE export type because in this case it will generate the
CREATE SCHEMA statement and it doesn't support multiple schema name.
For example, if you set PG_SCHEMA to something like "user_schema,
public", the search path will be set like this:
SET search_path = user_schema, public;
forcing the use of an other schema (here user_schema) than the one
from Oracle schema set in the SCHEMA directive.
You can also set the default search_path for the PostgreSQL user you
are using to connect to the destination database by using:
ALTER ROLE username SET search_path TO user_schema, public;
in this case you don't have to set PG_SCHEMA.
SYSUSERS
Without explicit schema, Ora2Pg will export all objects that not
belongs to 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
Following your Oracle installation you may have several other system
role defined. To append these users to the schema exclusion list,
just set the SYSUSERS configuration directive to a comma-separated
list of system user to exclude. For example:
SYSUSERS INTERNAL,SYSDBA,BI,HR,IX,OE,PM,SH
will add users INTERNAL and SYSDBA to the schema exclusion list.
FORCE_OWNER
By default the owner of the database objects is the one you're using
to connect to PostgreSQL using the psql command. If you use an other
user (postgres for example) you can force Ora2Pg to set the object
owner to be the one used in the Oracle database by setting the
directive to 1, or to a completely different username by setting the
directive value to that username.
FORCE_SECURITY_INVOKER
Ora2Pg use the function's security privileges set in Oracle and it
is often defined as SECURITY DEFINER. If you want to override those
security privileges for all functions and use SECURITY DEFINER
instead, enable this directive.
USE_TABLESPACE
When enabled this directive force ora2pg to export all tables,
indexes constraint and indexes using the tablespace name defined in
Oracle database. This works only with tablespace that are not TEMP,
USERS and SYSTEM.
WITH_OID
Activating this directive will force Ora2Pg to add WITH (OIDS) when
creating tables or views as tables. Default is same as PostgreSQL,
disabled.
LOOK_FORWARD_FUNCTION
List of schema to get functions/procedures meta information that are
used in the current schema export. When replacing call to function
with OUT parameters, if a function is declared in an other package
then the function call rewriting can not be done because Ora2Pg only
knows about functions declared in the current schema. By setting a
comma separated list of schema as value of this directive, Ora2Pg
will look forward in these packages for all
functions/procedures/packages declaration before proceeding to
current schema export.
NO_FUNCTION_METADATA
Force Ora2Pg to not look for function declaration. Note that this
will prevent Ora2Pg to rewrite function replacement call if needed.
Do not enable it unless looking forward at function breaks other
export.
Export type
The export action is perform following a single configuration directive
'TYPE', some other add more control on what should be really 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 perform at the same time so the TYPE
directive must be unique. If you have more than one only the last
found in the file will be registered.
Some export type can not or should not be load directly into the
PostgreSQL database and still require little manual editing. This is
the case for GRANT, TABLESPACE, TRIGGER, FUNCTION, PROCEDURE, TYPE,
QUERY and PACKAGE export types especially if you have PLSQL code or
Oracle specific SQL in it.
For TABLESPACE you must ensure that file path exist on the system
and for SYNONYM you may ensure that the object's owners and schemas
correspond to the new PostgreSQL database design.
Note that you can chained multiple export by giving to the TYPE
directive a comma-separated list of export type, but in this case
you must not use COPY or INSERT with other export type.
Ora2Pg will convert Oracle partition using table inheritance,
trigger and functions. See document at Pg site:
http://www.postgresql.org/docs/current/interactive/ddl-partitioning.
html
The TYPE export allow export of user defined Oracle type. If you
don't use the --plsql command line parameter it simply dump Oracle
user type asis else Ora2Pg will try to convert it to PostgreSQL
syntax.
The KETTLE export type requires that the Oracle and PostgreSQL DNS
are defined.
Since Ora2Pg v8.1 there'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 is an example of the SHOW_COLUMN output:
[2] TABLE CURRENT_SCHEMA (1 rows) (Warning: 'CURRENT_SCHEMA' is a reserved word in PostgreSQL)
CONSTRAINT : NUMBER(22) => bigint (Warning: 'CONSTRAINT' is a reserved word in PostgreSQL)
FREEZE : VARCHAR2(25) => varchar(25) (Warning: 'FREEZE' is a reserved word in PostgreSQL)
...
[6] TABLE LOCATIONS (23 rows)
LOCATION_ID : NUMBER(4) => smallint
STREET_ADDRESS : VARCHAR2(40) => varchar(40)
POSTAL_CODE : VARCHAR2(12) => varchar(12)
CITY : VARCHAR2(30) => varchar(30)
STATE_PROVINCE : VARCHAR2(25) => varchar(25)
COUNTRY_ID : CHAR(2) => char(2)
Those extraction keywords are use to only display the requested
information and exit. This allows you to quickly know on what you
are going to work.
The SHOW_COLUMN allow an other 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 used and the real encoding
of the Oracle database with the corresponding client encoding that
could be used with PostgreSQL
Ora2Pg allow you to export your Oracle, MySQL or MSSQL table
definition to be use 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');
Now you can use the table like a regular PostgreSQL table.
Release 10 adds a new export type destined to evaluate the content
of the database to migrate, in terms of objects and cost to end the
migration:
SHOW_REPORT : show a detailed report of the Oracle database content.
Here is a sample of report: http://ora2pg.darold.net/report.html
There also a more advanced report with migration cost. See the
dedicated chapter about Migration Cost Evaluation.
ESTIMATE_COST
Activate the migration cost evaluation. Must only be used with
SHOW_REPORT, FUNCTION, PROCEDURE, PACKAGE and QUERY export type.
Default is disabled. You may want to use the --estimate_cost command
line option instead to activate this functionality. Note that
enabling this directive will force PLSQL_PGSQL activation.
COST_UNIT_VALUE
Set the value in minutes of the migration cost evaluation unit.
Default is five minutes per unit. See --cost_unit_value to change
the unit value at command line.
DUMP_AS_HTML
By default when using SHOW_REPORT the migration report is generated
as simple text, enabling this directive will force ora2pg to create
a report in HTML format.
See http://ora2pg.darold.net/report.html for a sample report.
HUMAN_DAYS_LIMIT
Use this directive to redefined the number of human-days limit where
the migration assessment level must switch from B to C. Default is
set to 10 human-days.
JOBS
This configuration directive adds multiprocess support to COPY,
FUNCTION and PROCEDURE export type, the value is the number of
process to use. Default is multiprocess disable.
This directive is used to set the number of cores to used to
parallelize data import into PostgreSQL. During FUNCTION or
PROCEDURE export type each function will be translated to plpgsql
using a new process, the performances gain can be very important
when you have tons of function to convert.
There's no limitation in parallel processing than the number of
cores and the PostgreSQL I/O performance capabilities.
Doesn't work under Windows Operating System, it is simply disabled.
ORACLE_COPIES
This configuration directive adds multiprocess support to extract
data from Oracle. The value is the number of process to use to
parallelize the select query. Default is parallel query disable.
The parallelism is built on splitting the query following of the
number of cores given as value to ORACLE_COPIES as follow:
SELECT * FROM MYTABLE WHERE ABS(MOD(COLUMN, ORACLE_COPIES)) = CUR_PROC
where COLUMN is a technical key like a primary or unique key where
split will be based and the current core used by the query
(CUR_PROC). You can also force the column name to use using the
DEFINED_PK configuration directive.
Doesn't work under Windows Operating System, it is simply disabled.
DEFINED_PK
This directive is used to defined the technical key to used to split
the query between number of cores set with the ORACLE_COPIES
variable. For example:
DEFINED_PK EMPLOYEES:employee_id
The parallel query that will be used supposing that -J or
ORACLE_COPIES is set to 8:
SELECT * FROM EMPLOYEES WHERE ABS(MOD(employee_id, 8)) = N
where N is the current process forked starting from 0.
PARALLEL_TABLES
This directive is used to defined the number of tables that will be
processed in parallel for data extraction. The limit is the number
of cores on your machine. Ora2Pg will open one database connection
for each parallel table extraction. This directive, when upper than
1, will invalidate ORACLE_COPIES but not JOBS, so the real number of
process that will be used is PARALLEL_TABLES * JOBS.
Note that this directive when set upper that 1 will also
automatically enable the FILE_PER_TABLE directive if your are
exporting to files. This is used to export tables and views in
separate files.
Use PARALLEL_TABLES to use parallelism with COPY, INSERT and
TEST_DATA actions. It is also useful with TEST, TEST_COUNT, and
SHOW_TABLE if --count_rows is used for real row count.
DEFAULT_PARALLELISM_DEGREE
You can force Ora2Pg to use /*+ PARALLEL(tbname, degree) */ hint in
each query used to export data from Oracle by setting a value upper
than 1 to this directive. A value of 0 or 1 disable the use of
parallel hint. Default is disabled.
FDW_SERVER
This directive is used to set the name of the foreign data server
that is used in the "CREATE SERVER name FOREIGN DATA WRAPPER
..." command. This name will then be 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
Schema where foreign tables for data migration will be created. If
you use several instances of ora2pg for data migration through the
foreign data wrapper, you might need to change the name of the
schema for each instance. Default: ora2pg_fdw_import
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, allow 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 timestamp retrieves from custom type are extracted in the
following format: 01-JAN-77 12.00.00.000000 AM. It is impossible to
know the exact century that must be used, so by default any year
below 49 will be added to 2000 and others to 1900. You can use this
directive to change the default value 49. this is only relevant if
you have user defined type with a column timestamp.
AUDIT_USER
Set the comma separated list of username that must be used to filter
queries from the DBA_AUDIT_TRAIL table. Default is to not scan this
table and to never look for queries. This parameter is used only
with SHOW_REPORT and QUERY export type with no input file for
queries. Note that queries will be normalized before output unlike
when a file is given at input using the -i option or INPUT
directive.
FUNCTION_CHECK
Disable this directive if you want to disable check_function_bodies.
SET check_function_bodies = false;
It disables validation of the function body string during CREATE
FUNCTION. Default is to use de postgresql.conf setting that enable
it by default.
ENABLE_BLOB_EXPORT
Exporting BLOB takes time, in some circumstances you may want to
export all data except the BLOB columns. In this case disable this
directive and the BLOB columns will not be included into data
export. Take care that the target bytea column do not have a NOT
NULL constraint.
ENABLE_CLOB_EXPORT
Same behavior as ENABLE_BLOB_EXPORT but for CLOB.
DATA_EXPORT_ORDER
By default data export order will be done by sorting on table name.
If you have huge tables at end of alphabetic order and you are using
multiprocess, it can be better to set the sort order on size so that
multiple small tables can be processed before the largest tables
finish. In this case set this directive to size. Possible values are
name and size. Note that export type SHOW_TABLE and SHOW_COLUMN will
use this sort order too, not only COPY or INSERT export type.
Limiting objects to export
You may want to export only a part of an Oracle database, here are a set
of configuration directives that will allow you to control what parts of
the database should be exported.
ALLOW
This directive allows you to set a list of objects on which the
export must be limited, excluding all other objects in the same type
of export. The value is a space or comma-separated list of objects
name 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'
or
ora2pg -p -c ora2pg.conf -t GRANT -a 'GRANT[USER1 USER2]'
will limit export grants to users USER1 and USER2. But if you don't
want to export grants on some functions for these users, for
example:
ora2pg -p -c ora2pg.conf -t GRANT -a 'USER1 USER2' -e 'FUNCTION[adm_.*];PROCEDURE[adm_.*]'
Advanced filters may need some learning.
Oracle doesn't allow the use of lookahead 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.
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');
To force Ora2Pg to create an extra tsvector column with a dedicated
triggers for FTS indexes, disable this directive. In this case,
Ora2Pg will add the column as follow: ALTER TABLE t_document ADD
COLUMN tsv_title tsvector; Then update the column to compute FTS
vectors if data have been loaded before UPDATE t_document SET
tsv_title = to_tsvector('pg_catalog.french', coalesce(title,'')); To
automatically update the column when a modification in the title
column appears, 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 column, Ora2Pg
will use setweight() to set a weight in the order of the column
declaration.
FTS_CONFIG
Use this directive to force text search configuration to use. When
it is not set, Ora2Pg will autodetect the stemmer used by Oracle for
each index and pg_catalog.english if the information is not found.
USE_UNACCENT
If you want to perform your text search in an accent insensitive
way, enable this directive. Ora2Pg will create an helper function
over unaccent() and creates the pg_trgm indexes using this function.
With FTS Ora2Pg will redefine your text search configuration, for
example:
CREATE TEXT SEARCH CONFIGURATION fr (COPY = french);
ALTER TEXT SEARCH CONFIGURATION fr
ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem;
then set the FTS_CONFIG ora2pg.conf directive to fr instead of
pg_catalog.english.
When enabled, Ora2pg will create the wrapper function:
CREATE OR REPLACE FUNCTION unaccent_immutable(text)
RETURNS text AS
$$
SELECT public.unaccent('public.unaccent', $1);
$$ LANGUAGE sql IMMUTABLE
COST 1;
the indexes are exported as 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%';
USE_LOWER_UNACCENT
Same as above but call lower() in the unaccent_immutable() function:
CREATE OR REPLACE FUNCTION unaccent_immutable(text)
RETURNS text AS
$$
SELECT lower(public.unaccent('public.unaccent', $1));
$$ LANGUAGE sql IMMUTABLE;
Modifying object structure
One of the great usage of Ora2Pg is its flexibility to replicate Oracle
database into PostgreSQL database with a different structure or schema.
There's three configuration directives that allow you to map those
differences.
REORDERING_COLUMNS
Enable this directive to reordering columns and minimized the
footprint on disc, so that more rows fit on a data page, which is
the most important factor for speed. Default is disabled, that mean
the same order than in Oracle tables definition, that's should be
enough for most usage. This directive is only used with TABLE
export.
MODIFY_STRUCT
This directive allows you to limit the columns to extract for a
given table. The value consist in a space-separated list of table
name with a set of column between parenthesis as follow:
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 create table DDL will respect the new list of columns
and all indexes or foreign key pointing to or from a column removed
will not be exported.
EXCLUDE_COLUMNS
Instead of redefining the table structure with MODIFY_STRUCT you may
want to exclude some columns from the table export. The value
consist in a space-separated list of table name with a set of column
between parenthesis as follow:
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 create table DDL will respect the new list of
columns and all indexes or foreign key pointing to or from a column
removed will not be exported.
REPLACE_TABLES
This directive allows you to remap a list of Oracle table name to a
PostgreSQL table name 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
REPLACE_COLS
Like table name, the name of the 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 new name 'dictionary' and 'folder'.
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 column 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 the
't' and 'f' following the default replacement values and those
additionally set in directive BOOLEAN_VALUES.
Note that if you have modified the table name with REPLACE_TABLES
and/or the column's name, you need to use the name of the original
table and/or column.
REPLACE_COLS TB_NAME1(OLD_COL_NAME1:NEW_COL_NAME1)
REPLACE_AS_BOOLEAN TB_NAME1:OLD_COL_NAME1
You can also give a type and 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.
BOOLEAN_VALUES
Use this to add additional definition of the possible boolean values
used in Oracle fields. You must set a space-separated list of
TRUE:FALSE values. By default here are the values recognized by
Ora2Pg:
BOOLEAN_VALUES yes:no y:n 1:0 true:false enabled:disabled
Any values defined here will be added to the default list.
REPLACE_ZERO_DATE
When Ora2Pg find 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
NOT NULL constraint. If you can not remove the constraint, use this
directive to set an arbitral date that will be used instead. You can
also use -INFINITY if you don't want to use a fake date.
INDEXES_SUFFIX
Add the given value as suffix to indexes names. Useful if you have
indexes with same name as tables. For example:
INDEXES_SUFFIX _idx
will add _idx at ed of all index name. Not so common but can help.
INDEXES_RENAMING
Enable this directive to rename all indexes using
tablename_columns_names. Could be very useful for database that have
multiple time the same index name or that use the same name than a
table, which is not allowed by PostgreSQL Disabled by default.
USE_INDEX_OPCLASS
Operator classes text_pattern_ops, varchar_pattern_ops, and
bpchar_pattern_ops support B-tree indexes on the corresponding
types. The difference from the default operator classes is that the
values are compared strictly character by character rather than
according to the locale-specific collation rules. This makes these
operator classes suitable for use by queries involving pattern
matching expressions (LIKE or POSIX regular expressions) when the
database does not use the standard "C" locale. If you enable, with
value 1, this will force 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.
RENAME_PARTITION
Enable this directive if you want that your partition tables will be
renamed. Disabled by default. If you have multiple partitioned
table, when exported to PostgreSQL some partitions could have the
same name but different parent tables. This is not allowed, table
name must be unique, in this case enable this directive. A partition
will be renamed following the rule: "tablename"_part"pos" where
"pos" is the partition number. For subpartition this is:
"tablename"_part"pos"_subpart"pos" If this is partition/subpartition
default: "tablename"_part_default
"tablename"_part"pos"_subpart_default
DISABLE_PARTITION
If you don't want to reproduce the partitioning like in Oracle and
want to export all partitioned Oracle data into the main single
table in PostgreSQL enable this directive. Ora2Pg will export all
data into the main table name. Default is to use partitioning,
Ora2Pg will export data from each partition and import them into the
PostgreSQL dedicated partition table.
DISABLE_UNLOGGED
By default Ora2Pg export Oracle tables with the NOLOGGING attribute
as UNLOGGED tables. You may want to fully disable this feature
because you will lose all data from unlogged tables in case of a
PostgreSQL crash. Set it to 1 to export all tables as normal tables.
DOUBLE_MAX_VARCHAR
Increase varchar max character constraints to support PostgreSQL two
bytes character encoding when the source database applies the length
constraint on characters not bytes. Default disabled.
Oracle Spatial to PostGis
Ora2Pg fully export Spatial object from Oracle database. There's some
configuration directives that could be used to control the export.
AUTODETECT_SPATIAL_TYPE
By default Ora2Pg is looking at indexes to see the spatial
constraint type and dimensions defined under Oracle. Those
constraints are passed as at index creation using for example:
CREATE INDEX ... INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('sdo_indx_dims=2, layer_gtype=point');
If those Oracle constraints parameters are not set, the default is
to export those columns as generic type GEOMETRY to be able to
receive any spatial type.
The AUTODETECT_SPATIAL_TYPE directive allows to force Ora2Pg to
autodetect the real spatial type and dimension used in a spatial
column otherwise a non- constrained "geometry" type is used.
Enabling this feature will force Ora2Pg to scan a sample of 50000
column to look at the GTYPE used. You can increase or reduce the
sample size by setting the value of AUTODETECT_SPATIAL_TYPE to the
desired number of line to scan. The directive is enabled by default.
For example, in the case of a column named shape and defined with
Oracle type SDO_GEOMETRY, with AUTODETECT_SPATIAL_TYPE disabled it
will be converted as:
shape geometry(GEOMETRY) or shape geometry(GEOMETRYZ, 4326)
and if the directive is enabled and the column just contains a
single geometry type that use a single dimension:
shape geometry(POLYGON, 4326) or shape geometry(POLYGONZ, 4326)
with a two or three dimensional polygon.
CONVERT_SRID
This directive allows you to control the automatically 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 will be replaced by
the default value 8307 converted to its EPSG value: 4326 (see
DEFAULT_SRID).
If the value is upper than 1, all SRID will be forced to this value,
in this case DEFAULT_SRID will not be used when Oracle returns a
null value and the value will be forced to CONVERT_SRID.
Note that it is also possible to set the EPSG value on Oracle side
when sdo_cs.map_oracle_srid_to_epsg() return NULL if your want to
force the value:
system@db> UPDATE sdo_coord_ref_sys SET legacy_code=41014 WHERE srid = 27572;
DEFAULT_SRID
Use this directive to override the default EPSG SRID to used: 4326.
Can be overwritten by CONVERT_SRID, see above.
GEOMETRY_EXTRACT_TYPE
This directive can take three values: WKT (default), WKB and
INTERNAL. When it is set to WKT, Ora2Pg will use
SDO_UTIL.TO_WKTGEOMETRY() to extract the geometry data. When it is
set to WKB, Ora2Pg will use the binary output using
SDO_UTIL.TO_WKBGEOMETRY(). If those two extract type are calls at
Oracle side, they are slow and you can easily reach Out Of Memory
when you have lot of rows. Also WKB is not able to export 3D
geometry and some geometries like CURVEPOLYGON. In this case you may
use the INTERNAL extraction type. It will use a Pure Perl library to
convert the SDO_GEOMETRY data into a WKT representation, the
translation is done on Ora2Pg side. This is a work in progress,
please validate your exported data geometries before use. Default
spatial object extraction type is INTERNAL.
POSTGIS_SCHEMA
Use this directive to add a specific schema to the search path to
look for PostGis functions.
ST_SRID_FUNCTION
Oracle function to use to extract the srid from ST_Geometry meta
information. Default: ST_SRID, for example it should be set to
sde.st_srid for ArcSDE.
ST_DIMENSION_FUNCTION
Oracle function to use to extract the dimension from ST_Geometry
meta information. Default: ST_DIMENSION, for example it should be
set to sde.st_dimention for ArcSDE.
ST_GEOMETRYTYPE_FUNCTION
Oracle function to use to extract the geometry type from a
ST_Geometry column Default: ST_GEOMETRYTYPE, for example it should
be set to sde.st_geometrytype for ArcSDE.
ST_ASBINARY_FUNCTION
Oracle function to used to convert an ST_Geometry value into WKB
format. Default: ST_ASBINARY, for example it should be set to
sde.st_asbinary for ArcSDE.
ST_ASTEXT_FUNCTION
Oracle function to used to convert an ST_Geometry value into WKT
format. Default: ST_ASTEXT, for example it should be set to
sde.st_astext for ArcSDE.
PostgreSQL Import
By default conversion to PostgreSQL format is written to file
'output.sql'. The command:
psql mydb < output.sql
will import content of file output.sql into PostgreSQL mydb database.
DATA_LIMIT
When you are performing INSERT/COPY export Ora2Pg proceed by chunks
of DATA_LIMIT tuples for speed improvement. Tuples are stored in
memory before being written to disk, so if you want speed and have
enough system resources you can grow this limit to an upper value
for example: 100000 or 1000000. Before release 7.0 a value of 0 mean
no limit so that all tuples are stored in memory before being
flushed to disk. In 7.x branch this has been remove and chunk will
be set to the default: 10000
BLOB_LIMIT
When Ora2Pg detect a table with some BLOB it will automatically
reduce the value of this directive by dividing it by 10 until his
value is below 1000. You can control this value by setting
BLOB_LIMIT. Exporting BLOB use lot of resources, setting it to a too
high value can produce OOM.
CLOB_AS_BLOB
Apply same behavior on CLOB than BLOB with BLOB_LIMIT settings. This
is especially useful if you have large CLOB data. Default: enabled
OUTPUT
The Ora2Pg output filename can be changed with this directive.
Default value is output.sql. if you set the file name with extension
.gz or .bz2 the output will be automatically compressed. This
require 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 exists.
BZIP2
This directive allows you to specify the full path to the bzip2
program if it can not be found in the PATH environment variable.
FILE_PER_CONSTRAINT
Allow object constraints to be saved in a separate file during
schema export. The file will be named CONSTRAINTS_OUTPUT, where
OUTPUT is the value of the corresponding configuration directive.
You can use .gz xor .bz2 extension to enable compression. Default is
to save all data in the OUTPUT file. This directive is usable only
with TABLE export type.
The constraints can be imported quickly into PostgreSQL using the
LOAD export type to parallelize their creation over multiple (-j or
JOBS) connections.
FILE_PER_INDEX
Allow indexes to be saved in a separate file during schema export.
The file will be named INDEXES_OUTPUT, where OUTPUT is the value of
the corresponding configuration directive. You can use .gz xor .bz2
file extension to enable compression. Default is to save all data in
the OUTPUT file. This directive is usable only with TABLE AND
TABLESPACE export type. With the TABLESPACE export, it is used to
write "ALTER INDEX ... TABLESPACE ..." into a separate file named
TBSP_INDEXES_OUTPUT that can be loaded at end of the migration after
the indexes creation to move the indexes.
The indexes can be imported quickly into PostgreSQL using the LOAD
export type to parallelize their creation over multiple (-j or JOBS)
connections.
FILE_PER_FKEYS
Allow foreign key declaration to be saved in a separate file during
schema export. By default foreign keys are exported into the main
output file or in the CONSTRAINT_output.sql file. When enabled
foreign keys will be exported into a file named FKEYS_output.sql
FILE_PER_TABLE
Allow data export to be saved in one file per table/view. The files
will be named as tablename_OUTPUT, where OUTPUT is the value of the
corresponding configuration directive. You can still use .gz xor
.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
Allow functions, procedures and triggers to be saved in one file per
object. The files will be named as objectname_OUTPUT. Where OUTPUT
is the value of the corresponding configuration directive. You can
still use .gz xor .bz2 extension in the OUTPUT directive to enable
compression. Default 0 will save all in one single file, set it to 1
to enable this feature. This is usable only during the corresponding
export type, the package body export has a special behavior.
When export type is PACKAGE and you've enabled this directive,
Ora2Pg will create a directory per package, named with the lower
case name of the package, and will create one file per
function/procedure into that directory. If the configuration
directive is not enabled, it will create one file per package as
packagename_OUTPUT, where OUTPUT is the value of the corresponding
directive.
TRUNCATE_TABLE
If this directive is set to 1, a TRUNCATE TABLE instruction will be
add before loading data. This is usable only during INSERT or COPY
export type.
When activated, the instruction will be added only if there's no
global DELETE clause or not one specific to the current table (see
below).
DELETE
Support for include a DELETE FROM ... WHERE clause filter before
importing data and perform a delete of some lines instead of
truncating tables. Value is construct as follow:
TABLE_NAME[DELETE_WHERE_CLAUSE], or if you have only one where
clause for all tables just put the delete clause as single value.
Both are possible too. Here are some examples:
DELETE 1=1 # Apply to all tables and delete all tuples
DELETE TABLE_TEST[ID1='001'] # Apply only on table TABLE_TEST
DELETE TABLE_TEST[ID1='001' OR ID1='002] DATE_CREATE > '2001-01-01' TABLE_INFO[NAME='test']
The last applies two different delete where clause on tables
TABLE_TEST and TABLE_INFO and a generic delete where clause on
DATE_CREATE to all other tables. If TRUNCATE_TABLE is enabled it
will be applied to all tables not covered by the DELETE definition.
These DELETE clauses might be useful with regular "updates".
STOP_ON_ERROR
Set this parameter to 0 to not include the call to \set
ON_ERROR_STOP ON in all SQL scripts generated by Ora2Pg. By default
this order is always present so that the script will immediately
abort when an error is encountered.
COPY_FREEZE
Enable this directive to use COPY FREEZE instead of a simple COPY to
export data with rows already frozen. This is intended as a
performance option for initial data loading. Rows will be frozen
only if the table being loaded has been created or truncated in the
current sub-transaction. This will only work with export to file and
when -J or ORACLE_COPIES is not set or default to 1. It can be used
with direct import into PostgreSQL under the same condition but -j
or JOBS must also be unset or default to 1.
CREATE_OR_REPLACE
By default Ora2Pg uses CREATE OR REPLACE in functions and views DDL,
if you need not to override existing functions or views disable this
configuration directive, DDL will not include OR REPLACE.
DROP_IF_EXISTS
To add a DROP
O