Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
doc::Ora2Pg(3)	      User Contributed Perl Documentation	doc::Ora2Pg(3)

       Ora2Pg -	Oracle to PostgreSQL database schema converter

       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.

       Ora2Pg consist of a Perl	script (ora2pg)	and a Perl module (,
       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,

       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.
	       - Full MySQL export just	like Oracle database.
	       - Dispatch a list of SQL	orders over multiple PostgreSQL	connections
	       - Perform a diff	between	Oracle and PostgreSQL database for test	purpose.

       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 (

       See	for a HTML sample of an	Oracle
       database	migration report.

       All Perl	modules	can always be found at CPAN (
       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

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

       The Oracle Instant Client or a full Oracle installation must be
       installed on the	system.	You can	download the RPM from Oracle download

	   rpm -ivh oracle-instantclient12.2-basic-
	   rpm -ivh oracle-instantclient12.2-devel-
	   rpm -ivh oracle-instantclient12.2-jdbc-
	   rpm -ivh oracle-instantclient12.2-sqlplus-

       or simply download the corresponding ZIP	archives from Oracle download
       center and install them where you want, for example:

       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 migrate a MySQL database
       you need	the DBD::MySQL Perl modules.  These modules are	used to
       connect to the database but they	are not	mandatory if you want to
       migrate DDL input files.

       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

       On some Perl distribution you may need to install the Time::HiRes Perl

       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 Time::HiRes'

       otherwise use the packages provided by your distribution.

       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.

   Installing Ora2Pg
       Like any	other Perl Module Ora2Pg can be	installed with the following

	       tar xjf ora2pg-x.x.tar.bz2
	       cd ora2pg-x.x/
	       perl Makefile.PL
	       make && make install

       This will install 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
	       dmake &&	dmake 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/

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

       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:


       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.
	   -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.
	   -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.
	   -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.
	   --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_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	action.
	  --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.

       See full	documentation at 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
	   "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:


   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.

	       Generating generic configuration	file
	       Creating	script	to automate all	exports.
	       Creating	script 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
       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 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 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

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

	   This	directive is used to set the data source name in the form
	   standard DBI	DSN.  For example:;sid=DB_SID;port=1521



	   On 18c this could be	for example:


	   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:


	   the 'sid' part is replaced by 'database'.

	   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.

	   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.

	   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:


	   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.

	   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,

	   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.or or .sqlnet)	with the following

	       # Configure encryption of connections to	Oracle
	       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

   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.

       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.

	   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

	   This	directive is used to set the schema name to use	during export.
	   For example:


	   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.

	   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.

	   Enable/disable the CREATE SCHEMA SQL	order at starting of the
	   output file.	 It is enable by default and concern on	TABLE export

	   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.

	   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.

	   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

	   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.

	   Without explicit schema, Ora2Pg will	export all objects that	not
	   belongs to system schema or role:


	   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:


	   will	add users INTERNAL and SYSDBA to the schema exclusion list.

	   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.

	   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.

	   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,

	   Activating this directive will force	Ora2Pg to add WITH (OIDS) when
	   creating tables or views as tables. Default is same as PostgreSQL,

	   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.

	   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 type
       The export action is perform following a	single configuration directive
       'TYPE', some other add more control on what should be really exported.

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

		   - 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
		   - 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_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_VIEW:	perform	a count	on both	side of	rows returned by views

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

	   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

	   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

	   Since release v8.12,	Ora2Pg allow you to export your	Oracle Table
	   definition to be use	with the oracle_fdw foreign data wrapper. By
	   using type FDW your Oracle tables will be exported as follow:

			   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.

	   See	for more information on	this
	   foreign data	wrapper.

	   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

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

	   Here	is a sample of report:

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

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

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

	   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 for	a sample report.

	   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.

	   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.

	   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:


	   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

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

	   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.

	   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.

	   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.

	   This	directive is used to set the name of the foreign data server
	   that	is used	in the "CREATE SERVER name FOREIGN DATA	WRAPPER
	   oracle_fdw ..." command. This name will then	be used	in the "CREATE
	   FOREIGN TABLE ..." SQL command. Default is arbitrary	set to orcl.
	   This	only concern export type 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 timestamp retrieves	from custom type are extracted in the
	   following format: 01-JAN-77 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.

	   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

	   Disable this	directive if you want to disable

		   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.

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

	   By default data export order	will be	done by	sorting	on table 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.

	   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:


	   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

		   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

		   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

		   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.

	   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


	   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.

	   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.

	   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.

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

	   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

	   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.

	   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

	   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.

	   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

		   REPLACE_QUERY   EMPLOYEES[SELECT,e.fisrtname,lastname FROM EMPLOYEES e JOIN EMP_UPDT u ON ( 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.

	   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;

	   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(),

		   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

		   CREATE FUNCTION tsv_t_document_title() RETURNS trigger AS $$
			  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;
		   $$ 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

	   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

	   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:

			 ALTER MAPPING FOR hword, hword_part, word WITH	unaccent, french_stem;

	   then	set the	FTS_CONFIG ora2pg.conf directive to fr instead of

	   When	enabled, Ora2pg	will create the	wrapper	function:

		 CREATE	OR REPLACE FUNCTION unaccent_immutable(text)
		 RETURNS text AS
		     SELECT public.unaccent('public.unaccent', $1);
		    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%';

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

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

   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

	   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

	   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.

	   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:


	   Oracle tables ORIG_TBNAME1 and ORIG_TBNAME2 will be respectively
	   renamed into	DEST_TBNAME1 and DEST_TBNAME2

	   Like	table name, the	name of	the column can be remapped to a
	   different name using	the following syntax:


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

	   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.


	   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.


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


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

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

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

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

	   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.

	   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.

	   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

	   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.

	   Enable this directive if you	want that your partition table name
	   will	be exported using the parent table name. 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.

	   Enable this directive if you	want that your subpartition table name
	   will	be exported using the parent partition name. Enabled by
	   default. If the partition names are a part of the subpartition
	   names, you should enable this directive.

	   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.

	   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

   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.

	   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:

		   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

	   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.

	   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

	   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;

	   Use this directive to override the default EPSG SRID	to used: 4326.
	   Can be overwritten by CONVERT_SRID, see above.

	   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.

	   Use this directive to add a specific	schema to the search path to
	   look	for PostGis functions.

   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.

	   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

	   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.

	   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.

	   Since release 7.0, you can define a base directory where the	file
	   will	be written.  The directory must	exists.

	   This	directive allows you to	specify	the full path to the bzip2
	   program if it can not be found in the PATH environment variable.

	   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.

	   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.

	   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

	   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

	   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

	   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

	   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

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

	   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.

	   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.

	   By default Ora2Pg uses CREATE OR REPLACE in function	DDL, if	you
	   need	not to override	existing functions disable this	configuration
	   directive, DDL will not include OR REPLACE.

	   Enabling this directive will	prevent	Ora2Pg to print	his header
	   into	output files. Only the translated code will be written.

	   By default Ora2Pg use \i psql command to execute generated SQL
	   files if you	want to	use a relative path following the script
	   execution file enabling this	option will use	\ir. See psql help for
	   more	information.

       When using Ora2Pg export	type INSERT or COPY to dump data to file and
       that FILE_PER_TABLE is enabled, you will	be warned that Ora2Pg will not
       export data again if the	file already exists. This is to	prevent
       downloading twice table with huge amount	of data. To force the download
       of data from these tables you have to remove the	existing output	file

       If you want to import data on the fly to	the PostgreSQL database	you
       have three configuration	directives to set the PostgreSQL database
       connection. This	is only	possible with COPY or INSERT export type as
       for database schema there's no real interest to do that.

	   Use this directive to set the PostgreSQL data source	namespace
	   using DBD::Pg Perl module as	follow:


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

	   Note	that this directive is only used for data export, other	export
	   need	to be imported manually	through	the use	og psql	or any other
	   PostgreSQL client.

       PG_USER and PG_PWD
	   These two directives	are used to set	the login user and password.

	   If you do not supply	a credential with PG_PWD and you have
	   installed the Term::ReadKey Perl module, Ora2Pg will	ask for	the
	   password interactively. If PG_USER is not set it will be asked
	   interactively too.

	   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 to set
	   synchronous_commit directive	of postgresql.conf file.  This is only
	   used	when you load data directly to PostgreSQL, the default is off
	   to disable synchronous commit to gain speed at writing data.	Some
	   modified version of PostgreSQL, like	greenplum, do not have this
	   setting, so in this set this	directive to 1,	ora2pg will not	try to
	   change the setting.

	   This	directive can be used to send an initial command to
	   PostgreSQL, just after the connection. For example to set some
	   session parameters. This directive can be used multiple times.

   Column type control
	   If set to 1 replace portable	numeric	type into PostgreSQL internal
	   type.  Oracle data type NUMBER(p,s) is approximatively converted to
	   real	and float PostgreSQL data type.	If you have monetary fields or
	   don't want rounding issues with the extra decimals you should
	   preserve the	same numeric(p,s) PostgreSQL data type.	Do that	only
	   if you need exactness because using numeric(p,s) is slower than
	   using real or double.

	   If set to 1 replace portable	numeric	type into PostgreSQL internal
	   type.  Oracle data type NUMBER(p) or	NUMBER are converted to
	   smallint, integer or	bigint PostgreSQL data type following the
	   value of the	precision. If NUMBER without precision are set to
	   DEFAULT_NUMERIC (see	below).

	   NUMBER without precision are	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.

	   If you're experiencing any problem in data type schema conversion
	   with	this directive you can take full control of the	correspondence
	   between Oracle and PostgreSQL types to redefine data	type
	   translation used in Ora2pg. The syntax is a comma-separated list of
	   "Oracle datatype:Postgresql datatype". Here are the default list

		   DATA_TYPE	   VARCHAR2:varchar,NVARCHAR2:varchar,DATE:timestamp,LONG:text,LONG RAW:bytea,CLOB:text,NCLOB:text,BLOB:bytea,BFILE:bytea,RAW:bytea,UROWID:oid,ROWID:oid,FLOAT:double precision,DEC:decimal,DECIMAL:decimal,DOUBLE PRECISION:double precision,INT:numeric,INTEGER:numeric,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

	   Note	that the directive and the list	definition must	be a single

	   If you want to replace a type with a	precision and scale you	need
	   to escape the coma with a backslash.	For example, if	you want to
	   replace all NUMBER(*,0) into	bigint instead of numeric(38) add the

		  DATA_TYPE	  NUMBER(*\,0):bigint

	   You don't have to recopy all	default	type conversion	but just the
	   one you want	to rewrite.

	   There's a special case with BFILE when they are converted to	type
	   TEXT, they will just	contains the full path to the external file.
	   If you set the destination type to BYTEA, the default, Ora2Pg will
	   export the content of the BFILE as bytea. The third case is when
	   you set the destination type	to EFILE, in this case,	Ora2Pg will
	   export it as	an EFILE record: (DIRECTORY, FILENAME).	 Use the
	   DIRECTORY export type to export the existing	directories as well as
	   privileges on those directories.

	   There's no SQL function available to	retrieve the path to the
	   BFILE. Ora2Pg have to create	one using the DBMS_LOB package.

		   CREATE OR REPLACE FUNCTION ora2pg_get_bfilename( p_bfile IN BFILE )
		       l_dir   VARCHAR2(4000);
		       l_fname VARCHAR2(4000);
		       l_path  VARCHAR2(4000);
		       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;

	   This	function is only created if Ora2Pg found a table with a	BFILE
	   column and that the destination type	is TEXT. The function is
	   dropped at the end of the export. This concern both,	COPY and
	   INSERT export type.

	   There's no SQL function available to	retrieve BFILE as an EFILE
	   record, then	Ora2Pg have to create one using	the DBMS_LOB package.

		   CREATE OR REPLACE FUNCTION ora2pg_get_efile(	p_bfile	IN BFILE )
		       l_dir   VARCHAR2(4000);
		       l_fname VARCHAR2(4000);
		       dbms_lob.FILEGETNAME( p_bfile, l_dir, l_fname );
		       RETURN '(' || l_dir || ',' || l_fnamei || ')';

	   This	function is only created if Ora2Pg found a table with a	BFILE
	   column and that the destination type	is EFILE. The function is
	   dropped at the end of the export. This concern both,	COPY and
	   INSERT export type.

	   To set the destination type,	use the	DATA_TYPE configuration


	   for example.

	   The EFILE type is a user defined type created by the	PostgreSQL
	   extension external_file that	can be found here: This	is a port of the BFILE
	   Oracle type to PostgreSQL.

	   There's no SQL function available to	retrieve the content of	a
	   BFILE. Ora2Pg have to create	one using the DBMS_LOB package.

		   CREATE OR REPLACE FUNCTION ora2pg_get_bfile(	p_bfile	IN BFILE ) RETURN
			   filecontent BLOB := NULL;
			   src_file BFILE := NULL;
			   l_step PLS_INTEGER := 12000;
			   l_dir   VARCHAR2(4000);
			   l_fname VARCHAR2(4000);
			   offset NUMBER := 1;
		       IF p_bfile IS NULL THEN
		       END IF;

		       DBMS_LOB.FILEGETNAME( p_bfile, l_dir, l_fname );
		       src_file	:= BFILENAME( l_dir, l_fname );
		       IF src_file IS NULL THEN
		       END IF;

		       DBMS_LOB.CREATETEMPORARY(filecontent, true);
		       DBMS_LOB.LOADBLOBFROMFILE (filecontent, src_file, DBMS_LOB.LOBMAXSIZE, offset, offset);
		       RETURN filecontent;

	   This	function is only created if Ora2Pg found a table with a	BFILE
	   column and that the destination type	is bytea (the default).	The
	   function is dropped at the end of the export. This concern both,
	   COPY	and INSERT export type.

	   About the ROWID and UROWID, they are	converted into OID by
	   "logical" default but this will through 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.

	   Sometimes you need to force the destination type, for example a
	   column exported as timestamp	by Ora2Pg can be forced	into type
	   date. Value is a comma-separated list of TABLE:COLUMN:TYPE
	   structure. If you need to use comma or space	inside type definition
	   you will 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.

	   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, in this
	   case	you may	want to	transform this column in 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 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:


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

   Taking export under control
       The following other configuration directives interact directly with the
       export process and give you fine	granularity in database	export

	   For TABLE export you	may not	want to	export all schema constraints,
	   the SKIP configuration directive allows you to specify a space-
	   separated list of constraints that should not be exported. Possible
	   values are:

		   - fkeys: turn off foreign key constraints
		   - pkeys: turn off primary keys
		   - ukeys: turn off unique column constraints
		   - indexes: turn off all other index types
		   - checks: turn off check constraints

	   For example:

		   SKIP	   indexes,checks

	   will	removed	indexes	and check constraints from export.

	   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.

	   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.

	   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
	   do not support this feature,	you have to use	trigger	to operate the
	   ON UPDATE CASCADE.  As PostgreSQL has this feature, you can choose
	   how to add the foreign key option. There are	three values to	this
	   directive: never, the default that mean that	foreign	keys will be
	   declared exactly like in Oracle.  The second	value is delete, that
	   mean	that the ON UPDATE CASCADE option will be added	only if	the ON
	   DELETE CASCADE is already defined on	the foreign Keys. The last
	   value, always, will force all foreign keys to be defined using the
	   update option.

	   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.

	   In addition to exporting data when the DEFER_FKEY option set	to 1,
	   it will add a command to defer all foreign key constraints during
	   data	export and the import will be done in a	single transaction.
	   This	will work only if foreign keys have been exported as
	   deferrable and you are not using direct import to PostgreSQL
	   (PG_DSN is not defined). Constraints	will then be checked at	the
	   end of the transaction.

	   This	directive can also be enabled if you want to force all foreign
	   keys	to be created as deferrable and	initially deferred during
	   schema export (TABLE	export type).

	   If deferring	foreign	keys is	not possible due to the	amount of data
	   in a	single transaction, you've not exported	foreign	keys as
	   deferrable or you are using direct import to	PostgreSQL, you	can
	   use the DROP_FKEY directive.

	   It will drop	all foreign keys before	all data import	and recreate
	   them	at the end of the import.

	   This	directive allows you to	gain lot of speed improvement during
	   data	import by removing all indexes that are	not an automatic index
	   (indexes of primary keys) and recreate them at the end of data
	   import. Of course it	is far better to not import indexes and
	   constraints before having imported all data.

	   This	directive is used to disable triggers on all tables in COPY or
	   INSERT export modes.	Available values are USER (disable user-
	   defined triggers only) and ALL (includes RI system triggers).
	   Default is 0: do not	add SQL	statements to disable trigger before
	   data	import.

	   If you want to disable triggers during data migration, set the
	   value to USER if your are connected as non superuser	and ALL	if you
	   are connected as PostgreSQL superuser. A value of 1 is equal	to

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

	   By default all data that are	not of type date or time are escaped.
	   If you experience any problem with that you can set it to 1 to
	   disable character escaping during data export. This directive is
	   only	used during a COPY export.  See	STANDARD_CONFORMING_STRINGS
	   for enabling/disabling escape with INSERT statements.

	   This	controls whether ordinary string literals ('...') treat
	   backslashes literally, as specified in SQL standard.	This was the
	   default before Ora2Pg v8.5 so that all strings was escaped first,
	   now this is currently on, causing Ora2Pg to use the escape string
	   syntax (E'...') if this parameter is	not set	to 0. This is the
	   exact behavior of the same option in	PostgreSQL.  This directive is
	   only	used during data export	to build INSERT	statements.  See
	   NOESCAPE for	enabling/disabling escape in COPY statements.

	   If you want to convert CHAR(n) from Oracle into varchar(n) or text
	   on PostgreSQL using directive DATA_TYPE, you	might want to do some
	   trimming on the data. By default Ora2Pg will	auto-detect this
	   conversion and remove any whitespace	at both	leading	and trailing
	   position. If	you just want to remove	the leadings character set the
	   value to LEADING. If	you just want to remove	the trailing
	   character, set the value to TRAILING. Default value is BOTH.

	   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 charger, comment this directive, this is the default

	   If you want to preserve the case of Oracle object name set this
	   directive to	1.  By default Ora2Pg will convert all Oracle object
	   names to lower case.	I do not recommend to enable this unless you
	   will	always have to double-quote object names on all	your SQL

	   Allow escaping of column name using Oracle reserved words. Value is
	   a list of comma-separated reserved word. Default:

	   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.

	   Set this directive to 1 to replace default password by a random
	   password for	all extracted user during a GRANT export.

	   Since PostgreSQL 9.3, materialized view are supported with the SQL
	   syntax 'CREATE MATERIALIZED VIEW'. To force Ora2Pg to use the
	   native PostgreSQL support you must enable this configuration	-
	   enable by default. If you want to use the old style with table and
	   a set of function, you should disable it.

	   PostgreSQL version below 9.x	do not support IF EXISTS in DDL
	   statements.	Disabling the directive	with value 0 will prevent
	   Ora2Pg to add those keywords	in all generated statements. Default
	   value is 1, enabled.

       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 into
	   the generated PostgreSQL function declarations (disable it for
	   PostgreSQL database version lower than 8.1),	This is	now enable by

	   This	directive enable or disable the	use of default parameter value
	   in function export. Until PostgreSQL	8.4 such a default value was
	   not supported, this feature is now enable by	default.

       PG_SUPPORTS_WHEN	(Deprecated)
	   Add support to WHEN clause on triggers as PostgreSQL	v9.0 now
	   support it. This directive is enabled by default, set it to 0
	   disable this	feature.

       PG_SUPPORTS_INSTEADOF (Deprecated)
	   Add support to 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.

	   When	enabled, export	views with CHECK OPTION. Disable it if you
	   have	PostgreSQL version prior to 9.4. Default: 1, enabled.

	   If disabled,	do not export object with IF EXISTS statements.
	   Enabled by default.

	   PostgreSQL version prior to 10.0 do not have	native partitioning.
	   Enable this directive if you	want to	use declarative	partitioning.
	   Enable by default.

	   Some	versions of PostgreSQL like Redshift doesn't support substr()
	   and it need to be replaced by a call	to substring().	In this	case,
	   disable it.

	   Disable this	directive if you are using PG <	9.5, PL/SQL operator
	   used	in named parameter => will be replaced by PostgreSQL
	   proprietary operator	:= Enable by default.

	   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 IDENTITY column in
	   the Oracle table, they are exported as serial or bigserial columns.
	   When	it is enabled they are exported	as IDENTITY columns like:

		 CREATE	TABLE identity_test_tab	(
			 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 a
	   embedded function to	update the associated sequences	with the
	   restart value set to	"SELECT	max(colname)+1 FROM tablename".	Of
	   course this file must be imported after data	import otherwise
	   sequence will be kept to start value.  Enabled by default.

	   PostgreSQL v11 adds support of PROCEDURE, enable it if you use such

	   Use 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 GIN index,
	   when	disabled, a btree index	will be	created

	   Use pg_background extension to create an autonomous transaction
	   instead of using a dblink wrapper. With pg >= 9.5 only. Default is
	   to use dblink.  See
	   about this extension.

	   By default if you have an autonomous	transaction translated using
	   dblink extension instead of pg_background the connection is defined
	   using the values set	with PG_DSN, PG_USER and PG_PWD. If you	want
	   to fully override the connection string use this directive as
	   follow to set the connection	in the autonomous transaction wrapper
	   function. For example:

		   DBLINK_CONN	  port=5432 dbname=pgdb	host=localhost user=pguser password=pgpass

	   Use this directive to set the database handle's 'LongReadLen'
	   attribute to	a value	that will be the larger	than the expected size
	   of the LOBs.	The default is 1MB witch 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:

	   Important note: If you increase the value of	this directive take
	   care	that DATA_LIMIT	will probably needs 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 table separately and	set a DATA_LIMIT to
	   500 or lower, otherwise you may experience some out of memory.

	   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. Disable by default so that you will be warned if
	   your	LongReadLen value is not high enough.

	   Disable this	if you want to load full content of BLOB and CLOB and
	   not use LOB locators. In this case you will have to set LONGREADLEN
	   to the right	value. Note that this will not improve speed of	BLOB
	   export as most of the time is always	consumed by the	bytea escaping
	   and in this case export is done line	by line	and not	by chunk of
	   DATA_LIMIT rows. For	more information on how	it works, see

	   Default is enabled, it use LOB locators.

	   Oracle recommends reading from and writing to a LOB in batches
	   using a multiple of the LOB chunk size. This	chunk size defaults to
	   8k (8192).  Recent tests shown that the best	performances can be
	   reach with higher value like	512K or	4Mb.

	   A quick benchmark with 30120	rows with different size of BLOB
	   (200x5Mb, 19800x212k, 10000x942K, 100x17Mb, 20x156Mb), with
	   DATA_LIMIT=100, LONGREADLEN=170Mb and a total table size of 20GB

		  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	time faster with
	   LOB_CHUNK_SIZE set to 4Mb. Depending	of the size of most BLOB you
	   may want to adjust the value	here. For example if you have a
	   majority of small lobs bellow 8K, using 8192	is better to not waste
	   space. Default value	for LOB_CHUNK_SIZE is 512000.

	   Force the use getStringVal()	instead	of getClobVal()	for XML	data
	   export. Default is 1, enabled for backward compatibility. Set it to
	   0 to	use extract method a la	CLOB.  Note that XML value extracted
	   with	getStringVal() must not	exceed VARCHAR2	size limit (4000)
	   otherwise it	will return an error.

	   Set it to O if you want to disable export of	millisecond from
	   Oracle timestamp columns. By	default	milliseconds are exported with
	   the use of following	format:


	   Disabling will force	the use	of the following Oracle	format:

		   to_char(...,	'YYYY-MM-DD HH24:MI:SS')

	   By default milliseconds are exported.

	   Set this to 1 if you	don't want to export comment associated	to
	   tables and columns definition. Default is enabled.

   Control MySQL export	behavior
	   Enable this if double pipe and double ampersand (|| and &&) should
	   not be taken	as equivalent to OR and	AND. It	depend of the variable
	   @sql_mode, Use it only if Ora2Pg fail on auto detecting this

	   Enable this directive if you	want EXTRACT() replacement to use the
	   internal format returned as an integer, for example DD HH24:MM:SS
	   will	be replaced with format; DDHH24MMSS::bigint, this depend of
	   your	apps usage.

   Special options to handle character encoding
	   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	case it	could be useful. Using your own
	   settings with those configuration directive will change the client
	   encoding at Oracle side by setting the environment variables

	   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,
	   it will respectively	use

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

	   If you have change the NLS_LANG in non UTF8 encoding, you might
	   want	to set this directive. See for	more information.
	   Most	of the time, leave this	directive commented.

	   By default PostgreSQL client	encoding is automatically set to UTF8
	   to avoid encoding issue. 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

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

	   Enable/disable PLSQL	to PLPGSQL conversion. Enabled by default.

	   Ora2Pg can replace all conditions with a test on NULL by a call to
	   the coalesce() function to mimic the	Oracle behavior	where empty
	   string 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 that your	application
	   will	have the same behavior but if you have control on you
	   application a better	way is to change it to transform empty string
	   into	NULL because PostgreSQL	makes the difference.

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

	   If you don't	want to	export package as schema but as	simple
	   functions you might also want to replace all	call to
	   package_name.function_name. If you disable the PACKAGE_AS_SCHEMA
	   directive then Ora2Pg will replace all call to
	   package_name.function_name()	by package_name_function_name().
	   Default is to use a schema to emulate package.

	   The replacement will	be done	in all kind of DDL or code that	is
	   parsed by the PLSQL to PLPGSQL converter. PLSQL_PGSQL must be
	   enabled or -p used in command line.

	   Enable this directive if the	rewrite	of Oracle native syntax	(+) of
	   OUTER JOIN is broken. This will force Ora2Pg	to not rewrite such
	   code, default is to try to rewrite simple form of right outer join
	   for the moment.

	   By default Ora2Pg will convert call to SYS_GUID() Oracle function
	   with	a call to uuid_generate_v4 from	uuid-ossp extension. You can
	   redefined it	to use the gen_random_uuid function from pgcrypto
	   extension by	changing the function name. Default to

	   Note	that when a RAW(n) column has "SYS_GUID()" as default value
	   Ora2Pg will automatically translate the type	of the column into
	   uuid	which might be the right translation in	most of	the case.

	   By default Oracle functions are marked as STABLE as they can	not
	   modify data unless when used	in PL/SQL with variable	assignment or
	   as conditional expression. You can force Ora2Pg to create these
	   function as VOLATILE	by disabling this configuration	directive.

	   By default call to COMMIT/ROLLBACK are kept untouched by Ora2Pg to
	   force the user to review the	logic of the function. Once it is
	   fixed in Oracle source code or you want to comment this calls
	   enable the following	directive.

	   It is common	to see SAVEPOINT call inside PL/SQL procedure 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.

	   Ora2Pg replace all string constant during the pl/sql	to plpgsql
	   translation,	string constant	are all	text include between single
	   quote. If you have some string placeholder used in dynamic call to
	   queries you can set a list of regexp	to be temporary	replaced to
	   not break the parser. For example:

		   STRING_CONSTANT_REGEXP	  <placeholder value=".*">

	   The list of regexp must use the semi	colon as separator.

	   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:


	   ora2pg will use the $$ delimiter, with the example the result will

		   c_sample varchar(100) := $$This doesn't work.$$;

	   The value of	this configuration directive can be a list of regexp
	   separated by	a semi colon. The capture part (between	parenthesis)
	   is mandatory	in each	regexp if you want to restore the string

	   If you want to use functions	defined	in the Orafce library and
	   prevent Ora2Pg to translate call to these functions,	enable this
	   directive.  The Orafce library can be found here:

	   By default Ora2pg rewrite add_month(), add_year(), date_trunc() and
	   to_char() functions,	but you	may prefer to use the orafce version
	   of these function that do not need any code transformation.

	   Enable translation of autonomous transactions into a	wrapper
	   function using dblink or pg_background extension. If	you don't want
	   to use this translation and just want the function to be exported
	   as a	normal one without the pragma call, disable this directive.

   Materialized	view
       Materialized views are exported as snapshot "Snapshot Materialized
       Views" as PostgreSQL only supports full refresh.

       If you want to import the materialized views in PostgreSQL prior	to 9.3
       you have	to set configuration directive PG_SUPPORTS_MVIEW to 0. In this
       case Ora2Pg will	export all materialized	views as explain in this

       When exporting materialized view	Ora2Pg will first add the SQL code to
       create the "materialized_views" table:

	       CREATE TABLE materialized_views (
		       mview_name text NOT NULL	PRIMARY	KEY,
		       view_name text NOT NULL,
		       iname text,
		       last_refresh TIMESTAMP WITH TIME	ZONE

       all materialized	views will have	an entry in this table.	It then	adds
       the plpgsql code	to create 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 the
       name of the view	on which the materialized view is based	and the	third
       is the column name on which the index should be build (aka most of the
       time the	primary	key).  This column is not automatically	deduced	so you
       need to replace its name.

       As said above Ora2Pg only supports snapshot materialized	views so the
       table will be entirely refreshed	by issuing first a truncate of the
       table and then by load again all	data from the view:


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

   Other configuration directives
	   Set it to 1 will enable verbose output.

	   You can define common Ora2Pg	configuration directives into a	single
	   file	that can be imported into other	configuration files with the
	   IMPORT configuration	directive as follow:

		   IMPORT  commonfile.conf

	   will	import all configuration directives defined into
	   commonfile.conf into	the current configuration file.

   Exporting views as PostgreSQL tables
       You can export any Oracle view as a PostgreSQL table simply by setting
       TYPE configuration option to TABLE to have 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,	then it	will extract the data
       (if TYPE=COPY or	INSERT)	following the view schema.

       For example, with the following view:

	       CREATE OR REPLACE VIEW product_prices (category_id, product_count, low_price, high_price) AS
	       SELECT  category_id, COUNT(*) as	product_count,
		   MIN(list_price) as low_price,
		   MAX(list_price) as high_price
		FROM   product_information
	       GROUP BY	category_id;

       Setting VIEW_AS_TABLE to	product_prices and using export	type TABLE,
       will force Ora2Pg to detect columns returned types and to generate a
       create table statement:

	       CREATE TABLE product_prices (
		       category_id bigint,
		       product_count integer,
		       low_price numeric,
		       high_price numeric

       Data will be loaded following the COPY or INSERT	export type and	the
       view declaration.

       You can use the ALLOW and EXCLUDE directive in addition to filter other
       objects to export.

   Export as Kettle transformation XML files
       The KETTLE export type is useful	if you want to use Penthalo Data
       Integrator (Kettle) to import data to PostgreSQL. With this type	of
       export Ora2Pg will generate one XML Kettle transformation files (.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

       will generate one file called 'HR.MYTABLE.ktr' and add a	line to	the
       output file (



	       JAVAMAXMEM=4096 ./	-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 as follow:

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

       This is only possible if	you have defined the technical key to used 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 defined the
       SQL query as follow 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 obtain a good assessment of	this 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 inspect 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
       de type SHOW_REPORT like	in the following command:

	       ora2pg -t SHOW_REPORT

       Here is a sample	report obtained	with this command:

	       Ora2Pg: Oracle Database Content Report
	       Version Oracle Database 10g Enterprise Edition Release
	       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, by his ability to convert
       SQL and PL/SQL code from	Oracle syntax to PostgreSQL, can go further by
       estimating the code difficulties	and estimate the time necessary	to
       operate a full database migration.

       To estimate the migration cost in man-days, Ora2Pg allow	you to use a
       configuration directive called ESTIMATE_COST that you can also enabled
       at command line:


       This feature can	only be	used with the SHOW_REPORT, FUNCTION,
       PROCEDURE, PACKAGE and QUERY export type.

	       ora2pg -t SHOW_REPORT  --estimate_cost

       The generated report is same as above but with a	new 'Estimated cost'
       column as follow:

	       Ora2Pg: Oracle Database Content Report
	       Version Oracle Database 10g Express Edition Release
	       Schema  HR
	       Size  890.00 MB

	       Object  Number  Invalid Estimated cost  Comments
	       DATABASE	LINK  3	0 9 Database links will	be exported as SQL/MED PostgreSQL's Foreign Data Wrapper (FDW) extensions
					       using oracle_fdw.
	       FUNCTION	 2 0 7 Total size of function code: 369	bytes. HIGH_SALARY: 2, VALIDATE_SSN: 3.
	       INDEX 21	 0 11  11 index(es) are	concerned by the export, others	are automatically generated and	will do	so
					       on PostgreSQL. 11 b-tree	index(es). Note	that bitmap index(es) will be exported as b-tree
					       index(es) if any. Cluster, domain, bitmap join and IOT indexes will not be exported at all.
					       Reverse indexes are not exported	too, you may use a trigram-based index (see pg_trgm) or	a
					       reverse() function based	index and search. You may also use 'varchar_pattern_ops', 'text_pattern_ops'
					       or 'bpchar_pattern_ops' operators in your indexes to improve search with	the LIKE operator
					       respectively into varchar, text or char columns.
	       JOB 0 0 0 Job are not exported. You may set external cron job with them.
	       MATERIALIZED VIEW 1 0 3 All materialized	view will be exported as snapshot materialized views, they
						       are only	updated	when fully refreshed.
	       PACKAGE BODY  0 2 54  Total size	of package code: 2487 bytes. Number of procedures and functions	found
						       inside those packages: 7. two_proc.get_table: 10, emp_mgmt.create_dept: 4,
						       emp_mgmt.hire: 13, emp_mgmt.increase_comm: 4, emp_mgmt.increase_sal: 4,
						       emp_mgmt.remove_dept: 3,	emp_mgmt.remove_emp: 2.
	       PROCEDURE 4 0 39	 Total size of procedure code: 2436 bytes. TEST_COMMENTAIRE: 2,	SECURE_DML: 3,
						       PHD_GET_TABLE: 24, ADD_JOB_HISTORY: 6.
	       SEQUENCE	 3 0 0 Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL
						       will be transformed into	NEXTVAL('sequence_name') or CURRVAL('sequence_name').
	       SYNONYM	 3 0 4 SYNONYMs	will be	exported as views. SYNONYMs do not exists with PostgreSQL but a	common workaround
						       is to use views or set the PostgreSQL search_path in your session to access
						       object outside the current schema.
						       user1.emp_details_view_v	is an alias to hr.emp_details_view.
						       user1.emp_table is an alias to hr.employees@other_server.
						       user1.offices is	an alias to hr.locations.
	       TABLE 17	 0 8.5 1 external table(s) will	be exported as standard	table. See EXTERNAL_TO_FDW configuration
					       directive to export as file_fdw foreign tables or use COPY in your code if you just want	to
					       load data from external files. 2	binary columns.	4 unknown types.
	       TRIGGER 1 1 4 Total size	of trigger code: 123 bytes. UPDATE_JOB_HISTORY:	2.
	       TYPE  7 1 5 5 type(s) are concerned by the export, others are not supported. 2 Nested Tables. 2 Object type.
					       1 Subtype. 1 Type Boby. 1 Type inherited. 1 Varrays. Note that Type inherited and Subtype are
					       converted as table, type	inheritance is not supported.
	       TYPE BODY 0 3 30	 Export	of type	with member method are not supported, they will	not be exported.
	       VIEW  1 1 1 Views are fully supported, but if you have updatable	views you will need to use INSTEAD OF triggers.
	       Total 65	 8 162.5 162.5 cost migration units means approximatively 2 man	day(s).

       The last	line shows the total estimated migration code in man-days
       following the number of migration units estimated for each object. This
       migration unit represent	around five minutes for	a PostgreSQL expert.
       If this is your first migration you can get it higher with the
       configuration directive COST_UNIT_VALUE or the --cost_unit_value
       command line option:

	       ora2pg -t SHOW_REPORT  --estimate_cost --cost_unit_value	10

       Ora2Pg is also able to give you a migration difficulty level
       assessment, here	a sample:

       Migration level:	B-5

	   Migration levels:
	       A - Migration that might	be run automatically
	       B - Migration with code rewrite and a human-days	cost up	to 5 days
	       C - Migration with code rewrite and a human-days	cost above 5 days
	   Technical levels:
	       1 = trivial: no stored functions	and no triggers
	       2 = easy: no stored functions but with triggers,	no manual rewriting
	       3 = simple: stored functions and/or triggers, no	manual rewriting
	       4 = manual: no stored functions but with	triggers or views with code rewriting
	       5 = difficult: stored functions and/or triggers with code rewriting

       This assessment consist in a letter A or	B to specify if	the migration
       needs manual rewriting or not. And a number from	1 up to	5 to give you
       a technical difficulty level. You have an additional option
       --human_days_limit to specify the number	of human-days limit where the
       migration level should be set to	C to indicate that it need a huge
       amount of work and a full project management with migration support.
       Default is 10 human-days. You can use the configuration directive
       HUMAN_DAYS_LIMIT	to change this default value permanently.

       This feature has	been developed to help you or your boss	to decide
       which database to migrate first and the team that must be mobilized to
       operate the migration.

   Global Oracle and MySQL migration assessment
       Ora2Pg come with	a script ora2pg_scanner	that can be used when you have
       a huge number of	instances and schema to	scan for migration assessment.

       Usage: ora2pg_scanner -l	CSVFILE	[-o OUTDIR]

	  -b | --binpath DIR: full path	to directory where the ora2pg binary stays.
		       Might be	useful only on Windows OS.
	  -c | --config	FILE: set custom configuration file to use otherwise ora2pg
		       will use	the default: /etc/ora2pg/ora2pg.conf.
	  -l | --list FILE : CSV file containing a list	of databases to	scan with
		       all required information. The first line	of the file
		       can contain the following header	that describes the
		       format that must	be used:


	  -o | --outdir	DIR : (optional) by default all	reports	will be	dumped to a
		       directory named 'output', it will be created automatically.
		       If you want to change the name of this directory, set the name
		       at second argument.

	  -t | --test :	just try all connections by retrieving the required schema
			or database name. Useful to validate your CSV list file.
	  -u | --unit MIN : redefine globally the migration cost unit value in minutes.
			Default	is taken from the ora2pg.conf (default 5 minutes).

	  Here is a full example of a CSV databases list file:


	  The CSV field	separator must be a comma.

	  Note that if you want	to scan	all schemas from an Oracle instance you	just
	  have to leave	the schema field empty,	Ora2Pg will automatically detect all
	  available schemas and	generate a report for each one.	Of course you need to
	  use a	connection user	with enough privileges to be able to scan all schemas.
	  For example:


	  will generate	a report for all schema	in the XE instance. Note that in this
	  case the SCHEMA directive in ora2pg.conf must	not be set.

       It will generate	a CSV file with	the assessment result, one line	per
       schema or database and a	detailed HTML report for each database

       Hint: Use the -t	| --test option	before 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.

       In the migration	assessment details about functions Ora2Pg always
       include per default 2 migration units for TEST and 1 unit for SIZE per
       1000 characters in the code. This mean that by default it will add 15
       minutes in the migration	assessment per function. Obviously if you have
       unitary tests or	very simple functions this will	not represent the real
       migration time.

   Migration assessment	method
       Migration unit scores given to each type	of Oracle database object are
       defined in the Perl library lib/Ora2Pg/ in the %OBJECT_SCORE
       variable	definition.

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

       The number of migration units associated	to each	PL/SQL code
       difficulties can	be found in the	same Perl library lib/Ora2Pg/
       in the hash %UNCOVERED_SCORE initialization.

       This assessment method is a work	in progress so I'm expecting feedbacks
       on migration experiences	to polish the scores/units attributed in those

   Improving indexes and constraints creation speed
       Using the LOAD export type and a	file containing	SQL orders to perform,
       it is possible to dispatch those	orders over multiple PostgreSQL
       connections. To be able to use this feature, the	PG_DSN,	PG_USER	and
       PG_PWD must be set. Then:

	       ora2pg -t LOAD -c config/ora2pg.conf -i schema/tables/INDEXES_table.sql -j 4

       will dispatch indexes creation over 4 simultaneous PostgreSQL

       This will considerably accelerate this part of the migration process
       with huge data size.

   Exporting LONG RAW
       If you still have columns defined as LONG RAW, 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. 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:

		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.

   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:


       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.

       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:

       There's also an alternative with	SQL Developer Data Modeler, see

       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:

   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 set in	the SCHEMA directive
       to scan the Oracle database and also at PostgreSQL side if
       EXPORT_SCHEMA is	enabled.  If PG_SCHEMA is defined and EXPORT_SCHEMA is
       enabled Ora2Pg will use the list	of schemas defined in PG_SCHEMA	to
       scan PostgreSQL.	If EXPORT_SCHEMA is disabled the entire	PostgreSQL
       database	is scanned.

       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

	       Table ptab2 does	not exists in PostgreSQL database.

	       Table departments doesn't have the same number of indexes in Oracle (2) and in PostgreSQL (1).

	       OK, Oracle and PostgreSQL have the same number of VIEW.

	       OK, Oracle and PostgreSQL have the same number of MVIEW.

	       SEQUENCE	does not have the same count in	Oracle (1) and in PostgreSQL (0).

	       TYPE does not have the same count in Oracle (1) and in PostgreSQL (0).

	       [TEST FDW COUNT]
	       OK, Oracle and PostgreSQL have the same number of FDW.

       Here we can see that one	table, one index, one sequence and one user
       defined type have not been imported yet or have encountered an error.

   Author / Maintainer
       Gilles Darold <gilles AT	darold DOT net>

       Please report any bugs, patches,	help, etc. to <gilles AT darold	DOT

   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.

   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

       Copyright (c) 2000-2020 Gilles Darold - All rights reserved.

	       This program is free software: you can redistribute it and/or modify
	       it under	the terms of the GNU General Public License as published by
	       the Free	Software Foundation, either version 3 of the License, or
	       any later version.

	       This program is distributed in the hope that it will be useful,
	       but WITHOUT ANY WARRANTY; without even the implied warranty of
	       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 < >.

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

perl v5.32.1			  2021-04-01			doc::Ora2Pg(3)


Want to link to this manual page? Use this URL:

home | help