1NAME
2 Ora2Pg - Oracle to PostgreSQL database schema converter
3
4DESCRIPTION
5 Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL
6 compatible schema. It connects your Oracle database, scans it
7 automatically and extracts its structure or data, then generates SQL
8 scripts that you can load into your PostgreSQL database.
9
10 Ora2Pg can be used for anything from reverse engineering Oracle database
11 to huge enterprise database migration or simply replicating some Oracle
12 data into a PostgreSQL database. It is really easy to use and doesn't
13 require any Oracle database knowledge other than providing the
14 parameters needed to connect to the Oracle database.
15
16FEATURES
17 Ora2Pg consist of a Perl script (ora2pg) and a Perl module (Ora2Pg.pm),
18 the only thing you have to modify is the configuration file ora2pg.conf
19 by setting the DSN to the Oracle database and optionally the name of a
20 schema. Once that's done you just have to set the type of export you
21 want: TABLE with constraints, VIEW, MVIEW, TABLESPACE, SEQUENCE,
22 INDEXES, TRIGGER, GRANT, FUNCTION, PROCEDURE, PACKAGE, PARTITION, TYPE,
23 INSERT or COPY, FDW, QUERY, KETTLE, SYNONYM.
24
25 By default Ora2Pg exports to a file that you can load into PostgreSQL
26 with the psql client, but you can also import directly into a PostgreSQL
27 database by setting its DSN into the configuration file. With all
28 configuration options of ora2pg.conf you have full control of what
29 should be exported and how.
30
31 Features included:
32
33 - Export full database schema (tables, views, sequences, indexes), with
34 unique, primary, foreign key and check constraints.
35 - Export grants/privileges for users and groups.
36 - Export range/list partitions and sub partitions.
37 - Export a table selection (by specifying the table names).
38 - Export Oracle schema to a PostgreSQL 8.4+ schema.
39 - Export predefined functions, triggers, procedures, packages and
40 package bodies.
41 - Export full data or following a WHERE clause.
42 - Full support of Oracle BLOB object as PG BYTEA.
43 - Export Oracle views as PG tables.
44 - Export Oracle user defined types.
45 - Provide some basic automatic conversion of PLSQL code to PLPGSQL.
46 - Works on any platform.
47 - Export Oracle tables as foreign data wrapper tables.
48 - Export materialized view.
49 - Show a report of an Oracle database content.
50 - Migration cost assessment of an Oracle database.
51 - Migration difficulty level assessment of an Oracle database.
52 - Migration cost assessment of PL/SQL code from a file.
53 - Migration cost assessment of Oracle SQL queries stored in a file.
54 - Generate XML ktr files to be used with Penthalo Data Integrator (Kettle)
55 - Export Oracle locator and spatial geometries into PostGis.
56 - Export DBLINK as Oracle FDW.
57 - Export SYNONYMS as views.
58 - Export DIRECTORY as external table or directory for external_file extension.
59 - Full MySQL export just like Oracle database.
60 - Dispatch a list of SQL orders over multiple PostgreSQL connections
61 - Perform a diff between Oracle and PostgreSQL database for test purpose.
62
63 Ora2Pg does its best to automatically convert your Oracle database to
64 PostgreSQL but there's still manual works to do. The Oracle specific
65 PL/SQL code generated for functions, procedures, packages and triggers
66 has to be reviewed to match the PostgreSQL syntax. You will find some
67 useful recommendations on porting Oracle PL/SQL code to PostgreSQL
68 PL/PGSQL at "Converting from other Databases to PostgreSQL", section:
69 Oracle (http://wiki.postgresql.org/wiki/Main_Page).
70
71 See http://ora2pg.darold.net/report.html for a HTML sample of an Oracle
72 database migration report.
73
74INSTALLATION
75 All Perl modules can always be found at CPAN (http://search.cpan.org/).
76 Just type the full name of the module (ex: DBD::Oracle) into the search
77 input box, it will brings you the page for download.
78
79 Releases of Ora2Pg stay at SF.net
80 (https://sourceforge.net/projects/ora2pg/).
81
82 Under Windows you should install Strawberry Perl
83 (http://strawberryperl.com/) and the OSes corresponding Oracle clients.
84 Since version 5.32 this Perl distribution include pre-compiled driver of
85 DBD::Oracle and DBD::Pg.
86
87 Requirement
88 The Oracle Instant Client or a full Oracle installation must be
89 installed on the system. You can download the RPM from Oracle download
90 center:
91
92 rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
93 rpm -ivh oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
94 rpm -ivh oracle-instantclient12.2-jdbc-12.2.0.1.0-1.x86_64.rpm
95 rpm -ivh oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
96
97 or simply download the corresponding ZIP archives from Oracle download
98 center and install them where you want, for example:
99 /opt/oracle/instantclient_12_2/
100
101 You also need a modern Perl distribution (perl 5.10 and more). To
102 connect to a database and proceed to his migration you need the DBI Perl
103 module > 1.614. To migrate an Oracle database you need the DBD::Oracle
104 Perl modules to be installed. To migrate a MySQL database you need the
105 DBD::MySQL Perl modules. These modules are used to connect to the
106 database but they are not mandatory if you want to migrate DDL input
107 files.
108
109 To install DBD::Oracle and have it working you need to have the Oracle
110 client libraries installed and the ORACLE_HOME environment variable must
111 be defined.
112
113 If you plan to export a MySQL database you need to install the Perl
114 module DBD::mysql which requires that the mysql client libraries are
115 installed.
116
117 On some Perl distribution you may need to install the Time::HiRes Perl
118 module.
119
120 If your distribution doesn't include these Perl modules you can install
121 them using CPAN:
122
123 perl -MCPAN -e 'install DBD::Oracle'
124 perl -MCPAN -e 'install DBD::MySQL'
125 perl -MCPAN -e 'install Time::HiRes'
126
127 otherwise use the packages provided by your distribution.
128
129 Optional
130 By default Ora2Pg dumps export to flat files, to load them into your
131 PostgreSQL database you need the PostgreSQL client (psql). If you don't
132 have it on the host running Ora2Pg you can always transfer these files
133 to a host with the psql client installed. If you prefer to load export
134 'on the fly', the perl module DBD::Pg is required.
135
136 Ora2Pg allows you to dump all output in a compressed gzip file, to do
137 that you need the Compress::Zlib Perl module or if you prefer using
138 bzip2 compression, the program bzip2 must be available in your PATH.
139
140 If your distribution doesn't include these Perl modules you can install
141 them using CPAN:
142
143 perl -MCPAN -e 'install DBD::Pg'
144 perl -MCPAN -e 'install Compress::Zlib'
145
146 otherwise use the packages provided by your distribution.
147
148 Installing Ora2Pg
149 Like any other Perl Module Ora2Pg can be installed with the following
150 commands:
151
152 tar xjf ora2pg-x.x.tar.bz2
153 cd ora2pg-x.x/
154 perl Makefile.PL
155 make && make install
156
157 This will install Ora2Pg.pm into your site Perl repository, ora2pg into
158 /usr/local/bin/ and ora2pg.conf into /etc/ora2pg/.
159
160 On Windows(tm) OSes you may use instead:
161
162 perl Makefile.PL
163 gmake && gmake install
164
165 This will install scripts and libraries into your Perl site installation
166 directory and the ora2pg.conf file as well as all documentation files
167 into C:\ora2pg\
168
169 To install ora2pg in a different directory than the default one, simply
170 use this command:
171
172 perl Makefile.PL PREFIX=<your_install_dir>
173 make && make install
174
175 then set PERL5LIB to the path to your installation directory before
176 using Ora2Pg.
177
178 export PERL5LIB=<your_install_dir>
179 ora2pg -c config/ora2pg.conf -t TABLE -b outdir/
180
181 Packaging
182 If you want to build the binary package for your preferred Linux
183 distribution take a look at the packaging/ directory of the source
184 tarball. There is everything to build RPM, Slackware and Debian
185 packages. See README file in that directory.
186
187 Installing DBD::Oracle
188 Ora2Pg needs the Perl module DBD::Oracle for connectivity to an Oracle
189 database from perl DBI. To get DBD::Oracle get it from CPAN a perl
190 module repository.
191
192 After setting ORACLE_HOME and LD_LIBRARY_PATH environment variables as
193 root user, install DBD::Oracle. Proceed as follow:
194
195 export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib
196 export ORACLE_HOME=/usr/lib/oracle/12.2/client64
197 perl -MCPAN -e 'install DBD::Oracle'
198
199 If you are running for the first time it will ask many questions; you
200 can keep defaults by pressing ENTER key, but you need to give one
201 appropriate mirror site for CPAN to download the modules. Install
202 through CPAN manually if the above doesn't work:
203
204 #perl -MCPAN -e shell
205 cpan> get DBD::Oracle
206 cpan> quit
207 cd ~/.cpan/build/DBD-Oracle*
208 export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
209 export ORACLE_HOME=/usr/lib/oracle/11.2/client64
210 perl Makefile.PL
211 make
212 make install
213
214 Installing DBD::Oracle require that the three Oracle packages:
215 instant-client, SDK and SQLplus are installed as well as the libaio1
216 library.
217
218 If you are using Instant Client from ZIP archives, the LD_LIBRARY_PATH
219 and ORACLE_HOME will be the same and must be set to the directory where
220 you have installed the files. For example:
221 /opt/oracle/instantclient_12_2/
222
223CONFIGURATION
224 Ora2Pg configuration can be as simple as choosing the Oracle database to
225 export and choose the export type. This can be done in a minute.
226
227 By reading this documentation you will also be able to:
228
229 - Select only certain tables and/or column for export.
230 - Rename some tables and/or column during export.
231 - Select data to export following a WHERE clause per table.
232 - Delay database constraints during data loading.
233 - Compress exported data to save disk space.
234 - and much more.
235
236 The full control of the Oracle database migration is taken though a
237 single configuration file named ora2pg.conf. The format of this file
238 consist in a directive name in upper case followed by tab character and
239 a value. Comments are lines beginning with a #.
240
241 There's no specific order to place the configuration directives, they
242 are set at the time they are read in the configuration file.
243
244 For configuration directives that just take a single value, you can use
245 them multiple time in the configuration file but only the last
246 occurrence found in the file will be used. For configuration directives
247 that allow a list of value, you can use it multiple time, the values
248 will be appended to the list. If you use the IMPORT directive to load a
249 custom configuration file, directives defined in this file will be
250 stores from the place the IMPORT directive is found, so it is better to
251 put it at the end of the configuration file.
252
253 Values set in command line options will override values from the
254 configuration file.
255
256 Ora2Pg usage
257 First of all be sure that libraries and binaries path include the Oracle
258 Instant Client installation:
259
260 export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
261 export PATH="/usr/lib/oracle/11.2/client64/bin:$PATH"
262
263 By default Ora2Pg will look for /etc/ora2pg/ora2pg.conf configuration
264 file, if the file exist you can simply execute:
265
266 /usr/local/bin/ora2pg
267
268 or under Windows(tm) run ora2pg.bat file, located in your perl bin
269 directory. Windows(tm) users may also find a template configuration file
270 in C:\ora2pg
271
272 If you want to call another configuration file, just give the path as
273 command line argument:
274
275 /usr/local/bin/ora2pg -c /etc/ora2pg/new_ora2pg.conf
276
277 Here are all command line parameters available when using ora2pg:
278
279 Usage: ora2pg [-dhpqv --estimate_cost --dump_as_html] [--option value]
280
281 -a | --allow str : Comma separated list of objects to allow from export.
282 Can be used with SHOW_COLUMN too.
283 -b | --basedir dir: Set the default output directory, where files
284 resulting from exports will be stored.
285 -c | --conf file : Set an alternate configuration file other than the
286 default /etc/ora2pg/ora2pg.conf.
287 -d | --debug : Enable verbose output.
288 -D | --data_type STR : Allow custom type replacement at command line.
289 -e | --exclude str: Comma separated list of objects to exclude from export.
290 Can be used with SHOW_COLUMN too.
291 -h | --help : Print this short help.
292 -g | --grant_object type : Extract privilege from the given object type.
293 See possible values with GRANT_OBJECT configuration.
294 -i | --input file : File containing Oracle PL/SQL code to convert with
295 no Oracle database connection initiated.
296 -j | --jobs num : Number of parallel process to send data to PostgreSQL.
297 -J | --copies num : Number of parallel connections to extract data from Oracle.
298 -l | --log file : Set a log file. Default is stdout.
299 -L | --limit num : Number of tuples extracted from Oracle and stored in
300 memory before writing, default: 10000.
301 -m | --mysql : Export a MySQL database instead of an Oracle schema.
302 -n | --namespace schema : Set the Oracle schema to extract from.
303 -N | --pg_schema schema : Set PostgreSQL's search_path.
304 -o | --out file : Set the path to the output file where SQL will
305 be written. Default: output.sql in running directory.
306 -p | --plsql : Enable PLSQL to PLPGSQL code conversion.
307 -P | --parallel num: Number of parallel tables to extract at the same time.
308 -q | --quiet : Disable progress bar.
309 -r | --relative : use \ir instead of \i in the psql scripts generated.
310 -s | --source DSN : Allow to set the Oracle DBI datasource.
311 -t | --type export: Set the export type. It will override the one
312 given in the configuration file (TYPE).
313 -T | --temp_dir DIR: Set a distinct temporary directory when two
314 or more ora2pg are run in parallel.
315 -u | --user name : Set the Oracle database connection user.
316 ORA2PG_USER environment variable can be used instead.
317 -v | --version : Show Ora2Pg Version and exit.
318 -w | --password pwd : Set the password of the Oracle database user.
319 ORA2PG_PASSWD environment variable can be used instead.
320 --forceowner : Force ora2pg to set tables and sequences owner like in
321 Oracle database. If the value is set to a username this one
322 will be used as the objects owner. By default it's the user
323 used to connect to the Pg database that will be the owner.
324 --nls_lang code: Set the Oracle NLS_LANG client encoding.
325 --client_encoding code: Set the PostgreSQL client encoding.
326 --view_as_table str: Comma separated list of views to export as table.
327 --estimate_cost : Activate the migration cost evaluation with SHOW_REPORT
328 --cost_unit_value minutes: Number of minutes for a cost evaluation unit.
329 default: 5 minutes, corresponds to a migration conducted by a
330 PostgreSQL expert. Set it to 10 if this is your first migration.
331 --dump_as_html : Force ora2pg to dump report in HTML, used only with
332 SHOW_REPORT. Default is to dump report as simple text.
333 --dump_as_csv : As above but force ora2pg to dump report in CSV.
334 --dump_as_sheet : Report migration assessment with one CSV line per database.
335 --init_project NAME: Initialise a typical ora2pg project tree. Top directory
336 will be created under project base dir.
337 --project_base DIR : Define the base dir for ora2pg project trees. Default
338 is current directory.
339 --print_header : Used with --dump_as_sheet to print the CSV header
340 especially for the first run of ora2pg.
341 --human_days_limit num : Set the number of human-days limit where the migration
342 assessment level switch from B to C. Default is set to
343 5 human-days.
344 --audit_user LIST : Comma separated list of usernames to filter queries in
345 the DBA_AUDIT_TRAIL table. Used only with SHOW_REPORT
346 and QUERY export type.
347 --pg_dsn DSN : Set the datasource to PostgreSQL for direct import.
348 --pg_user name : Set the PostgreSQL user to use.
349 --pg_pwd password : Set the PostgreSQL password to use.
350 --count_rows : Force ora2pg to perform a real row count in TEST action.
351 --no_header : Do not append Ora2Pg header to output file
352 --oracle_speed : Use to know at which speed Oracle is able to send
353 data. No data will be processed or written.
354 --ora2pg_speed : Use to know at which speed Ora2Pg is able to send
355 transformed data. Nothing will be written.
356
357 See full documentation at http://ora2pg.darold.net/ for more help or see
358 manpage with 'man ora2pg'.
359
360 ora2pg will return 0 on success, 1 on error. It will return 2 when a
361 child process has been interrupted and you've gotten the warning
362 message: "WARNING: an error occurs during data export. Please check
363 what's happen." Most of the time this is an OOM issue, first try
364 reducing DATA_LIMIT value.
365
366 For developers, it is possible to add your own custom option(s) in the
367 Perl script ora2pg as any configuration directive from ora2pg.conf can
368 be passed in lower case to the new Ora2Pg object instance. See ora2pg
369 code on how to add your own option.
370
371 Note that performance might be improved by updating stats on oracle:
372
373 BEGIN
374 DBMS_STATS.GATHER_SCHEMA_STATS
375 DBMS_STATS.GATHER_DATABASE_STATS
376 DBMS_STATS.GATHER_DICTIONARY_STATS
377 END;
378
379 Generate a migration template
380 The two options --project_base and --init_project when used indicate to
381 ora2pg that he has to create a project template with a work tree, a
382 configuration file and a script to export all objects from the Oracle
383 database. Here a sample of the command usage:
384
385 ora2pg --project_base /app/migration/ --init_project test_project
386 Creating project test_project.
387 /app/migration/test_project/
388 schema/
389 dblinks/
390 directories/
391 functions/
392 grants/
393 mviews/
394 packages/
395 partitions/
396 procedures/
397 sequences/
398 synonyms/
399 tables/
400 tablespaces/
401 triggers/
402 types/
403 views/
404 sources/
405 functions/
406 mviews/
407 packages/
408 partitions/
409 procedures/
410 triggers/
411 types/
412 views/
413 data/
414 config/
415 reports/
416
417 Generating generic configuration file
418 Creating script export_schema.sh to automate all exports.
419 Creating script import_all.sh to automate all imports.
420
421 It create a generic config file where you just have to define the Oracle
422 database connection and a shell script called export_schema.sh. The
423 sources/ directory will contains the Oracle code, the schema/ will
424 contains the code ported to PostgreSQL. The reports/ directory will
425 contains the html reports with the migration cost assessment.
426
427 If you want to use your own default config file, use the -c option to
428 give the path to that file. Rename it with .dist suffix if you want
429 ora2pg to apply the generic configuration values otherwise, the
430 configuration file will be copied untouched.
431
432 Once you have set the connection to the Oracle Database you can execute
433 the script export_schema.sh that will export all object type from your
434 Oracle database and output DDL files into the schema's subdirectories.
435 At end of the export it will give you the command to export data later
436 when the import of the schema will be done and verified.
437
438 You can choose to load the DDL files generated manually or use the
439 second script import_all.sh to import those file interactively. If this
440 kind of migration is not something current for you it's recommended you
441 to use those scripts.
442
443 Oracle database connection
444 There's 5 configuration directives to control the access to the Oracle
445 database.
446
447 ORACLE_HOME
448 Used to set ORACLE_HOME environment variable to the Oracle libraries
449 required by the DBD::Oracle Perl module.
450
451 ORACLE_DSN
452 This directive is used to set the data source name in the form
453 standard DBI DSN. For example:
454
455 dbi:Oracle:host=oradb_host.myhost.com;sid=DB_SID;port=1521
456
457 or
458
459 dbi:Oracle:DB_SID
460
461 On 18c this could be for example:
462
463 dbi:Oracle:host=192.168.1.29;service_name=pdb1;port=1521
464
465 for the second notation the SID should be declared in the well known
466 file $ORACLE_HOME/network/admin/tnsnames.ora or in the path given to
467 the TNS_ADMIN environment variable.
468
469 For MySQL the DSN will lool like this:
470
471 dbi:mysql:host=192.168.1.10;database=sakila;port=3306
472
473 the 'sid' part is replaced by 'database'.
474
475 ORACLE_USER et ORACLE_PWD
476 These two directives are used to define the user and password for
477 the Oracle database connection. Note that if you can it is better to
478 login as Oracle super admin to avoid grants problem during the
479 database scan and be sure that nothing is missing.
480
481 If you do not supply a credential with ORACLE_PWD and you have
482 installed the Term::ReadKey Perl module, Ora2Pg will ask for the
483 password interactively. If ORACLE_USER is not set it will be asked
484 interactively too.
485
486 To connect to a local ORACLE instance with connections "as sysdba"
487 you have to set ORACLE_USER to "/" and an empty password.
488
489 USER_GRANTS
490 Set this directive to 1 if you connect the Oracle database as simple
491 user and do not have enough grants to extract things from the
492 DBA_... tables. It will use tables ALL_... instead.
493
494 Warning: if you use export type GRANT, you must set this
495 configuration option to 0 or it will not work.
496
497 TRANSACTION
498 This directive may be used if you want to change the default
499 isolation level of the data export transaction. Default is now to
500 set the level to a serializable transaction to ensure data
501 consistency. The allowed values for this directive are:
502
503 readonly: 'SET TRANSACTION READ ONLY',
504 readwrite: 'SET TRANSACTION READ WRITE',
505 serializable: 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'
506 committed: 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED',
507
508 Releases before 6.2 used to set the isolation level to READ ONLY
509 transaction but in some case this was breaking data consistency so
510 now default is set to SERIALIZABLE.
511
512 INPUT_FILE
513 This directive did not control the Oracle database connection or
514 unless it purely disables the use of any Oracle database by
515 accepting a file as argument. Set this directive to a file
516 containing PL/SQL Oracle Code like function, procedure or full
517 package body to prevent Ora2Pg from connecting to an Oracle database
518 and just apply his conversion tool to the content of the file. This
519 can be used with the most of export types: TABLE, TRIGGER,
520 PROCEDURE, VIEW, FUNCTION or PACKAGE, etc.
521
522 ORA_INITIAL_COMMAND
523 This directive can be used to send an initial command to Oracle,
524 just after the connection. For example to unlock a policy before
525 reading objects or to set some session parameters. This directive
526 can be used multiple times.
527
528 Data encryption with Oracle server
529 If your Oracle Client config file already includes the encryption
530 method, then DBD:Oracle uses those settings to encrypt the connection
531 while you extract the data. For example if you have configured the
532 Oracle Client config file (sqlnet.or or .sqlnet) with the following
533 information:
534
535 # Configure encryption of connections to Oracle
536 SQLNET.ENCRYPTION_CLIENT = required
537 SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256, RC4_256)
538 SQLNET.CRYPTO_SEED = 'should be 10-70 random characters'
539
540 Any tool that uses the Oracle client to talk to the database will be
541 encrypted if you setup session encryption like above.
542
543 For example, Perl's DBI uses DBD-Oracle, which uses the Oracle client
544 for actually handling database communication. If the installation of
545 Oracle client used by Perl is setup to request encrypted connections,
546 then your Perl connection to an Oracle database will also be encrypted.
547
548 Full details at
549 https://kb.berkeley.edu/jivekb/entry.jspa?externalID=1005
550
551 Testing connection
552 Once you have set the Oracle database DSN you can execute ora2pg to see
553 if it works:
554
555 ora2pg -t SHOW_VERSION -c config/ora2pg.conf
556
557 will show the Oracle database server version. Take some time here to
558 test your installation as most problems take place here, the other
559 configuration steps are more technical.
560
561 Troubleshooting
562 If the output.sql file has not exported anything other than the Pg
563 transaction header and footer there's two possible reasons. The perl
564 script ora2pg dump an ORA-XXX error, that mean that your DSN or login
565 information are wrong, check the error and your settings and try again.
566 The perl script says nothing and the output file is empty: the user
567 lacks permission to extract something from the database. Try to connect
568 to Oracle as super user or take a look at directive USER_GRANTS above
569 and at next section, especially the SCHEMA directive.
570
571 LOGFILE
572 By default all messages are sent to the standard output. If you give
573 a file path to that directive, all output will be appended to this
574 file.
575
576 Oracle schema to export
577 The Oracle database export can be limited to a specific Schema or
578 Namespace, this can be mandatory following the database connection user.
579
580 SCHEMA
581 This directive is used to set the schema name to use during export.
582 For example:
583
584 SCHEMA APPS
585
586 will extract objects associated to the APPS schema.
587
588 When no schema name is provided and EXPORT_SCHEMA is enabled, Ora2Pg
589 will export all objects from all schema of the Oracle instance with
590 their names prefixed with the schema name.
591
592 EXPORT_SCHEMA
593 By default the Oracle schema is not exported into the PostgreSQL
594 database and all objects are created under the default Pg namespace.
595 If you want to also export this schema and create all objects under
596 this namespace, set the EXPORT_SCHEMA directive to 1. This will set
597 the schema search_path at top of export SQL file to the schema name
598 set in the SCHEMA directive with the default pg_catalog schema. If
599 you want to change this path, use the directive PG_SCHEMA.
600
601 CREATE_SCHEMA
602 Enable/disable the CREATE SCHEMA SQL order at starting of the output
603 file. It is enable by default and concern on TABLE export type.
604
605 COMPILE_SCHEMA
606 By default Ora2Pg will only export valid PL/SQL code. You can force
607 Oracle to compile again the invalidated code to get a chance to have
608 it obtain the valid status and then be able to export it.
609
610 Enable this directive to force Oracle to compile schema before
611 exporting code. When this directive is enabled and SCHEMA is set to
612 a specific schema name, only invalid objects in this schema will be
613 recompiled. If SCHEMA is not set then all schema will be recompiled.
614 To force recompile invalid object in a specific schema, set
615 COMPILE_SCHEMA to the schema name you want to recompile.
616
617 This will ask to Oracle to validate the PL/SQL that could have been
618 invalidate after a export/import for example. The 'VALID' or
619 'INVALID' status applies to functions, procedures, packages and user
620 defined types.
621
622 EXPORT_INVALID
623 If the above configuration directive is not enough to validate your
624 PL/SQL code enable this configuration directive to allow export of
625 all PL/SQL code even if it is marked as invalid. The 'VALID' or
626 'INVALID' status applies to functions, procedures, packages and user
627 defined types.
628
629 PG_SCHEMA
630 Allow you to defined/force the PostgreSQL schema to use. By default
631 if you set EXPORT_SCHEMA to 1 the PostgreSQL search_path will be set
632 to the schema name exported set as value of the SCHEMA directive.
633
634 The value can be a comma delimited list of schema name but not when
635 using TABLE export type because in this case it will generate the
636 CREATE SCHEMA statement and it doesn't support multiple schema name.
637 For example, if you set PG_SCHEMA to something like "user_schema,
638 public", the search path will be set like this:
639
640 SET search_path = user_schema, public;
641
642 forcing the use of an other schema (here user_schema) than the one
643 from Oracle schema set in the SCHEMA directive.
644
645 You can also set the default search_path for the PostgreSQL user you
646 are using to connect to the destination database by using:
647
648 ALTER ROLE username SET search_path TO user_schema, public;
649
650 in this case you don't have to set PG_SCHEMA.
651
652 SYSUSERS
653 Without explicit schema, Ora2Pg will export all objects that not
654 belongs to system schema or role:
655
656 SYSTEM,CTXSYS,DBSNMP,EXFSYS,LBACSYS,MDSYS,MGMT_VIEW,
657 OLAPSYS,ORDDATA,OWBSYS,ORDPLUGINS,ORDSYS,OUTLN,
658 SI_INFORMTN_SCHEMA,SYS,SYSMAN,WK_TEST,WKSYS,WKPROXY,
659 WMSYS,XDB,APEX_PUBLIC_USER,DIP,FLOWS_020100,FLOWS_030000,
660 FLOWS_040100,FLOWS_010600,FLOWS_FILES,MDDATA,ORACLE_OCM,
661 SPATIAL_CSW_ADMIN_USR,SPATIAL_WFS_ADMIN_USR,XS$NULL,PERFSTAT,
662 SQLTXPLAIN,DMSYS,TSMSYS,WKSYS,APEX_040000,APEX_040200,
663 DVSYS,OJVMSYS,GSMADMIN_INTERNAL,APPQOSSYS,DVSYS,DVF,
664 AUDSYS,APEX_030200,MGMT_VIEW,ODM,ODM_MTR,TRACESRV,MTMSYS,
665 OWBSYS_AUDIT,WEBSYS,WK_PROXY,OSE$HTTP$ADMIN,
666 AURORA$JIS$UTILITY$,AURORA$ORB$UNAUTHENTICATED,
667 DBMS_PRIVILEGE_CAPTURE,CSMIG,MGDSYS,SDE,DBSFWUSER
668
669 Following your Oracle installation you may have several other system
670 role defined. To append these users to the schema exclusion list,
671 just set the SYSUSERS configuration directive to a comma-separated
672 list of system user to exclude. For example:
673
674 SYSUSERS INTERNAL,SYSDBA,BI,HR,IX,OE,PM,SH
675
676 will add users INTERNAL and SYSDBA to the schema exclusion list.
677
678 FORCE_OWNER
679 By default the owner of the database objects is the one you're using
680 to connect to PostgreSQL using the psql command. If you use an other
681 user (postgres for example) you can force Ora2Pg to set the object
682 owner to be the one used in the Oracle database by setting the
683 directive to 1, or to a completely different username by setting the
684 directive value to that username.
685
686 FORCE_SECURITY_INVOKER
687 Ora2Pg use the function's security privileges set in Oracle and it
688 is often defined as SECURITY DEFINER. If you want to override those
689 security privileges for all functions and use SECURITY DEFINER
690 instead, enable this directive.
691
692 USE_TABLESPACE
693 When enabled this directive force ora2pg to export all tables,
694 indexes constraint and indexes using the tablespace name defined in
695 Oracle database. This works only with tablespace that are not TEMP,
696 USERS and SYSTEM.
697
698 WITH_OID
699 Activating this directive will force Ora2Pg to add WITH (OIDS) when
700 creating tables or views as tables. Default is same as PostgreSQL,
701 disabled.
702
703 LOOK_FORWARD_FUNCTION
704 List of schema to get functions/procedures meta information that are
705 used in the current schema export. When replacing call to function
706 with OUT parameters, if a function is declared in an other package
707 then the function call rewriting can not be done because Ora2Pg only
708 knows about functions declared in the current schema. By setting a
709 comma separated list of schema as value of this directive, Ora2Pg
710 will look forward in these packages for all
711 functions/procedures/packages declaration before proceeding to
712 current schema export.
713
714 NO_FUNCTION_METADATA
715 Force Ora2Pg to not look for function declaration. Note that this
716 will prevent Ora2Pg to rewrite function replacement call if needed.
717 Do not enable it unless looking forward at function breaks other
718 export.
719
720 Export type
721 The export action is perform following a single configuration directive
722 'TYPE', some other add more control on what should be really exported.
723
724 TYPE
725 Here are the different values of the TYPE directive, default is
726 TABLE:
727
728 - TABLE: Extract all tables with indexes, primary keys, unique keys,
729 foreign keys and check constraints.
730 - VIEW: Extract only views.
731 - GRANT: Extract roles converted to Pg groups, users and grants on all
732 objects.
733 - SEQUENCE: Extract all sequence and their last position.
734 - TABLESPACE: Extract storage spaces for tables and indexes (Pg >= v8).
735 - TRIGGER: Extract triggers defined following actions.
736 - FUNCTION: Extract functions.
737 - PROCEDURE: Extract procedures.
738 - PACKAGE: Extract packages and package bodies.
739 - INSERT: Extract data as INSERT statement.
740 - COPY: Extract data as COPY statement.
741 - PARTITION: Extract range and list Oracle partitions with subpartitions.
742 - TYPE: Extract user defined Oracle type.
743 - FDW: Export Oracle tables as foreign table for oracle_fdw.
744 - MVIEW: Export materialized view.
745 - QUERY: Try to automatically convert Oracle SQL queries.
746 - KETTLE: Generate XML ktr template files to be used by Kettle.
747 - DBLINK: Generate oracle foreign data wrapper server to use as dblink.
748 - SYNONYM: Export Oracle's synonyms as views on other schema's objects.
749 - DIRECTORY: Export Oracle's directories as external_file extension objects.
750 - LOAD: Dispatch a list of queries over multiple PostgreSQl connections.
751 - TEST: perform a diff between Oracle and PostgreSQL database.
752 - TEST_VIEW: perform a count on both side of rows returned by views
753
754 Only one type of export can be perform at the same time so the TYPE
755 directive must be unique. If you have more than one only the last
756 found in the file will be registered.
757
758 Some export type can not or should not be load directly into the
759 PostgreSQL database and still require little manual editing. This is
760 the case for GRANT, TABLESPACE, TRIGGER, FUNCTION, PROCEDURE, TYPE,
761 QUERY and PACKAGE export types especially if you have PLSQL code or
762 Oracle specific SQL in it.
763
764 For TABLESPACE you must ensure that file path exist on the system
765 and for SYNONYM you may ensure that the object's owners and schemas
766 correspond to the new PostgreSQL database design.
767
768 Note that you can chained multiple export by giving to the TYPE
769 directive a comma-separated list of export type, but in this case
770 you must not use COPY or INSERT with other export type.
771
772 Ora2Pg will convert Oracle partition using table inheritance,
773 trigger and functions. See document at Pg site:
774 http://www.postgresql.org/docs/current/interactive/ddl-partitioning.
775 html
776
777 The TYPE export allow export of user defined Oracle type. If you
778 don't use the --plsql command line parameter it simply dump Oracle
779 user type asis else Ora2Pg will try to convert it to PostgreSQL
780 syntax.
781
782 The KETTLE export type requires that the Oracle and PostgreSQL DNS
783 are defined.
784
785 Since Ora2Pg v8.1 there's three new export types:
786
787 SHOW_VERSION : display Oracle version
788 SHOW_SCHEMA : display the list of schema available in the database.
789 SHOW_TABLE : display the list of tables available.
790 SHOW_COLUMN : display the list of tables columns available and the
791 Ora2PG conversion type from Oracle to PostgreSQL that will be
792 applied. It will also warn you if there's PostgreSQL reserved
793 words in Oracle object names.
794
795 Here is an example of the SHOW_COLUMN output:
796
797 [2] TABLE CURRENT_SCHEMA (1 rows) (Warning: 'CURRENT_SCHEMA' is a reserved word in PostgreSQL)
798 CONSTRAINT : NUMBER(22) => bigint (Warning: 'CONSTRAINT' is a reserved word in PostgreSQL)
799 FREEZE : VARCHAR2(25) => varchar(25) (Warning: 'FREEZE' is a reserved word in PostgreSQL)
800 ...
801 [6] TABLE LOCATIONS (23 rows)
802 LOCATION_ID : NUMBER(4) => smallint
803 STREET_ADDRESS : VARCHAR2(40) => varchar(40)
804 POSTAL_CODE : VARCHAR2(12) => varchar(12)
805 CITY : VARCHAR2(30) => varchar(30)
806 STATE_PROVINCE : VARCHAR2(25) => varchar(25)
807 COUNTRY_ID : CHAR(2) => char(2)
808
809 Those extraction keywords are use to only display the requested
810 information and exit. This allows you to quickly know on what you
811 are going to work.
812
813 The SHOW_COLUMN allow an other ora2pg command line option: '--allow
814 relname' or '-a relname' to limit the displayed information to the
815 given table.
816
817 The SHOW_ENCODING export type will display the NLS_LANG and
818 CLIENT_ENCODING values that Ora2Pg will used and the real encoding
819 of the Oracle database with the corresponding client encoding that
820 could be used with PostgreSQL
821
822 Since release v8.12, Ora2Pg allow you to export your Oracle Table
823 definition to be use with the oracle_fdw foreign data wrapper. By
824 using type FDW your Oracle tables will be exported as follow:
825
826 CREATE FOREIGN TABLE oratab (
827 id integer NOT NULL,
828 text character varying(30),
829 floating double precision NOT NULL
830 ) SERVER oradb OPTIONS (table 'ORATAB');
831
832 Now you can use the table like a regular PostgreSQL table.
833
834 See http://pgxn.org/dist/oracle_fdw/ for more information on this
835 foreign data wrapper.
836
837 Release 10 adds a new export type destined to evaluate the content
838 of the database to migrate, in terms of objects and cost to end the
839 migration:
840
841 SHOW_REPORT : show a detailed report of the Oracle database content.
842
843 Here is a sample of report: http://ora2pg.darold.net/report.html
844
845 There also a more advanced report with migration cost. See the
846 dedicated chapter about Migration Cost Evaluation.
847
848 ESTIMATE_COST
849 Activate the migration cost evaluation. Must only be used with
850 SHOW_REPORT, FUNCTION, PROCEDURE, PACKAGE and QUERY export type.
851 Default is disabled. You may want to use the --estimate_cost command
852 line option instead to activate this functionality. Note that
853 enabling this directive will force PLSQL_PGSQL activation.
854
855 COST_UNIT_VALUE
856 Set the value in minutes of the migration cost evaluation unit.
857 Default is five minutes per unit. See --cost_unit_value to change
858 the unit value at command line.
859
860 DUMP_AS_HTML
861 By default when using SHOW_REPORT the migration report is generated
862 as simple text, enabling this directive will force ora2pg to create
863 a report in HTML format.
864
865 See http://ora2pg.darold.net/report.html for a sample report.
866
867 HUMAN_DAYS_LIMIT
868 Use this directive to redefined the number of human-days limit where
869 the migration assessment level must switch from B to C. Default is
870 set to 10 human-days.
871
872 JOBS
873 This configuration directive adds multiprocess support to COPY,
874 FUNCTION and PROCEDURE export type, the value is the number of
875 process to use. Default is multiprocess disable.
876
877 This directive is used to set the number of cores to used to
878 parallelize data import into PostgreSQL. During FUNCTION or
879 PROCEDURE export type each function will be translated to plpgsql
880 using a new process, the performances gain can be very important
881 when you have tons of function to convert.
882
883 There's no limitation in parallel processing than the number of
884 cores and the PostgreSQL I/O performance capabilities.
885
886 Doesn't work under Windows Operating System, it is simply disabled.
887
888 ORACLE_COPIES
889 This configuration directive adds multiprocess support to extract
890 data from Oracle. The value is the number of process to use to
891 parallelize the select query. Default is parallel query disable.
892
893 The parallelism is built on splitting the query following of the
894 number of cores given as value to ORACLE_COPIES as follow:
895
896 SELECT * FROM MYTABLE WHERE ABS(MOD(COLUMN, ORACLE_COPIES)) = CUR_PROC
897
898 where COLUMN is a technical key like a primary or unique key where
899 split will be based and the current core used by the query
900 (CUR_PROC).
901
902 Doesn't work under Windows Operating System, it is simply disabled.
903
904 DEFINED_PK
905 This directive is used to defined the technical key to used to split
906 the query between number of cores set with the ORACLE_COPIES
907 variable. For example:
908
909 DEFINED_PK EMPLOYEES:employee_id
910
911 The parallel query that will be used supposing that -J or
912 ORACLE_COPIES is set to 8:
913
914 SELECT * FROM EMPLOYEES WHERE ABS(MOD(employee_id, 8)) = N
915
916 where N is the current process forked starting from 0.
917
918 PARALLEL_TABLES
919 This directive is used to defined the number of tables that will be
920 processed in parallel for data extraction. The limit is the number
921 of cores on your machine. Ora2Pg will open one database connection
922 for each parallel table extraction. This directive, when upper than
923 1, will invalidate ORACLE_COPIES but not JOBS, so the real number of
924 process that will be used is PARALLEL_TABLES * JOBS.
925
926 Note that this directive when set upper that 1 will also
927 automatically enable the FILE_PER_TABLE directive if your are
928 exporting to files.
929
930 DEFAULT_PARALLELISM_DEGREE
931 You can force Ora2Pg to use /*+ PARALLEL(tbname, degree) */ hint in
932 each query used to export data from Oracle by setting a value upper
933 than 1 to this directive. A value of 0 or 1 disable the use of
934 parallel hint. Default is disabled.
935
936 FDW_SERVER
937 This directive is used to set the name of the foreign data server
938 that is used in the "CREATE SERVER name FOREIGN DATA WRAPPER
939 oracle_fdw ..." command. This name will then be used in the "CREATE
940 FOREIGN TABLE ..." SQL commands and to import data using oracle_fdw.
941 Default is no foreign server defined. This only concerns export type
942 FDW, COPY and INSERT. For export type FDW the default value is orcl.
943
944 ORACLE_FDW_TRANSFORM
945 Use this directive to precise which transformation should be applied
946 to a column when exporting data. Value must be a semicolon separated
947 list of
948
949 TABLE[COLUMN_NAME, <replace code in SELECT target list>]
950
951 For example to replace string 'Oracle' by 'PostgreSQL' in a varchar2
952 column use the following.
953
954 ORACLE_FDW_TRANSFORM ERROR_LOG_SAMPLE[DBMS_TYPE:regexp_replace("DBMS_TYPE",'Oracle','PostgreSQL')]
955
956 EXTERNAL_TO_FDW
957 This directive, enabled by default, allow to export Oracle's
958 External Tables as file_fdw foreign tables. To not export these
959 tables at all, set the directive to 0.
960
961 INTERNAL_DATE_MAX
962 Internal timestamp retrieves from custom type are extracted in the
963 following format: 01-JAN-77 12.00.00.000000 AM. It is impossible to
964 know the exact century that must be used, so by default any year
965 below 49 will be added to 2000 and others to 1900. You can use this
966 directive to change the default value 49. this is only relevant if
967 you have user defined type with a column timestamp.
968
969 AUDIT_USER
970 Set the comma separated list of username that must be used to filter
971 queries from the DBA_AUDIT_TRAIL table. Default is to not scan this
972 table and to never look for queries. This parameter is used only
973 with SHOW_REPORT and QUERY export type with no input file for
974 queries. Note that queries will be normalized before output unlike
975 when a file is given at input using the -i option or INPUT
976 directive.
977
978 FUNCTION_CHECK
979 Disable this directive if you want to disable check_function_bodies.
980
981 SET check_function_bodies = false;
982
983 It disables validation of the function body string during CREATE
984 FUNCTION. Default is to use de postgresql.conf setting that enable
985 it by default.
986
987 ENABLE_BLOB_EXPORT
988 Exporting BLOB takes time, in some circumstances you may want to
989 export all data except the BLOB columns. In this case disable this
990 directive and the BLOB columns will not be included into data
991 export. Take care that the target bytea column do not have a NOT
992 NULL constraint.
993
994 DATA_EXPORT_ORDER
995 By default data export order will be done by sorting on table name.
996 If you have huge tables at end of alphabetic order and you are using
997 multiprocess, it can be better to set the sort order on size so that
998 multiple small tables can be processed before the largest tables
999 finish. In this case set this directive to size. Possible values are
1000 name and size. Note that export type SHOW_TABLE and SHOW_COLUMN will
1001 use this sort order too, not only COPY or INSERT export type.
1002
1003 Limiting objects to export
1004 You may want to export only a part of an Oracle database, here are a set
1005 of configuration directives that will allow you to control what parts of
1006 the database should be exported.
1007
1008 ALLOW
1009 This directive allows you to set a list of objects on which the
1010 export must be limited, excluding all other objects in the same type
1011 of export. The value is a space or comma-separated list of objects
1012 name to export. You can include valid regex into the list. For
1013 example:
1014
1015 ALLOW EMPLOYEES SALE_.* COUNTRIES .*_GEOM_SEQ
1016
1017 will export objects with name EMPLOYEES, COUNTRIES, all objects
1018 beginning with 'SALE_' and all objects with a name ending by
1019 '_GEOM_SEQ'. The object depends of the export type. Note that regex
1020 will not works with 8i database, you must use the % placeholder
1021 instead, Ora2Pg will use the LIKE operator.
1022
1023 This is the manner to declare global filters that will be used with
1024 the current export type. You can also use extended filters that will
1025 be applied on specific objects or only on their related export type.
1026 For example:
1027
1028 ora2pg -p -c ora2pg.conf -t TRIGGER -a 'TABLE[employees]'
1029
1030 will limit export of trigger to those defined on table employees. If
1031 you want to extract all triggers but not some INSTEAD OF triggers:
1032
1033 ora2pg -c ora2pg.conf -t TRIGGER -e 'VIEW[trg_view_.*]'
1034
1035 Or a more complex form:
1036
1037 ora2pg -p -c ora2pg.conf -t TABLE -a 'TABLE[EMPLOYEES]' \
1038 -e 'INDEX[emp_.*];CKEY[emp_salary_min]'
1039
1040 This command will export the definition of the employee table but
1041 will exclude all index beginning with 'emp_' and the CHECK
1042 constraint called 'emp_salary_min'.
1043
1044 When exporting partition you can exclude some partition tables by
1045 using
1046
1047 ora2pg -p -c ora2pg.conf -t PARTITION -e 'PARTITION[PART_199.* PART_198.*]'
1048
1049 This will exclude partitioned tables for year 1980 to 1999 from the
1050 export but not the main partition table. The trigger will also be
1051 adapted to exclude those table.
1052
1053 With GRANT export you can use this extended form to exclude some
1054 users from the export or limit the export to some others:
1055
1056 ora2pg -p -c ora2pg.conf -t GRANT -a 'USER1 USER2'
1057
1058 or
1059
1060 ora2pg -p -c ora2pg.conf -t GRANT -a 'GRANT[USER1 USER2]'
1061
1062 will limit export grants to users USER1 and USER2. But if you don't
1063 want to export grants on some functions for these users, for
1064 example:
1065
1066 ora2pg -p -c ora2pg.conf -t GRANT -a 'USER1 USER2' -e 'FUNCTION[adm_.*];PROCEDURE[adm_.*]'
1067
1068 Advanced filters may need some learning.
1069
1070 Oracle doesn't allow the use of lookahead expression so you may want
1071 to exclude some object that match the ALLOW regexp you have defined.
1072 For example if you want to export all table starting with E but not
1073 those starting with EXP it is not possible to do that in a single
1074 expression. This is why you can start a regular expression with the
1075 ! character to exclude object matching the regexp given just after.
1076 Our previous example can be written as follow:
1077
1078 ALLOW E.* !EXP.*
1079
1080 it will be translated into:
1081
1082 REGEXP_LIKE(..., '^E.*$') AND NOT REGEXP_LIKE(..., '^EXP.*$')
1083
1084 in the object search expression.
1085
1086 EXCLUDE
1087 This directive is the opposite of the previous, it allow you to
1088 define a space or comma-separated list of object name to exclude
1089 from the export. You can include valid regex into the list. For
1090 example:
1091
1092 EXCLUDE EMPLOYEES TMP_.* COUNTRIES
1093
1094 will exclude object with name EMPLOYEES, COUNTRIES and all tables
1095 beginning with 'tmp_'.
1096
1097 For example, you can ban from export some unwanted function with
1098 this directive:
1099
1100 EXCLUDE write_to_.* send_mail_.*
1101
1102 this example will exclude all functions, procedures or functions in
1103 a package with the name beginning with those regex. Note that regex
1104 will not work with 8i database, you must use the % placeholder
1105 instead, Ora2Pg will use the NOT LIKE operator.
1106
1107 See above (directive 'ALLOW') for the extended syntax.
1108
1109 VIEW_AS_TABLE
1110 Set which view to export as table. By default none. Value must be a
1111 list of view name or regexp separated by space or comma. If the
1112 object name is a view and the export type is TABLE, the view will be
1113 exported as a create table statement. If export type is COPY or
1114 INSERT, the corresponding data will be exported.
1115
1116 See chapter "Exporting views as PostgreSQL table" for more details.
1117
1118 NO_VIEW_ORDERING
1119 By default Ora2Pg try to order views to avoid error at import time
1120 with nested views. With a huge number of views this can take a very
1121 long time, you can bypass this ordering by enabling this directive.
1122
1123 GRANT_OBJECT
1124 When exporting GRANTs you can specify a comma separated list of
1125 objects for which privilege will be exported. Default is export for
1126 all objects. Here are the possibles values TABLE, VIEW, MATERIALIZED
1127 VIEW, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE BODY, TYPE, SYNONYM,
1128 DIRECTORY. Only one object type is allowed at a time. For example
1129 set it to TABLE if you just want to export privilege on tables. You
1130 can use the -g option to overwrite it.
1131
1132 When used this directive prevent the export of users unless it is
1133 set to USER. In this case only users definitions are exported.
1134
1135 WHERE
1136 This directive allows you to specify a WHERE clause filter when
1137 dumping the contents of tables. Value is constructs as follows:
1138 TABLE_NAME[WHERE_CLAUSE], or if you have only one where clause for
1139 each table just put the where clause as the value. Both are possible
1140 too. Here are some examples:
1141
1142 # Global where clause applying to all tables included in the export
1143 WHERE 1=1
1144
1145 # Apply the where clause only on table TABLE_NAME
1146 WHERE TABLE_NAME[ID1='001']
1147
1148 # Applies two different clause on tables TABLE_NAME and OTHER_TABLE
1149 # and a generic where clause on DATE_CREATE to all other tables
1150 WHERE TABLE_NAME[ID1='001' OR ID1='002] DATE_CREATE > '2001-01-01' OTHER_TABLE[NAME='test']
1151
1152 Any where clause not included into a table name bracket clause will
1153 be applied to all exported table including the tables defined in the
1154 where clause. These WHERE clauses are very useful if you want to
1155 archive some data or at the opposite only export some recent data.
1156
1157 To be able to quickly test data import it is useful to limit data
1158 export to the first thousand tuples of each table. For Oracle define
1159 the following clause:
1160
1161 WHERE ROWNUM < 1000
1162
1163 and for MySQL, use the following:
1164
1165 WHERE 1=1 LIMIT 1,1000
1166
1167 This can also be restricted to some tables data export.
1168
1169 TOP_MAX
1170 This directive is used to limit the number of item shown in the top
1171 N lists like the top list of tables per number of rows and the top
1172 list of largest tables in megabytes. By default it is set to 10
1173 items.
1174
1175 LOG_ON_ERROR
1176 Enable this directive if you want to continue direct data import on
1177 error. When Ora2Pg received an error in the COPY or INSERT statement
1178 from PostgreSQL it will log the statement to a file called
1179 TABLENAME_error.log in the output directory and continue to next
1180 bulk of data. Like this you can try to fix the statement and
1181 manually reload the error log file. Default is disabled: abort
1182 import on error.
1183
1184 REPLACE_QUERY
1185 Sometime you may want to extract data from an Oracle table but you
1186 need a custom query for that. Not just a "SELECT * FROM table" like
1187 Ora2Pg do but a more complex query. This directive allows you to
1188 overwrite the query used by Ora2Pg to extract data. The format is
1189 TABLENAME[SQL_QUERY]. If you have multiple table to extract by
1190 replacing the Ora2Pg query, you can define multiple REPLACE_QUERY
1191 lines.
1192
1193 REPLACE_QUERY EMPLOYEES[SELECT e.id,e.fisrtname,lastname FROM EMPLOYEES e JOIN EMP_UPDT u ON (e.id=u.id AND u.cdate>'2014-08-01 00:00:00')]
1194
1195 Control of Full Text Search export
1196 Several directives can be used to control the way Ora2Pg will export the
1197 Oracle's Text search indexes. By default CONTEXT indexes will be
1198 exported to PostgreSQL FTS indexes but CTXCAT indexes will be exported
1199 as indexes using the pg_trgm extension.
1200
1201 CONTEXT_AS_TRGM
1202 Force Ora2Pg to translate Oracle Text indexes into PostgreSQL
1203 indexes using pg_trgm extension. Default is to translate CONTEXT
1204 indexes into FTS indexes and CTXCAT indexes using pg_trgm. Most of
1205 the time using pg_trgm is enough, this is why this directive stand
1206 for. You need to create the pg_trgm extension into the destination
1207 database before importing the objects:
1208
1209 CREATE EXTENSION pg_trgm;
1210
1211 FTS_INDEX_ONLY
1212 By default Ora2Pg creates a function-based index to translate Oracle
1213 Text indexes.
1214
1215 CREATE INDEX ON t_document
1216 USING gin(to_tsvector('pg_catalog.french', title));
1217
1218 You will have to rewrite the CONTAIN() clause using to_tsvector(),
1219 example:
1220
1221 SELECT id,title FROM t_document
1222 WHERE to_tsvector(title)) @@ to_tsquery('search_word');
1223
1224 To force Ora2Pg to create an extra tsvector column with a dedicated
1225 triggers for FTS indexes, disable this directive. In this case,
1226 Ora2Pg will add the column as follow: ALTER TABLE t_document ADD
1227 COLUMN tsv_title tsvector; Then update the column to compute FTS
1228 vectors if data have been loaded before UPDATE t_document SET
1229 tsv_title = to_tsvector('pg_catalog.french', coalesce(title,'')); To
1230 automatically update the column when a modification in the title
1231 column appears, Ora2Pg adds the following trigger:
1232
1233 CREATE FUNCTION tsv_t_document_title() RETURNS trigger AS $$
1234 BEGIN
1235 IF TG_OP = 'INSERT' OR new.title != old.title THEN
1236 new.tsv_title :=
1237 to_tsvector('pg_catalog.french', coalesce(new.title,''));
1238 END IF;
1239 return new;
1240 END
1241 $$ LANGUAGE plpgsql;
1242 CREATE TRIGGER trig_tsv_t_document_title BEFORE INSERT OR UPDATE
1243 ON t_document
1244 FOR EACH ROW EXECUTE PROCEDURE tsv_t_document_title();
1245
1246 When the Oracle text index is defined over multiple column, Ora2Pg
1247 will use setweight() to set a weight in the order of the column
1248 declaration.
1249
1250 FTS_CONFIG
1251 Use this directive to force text search configuration to use. When
1252 it is not set, Ora2Pg will autodetect the stemmer used by Oracle for
1253 each index and pg_catalog.english if the information is not found.
1254
1255 USE_UNACCENT
1256 If you want to perform your text search in an accent insensitive
1257 way, enable this directive. Ora2Pg will create an helper function
1258 over unaccent() and creates the pg_trgm indexes using this function.
1259 With FTS Ora2Pg will redefine your text search configuration, for
1260 example:
1261
1262 CREATE TEXT SEARCH CONFIGURATION fr (COPY = french);
1263 ALTER TEXT SEARCH CONFIGURATION fr
1264 ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem;
1265
1266 then set the FTS_CONFIG ora2pg.conf directive to fr instead of
1267 pg_catalog.english.
1268
1269 When enabled, Ora2pg will create the wrapper function:
1270
1271 CREATE OR REPLACE FUNCTION unaccent_immutable(text)
1272 RETURNS text AS
1273 $$
1274 SELECT public.unaccent('public.unaccent', $1);
1275 $$ LANGUAGE sql IMMUTABLE
1276 COST 1;
1277
1278 the indexes are exported as follow:
1279
1280 CREATE INDEX t_document_title_unaccent_trgm_idx ON t_document
1281 USING gin (unaccent_immutable(title) gin_trgm_ops);
1282
1283 In your queries you will need to use the same function in the search
1284 to be able to use the function-based index. Example:
1285
1286 SELECT * FROM t_document
1287 WHERE unaccent_immutable(title) LIKE '%donnees%';
1288
1289 USE_LOWER_UNACCENT
1290 Same as above but call lower() in the unaccent_immutable() function:
1291
1292 CREATE OR REPLACE FUNCTION unaccent_immutable(text)
1293 RETURNS text AS
1294 $$
1295 SELECT lower(public.unaccent('public.unaccent', $1));
1296 $$ LANGUAGE sql IMMUTABLE;
1297
1298 Modifying object structure
1299 One of the great usage of Ora2Pg is its flexibility to replicate Oracle
1300 database into PostgreSQL database with a different structure or schema.
1301 There's three configuration directives that allow you to map those
1302 differences.
1303
1304 REORDERING_COLUMNS
1305 Enable this directive to reordering columns and minimized the
1306 footprint on disc, so that more rows fit on a data page, which is
1307 the most important factor for speed. Default is disabled, that mean
1308 the same order than in Oracle tables definition, that's should be
1309 enough for most usage. This directive is only used with TABLE
1310 export.
1311
1312 MODIFY_STRUCT
1313 This directive allows you to limit the columns to extract for a
1314 given table. The value consist in a space-separated list of table
1315 name with a set of column between parenthesis as follow:
1316
1317 MODIFY_STRUCT NOM_TABLE(nomcol1,nomcol2,...) ...
1318
1319 for example:
1320
1321 MODIFY_STRUCT T_TEST1(id,dossier) T_TEST2(id,fichier)
1322
1323 This will only extract columns 'id' and 'dossier' from table T_TEST1
1324 and columns 'id' and 'fichier' from the T_TEST2 table. This
1325 directive can only be used with TABLE, COPY or INSERT export. With
1326 TABLE export create table DDL will respect the new list of columns
1327 and all indexes or foreign key pointing to or from a column removed
1328 will not be exported.
1329
1330 REPLACE_TABLES
1331 This directive allows you to remap a list of Oracle table name to a
1332 PostgreSQL table name during export. The value is a list of
1333 space-separated values with the following structure:
1334
1335 REPLACE_TABLES ORIG_TBNAME1:DEST_TBNAME1 ORIG_TBNAME2:DEST_TBNAME2
1336
1337 Oracle tables ORIG_TBNAME1 and ORIG_TBNAME2 will be respectively
1338 renamed into DEST_TBNAME1 and DEST_TBNAME2
1339
1340 REPLACE_COLS
1341 Like table name, the name of the column can be remapped to a
1342 different name using the following syntax:
1343
1344 REPLACE_COLS ORIG_TBNAME(ORIG_COLNAME1:NEW_COLNAME1,ORIG_COLNAME2:NEW_COLNAME2)
1345
1346 For example:
1347
1348 REPLACE_COLS T_TEST(dico:dictionary,dossier:folder)
1349
1350 will rename Oracle columns 'dico' and 'dossier' from table T_TEST
1351 into new name 'dictionary' and 'folder'.
1352
1353 REPLACE_AS_BOOLEAN
1354 If you want to change the type of some Oracle columns into
1355 PostgreSQL boolean during the export you can define here a list of
1356 tables and column separated by space as follow.
1357
1358 REPLACE_AS_BOOLEAN TB_NAME1:COL_NAME1 TB_NAME1:COL_NAME2 TB_NAME2:COL_NAME2
1359
1360 The values set in the boolean columns list will be replaced with the
1361 't' and 'f' following the default replacement values and those
1362 additionally set in directive BOOLEAN_VALUES.
1363
1364 Note that if you have modified the table name with REPLACE_TABLES
1365 and/or the column's name, you need to use the name of the original
1366 table and/or column.
1367
1368 REPLACE_COLS TB_NAME1(OLD_COL_NAME1:NEW_COL_NAME1)
1369 REPLACE_AS_BOOLEAN TB_NAME1:OLD_COL_NAME1
1370
1371 You can also give a type and a precision to automatically convert
1372 all fields of that type as a boolean. For example:
1373
1374 REPLACE_AS_BOOLEAN NUMBER:1 CHAR:1 TB_NAME1:COL_NAME1 TB_NAME1:COL_NAME2
1375
1376 will also replace any field of type number(1) or char(1) as a
1377 boolean in all exported tables.
1378
1379 BOOLEAN_VALUES
1380 Use this to add additional definition of the possible boolean values
1381 used in Oracle fields. You must set a space-separated list of
1382 TRUE:FALSE values. By default here are the values recognized by
1383 Ora2Pg:
1384
1385 BOOLEAN_VALUES yes:no y:n 1:0 true:false enabled:disabled
1386
1387 Any values defined here will be added to the default list.
1388
1389 REPLACE_ZERO_DATE
1390 When Ora2Pg find a "zero" date: 0000-00-00 00:00:00 it is replaced
1391 by a NULL. This could be a problem if your column is defined with
1392 NOT NULL constraint. If you can not remove the constraint, use this
1393 directive to set an arbitral date that will be used instead. You can
1394 also use -INFINITY if you don't want to use a fake date.
1395
1396 INDEXES_SUFFIX
1397 Add the given value as suffix to indexes names. Useful if you have
1398 indexes with same name as tables. For example:
1399
1400 INDEXES_SUFFIX _idx
1401
1402 will add _idx at ed of all index name. Not so common but can help.
1403
1404 INDEXES_RENAMING
1405 Enable this directive to rename all indexes using
1406 tablename_columns_names. Could be very useful for database that have
1407 multiple time the same index name or that use the same name than a
1408 table, which is not allowed by PostgreSQL Disabled by default.
1409
1410 USE_INDEX_OPCLASS
1411 Operator classes text_pattern_ops, varchar_pattern_ops, and
1412 bpchar_pattern_ops support B-tree indexes on the corresponding
1413 types. The difference from the default operator classes is that the
1414 values are compared strictly character by character rather than
1415 according to the locale-specific collation rules. This makes these
1416 operator classes suitable for use by queries involving pattern
1417 matching expressions (LIKE or POSIX regular expressions) when the
1418 database does not use the standard "C" locale. If you enable, with
1419 value 1, this will force Ora2Pg to export all indexes defined on
1420 varchar2() and char() columns using those operators. If you set it
1421 to a value greater than 1 it will only change indexes on columns
1422 where the character limit is greater or equal than this value. For
1423 example, set it to 128 to create these kind of indexes on columns of
1424 type varchar2(N) where N >= 128.
1425
1426 PREFIX_PARTITION
1427 Enable this directive if you want that your partition table name
1428 will be exported using the parent table name. Disabled by default.
1429 If you have multiple partitioned table, when exported to PostgreSQL
1430 some partitions could have the same name but different parent
1431 tables. This is not allowed, table name must be unique.
1432
1433 PREFIX_SUB_PARTITION
1434 Enable this directive if you want that your subpartition table name
1435 will be exported using the parent partition name. Enabled by
1436 default. If the partition names are a part of the subpartition
1437 names, you should enable this directive.
1438
1439 DISABLE_PARTITION
1440 If you don't want to reproduce the partitioning like in Oracle and
1441 want to export all partitioned Oracle data into the main single
1442 table in PostgreSQL enable this directive. Ora2Pg will export all
1443 data into the main table name. Default is to use partitioning,
1444 Ora2Pg will export data from each partition and import them into the
1445 PostgreSQL dedicated partition table.
1446
1447 DISABLE_UNLOGGED
1448 By default Ora2Pg export Oracle tables with the NOLOGGING attribute
1449 as UNLOGGED tables. You may want to fully disable this feature
1450 because you will lose all data from unlogged tables in case of a
1451 PostgreSQL crash. Set it to 1 to export all tables as normal tables.
1452
1453 Oracle Spatial to PostGis
1454 Ora2Pg fully export Spatial object from Oracle database. There's some
1455 configuration directives that could be used to control the export.
1456
1457 AUTODETECT_SPATIAL_TYPE
1458 By default Ora2Pg is looking at indexes to see the spatial
1459 constraint type and dimensions defined under Oracle. Those
1460 constraints are passed as at index creation using for example:
1461
1462 CREATE INDEX ... INDEXTYPE IS MDSYS.SPATIAL_INDEX
1463 PARAMETERS('sdo_indx_dims=2, layer_gtype=point');
1464
1465 If those Oracle constraints parameters are not set, the default is
1466 to export those columns as generic type GEOMETRY to be able to
1467 receive any spatial type.
1468
1469 The AUTODETECT_SPATIAL_TYPE directive allows to force Ora2Pg to
1470 autodetect the real spatial type and dimension used in a spatial
1471 column otherwise a non- constrained "geometry" type is used.
1472 Enabling this feature will force Ora2Pg to scan a sample of 50000
1473 column to look at the GTYPE used. You can increase or reduce the
1474 sample size by setting the value of AUTODETECT_SPATIAL_TYPE to the
1475 desired number of line to scan. The directive is enabled by default.
1476
1477 For example, in the case of a column named shape and defined with
1478 Oracle type SDO_GEOMETRY, with AUTODETECT_SPATIAL_TYPE disabled it
1479 will be converted as:
1480
1481 shape geometry(GEOMETRY) or shape geometry(GEOMETRYZ, 4326)
1482
1483 and if the directive is enabled and the column just contains a
1484 single geometry type that use a single dimension:
1485
1486 shape geometry(POLYGON, 4326) or shape geometry(POLYGONZ, 4326)
1487
1488 with a two or three dimensional polygon.
1489
1490 CONVERT_SRID
1491 This directive allows you to control the automatically conversion of
1492 Oracle SRID to standard EPSG. If enabled, Ora2Pg will use the Oracle
1493 function sdo_cs.map_oracle_srid_to_epsg() to convert all SRID.
1494 Enabled by default.
1495
1496 If the SDO_SRID returned by Oracle is NULL, it will be replaced by
1497 the default value 8307 converted to its EPSG value: 4326 (see
1498 DEFAULT_SRID).
1499
1500 If the value is upper than 1, all SRID will be forced to this value,
1501 in this case DEFAULT_SRID will not be used when Oracle returns a
1502 null value and the value will be forced to CONVERT_SRID.
1503
1504 Note that it is also possible to set the EPSG value on Oracle side
1505 when sdo_cs.map_oracle_srid_to_epsg() return NULL if your want to
1506 force the value:
1507
1508 system@db> UPDATE sdo_coord_ref_sys SET legacy_code=41014 WHERE srid = 27572;
1509
1510 DEFAULT_SRID
1511 Use this directive to override the default EPSG SRID to used: 4326.
1512 Can be overwritten by CONVERT_SRID, see above.
1513
1514 GEOMETRY_EXTRACT_TYPE
1515 This directive can take three values: WKT (default), WKB and
1516 INTERNAL. When it is set to WKT, Ora2Pg will use
1517 SDO_UTIL.TO_WKTGEOMETRY() to extract the geometry data. When it is
1518 set to WKB, Ora2Pg will use the binary output using
1519 SDO_UTIL.TO_WKBGEOMETRY(). If those two extract type are calls at
1520 Oracle side, they are slow and you can easily reach Out Of Memory
1521 when you have lot of rows. Also WKB is not able to export 3D
1522 geometry and some geometries like CURVEPOLYGON. In this case you may
1523 use the INTERNAL extraction type. It will use a Pure Perl library to
1524 convert the SDO_GEOMETRY data into a WKT representation, the
1525 translation is done on Ora2Pg side. This is a work in progress,
1526 please validate your exported data geometries before use. Default
1527 spatial object extraction type is INTERNAL.
1528
1529 POSTGIS_SCHEMA
1530 Use this directive to add a specific schema to the search path to
1531 look for PostGis functions.
1532
1533 PostgreSQL Import
1534 By default conversion to PostgreSQL format is written to file
1535 'output.sql'. The command:
1536
1537 psql mydb < output.sql
1538
1539 will import content of file output.sql into PostgreSQL mydb database.
1540
1541 DATA_LIMIT
1542 When you are performing INSERT/COPY export Ora2Pg proceed by chunks
1543 of DATA_LIMIT tuples for speed improvement. Tuples are stored in
1544 memory before being written to disk, so if you want speed and have
1545 enough system resources you can grow this limit to an upper value
1546 for example: 100000 or 1000000. Before release 7.0 a value of 0 mean
1547 no limit so that all tuples are stored in memory before being
1548 flushed to disk. In 7.x branch this has been remove and chunk will
1549 be set to the default: 10000
1550
1551 BLOB_LIMIT
1552 When Ora2Pg detect a table with some BLOB it will automatically
1553 reduce the value of this directive by dividing it by 10 until his
1554 value is below 1000. You can control this value by setting
1555 BLOB_LIMIT. Exporting BLOB use lot of resources, setting it to a too
1556 high value can produce OOM.
1557
1558 OUTPUT
1559 The Ora2Pg output filename can be changed with this directive.
1560 Default value is output.sql. if you set the file name with extension
1561 .gz or .bz2 the output will be automatically compressed. This
1562 require that the Compress::Zlib Perl module is installed if the
1563 filename extension is .gz and that the bzip2 system command is
1564 installed for the .bz2 extension.
1565
1566 OUTPUT_DIR
1567 Since release 7.0, you can define a base directory where the file
1568 will be written. The directory must exists.
1569
1570 BZIP2
1571 This directive allows you to specify the full path to the bzip2
1572 program if it can not be found in the PATH environment variable.
1573
1574 FILE_PER_CONSTRAINT
1575 Allow object constraints to be saved in a separate file during
1576 schema export. The file will be named CONSTRAINTS_OUTPUT, where
1577 OUTPUT is the value of the corresponding configuration directive.
1578 You can use .gz xor .bz2 extension to enable compression. Default is
1579 to save all data in the OUTPUT file. This directive is usable only
1580 with TABLE export type.
1581
1582 The constraints can be imported quickly into PostgreSQL using the
1583 LOAD export type to parallelize their creation over multiple (-j or
1584 JOBS) connections.
1585
1586 FILE_PER_INDEX
1587 Allow indexes to be saved in a separate file during schema export.
1588 The file will be named INDEXES_OUTPUT, where OUTPUT is the value of
1589 the corresponding configuration directive. You can use .gz xor .bz2
1590 file extension to enable compression. Default is to save all data in
1591 the OUTPUT file. This directive is usable only with TABLE AND
1592 TABLESPACE export type. With the TABLESPACE export, it is used to
1593 write "ALTER INDEX ... TABLESPACE ..." into a separate file named
1594 TBSP_INDEXES_OUTPUT that can be loaded at end of the migration after
1595 the indexes creation to move the indexes.
1596
1597 The indexes can be imported quickly into PostgreSQL using the LOAD
1598 export type to parallelize their creation over multiple (-j or JOBS)
1599 connections.
1600
1601 FILE_PER_FKEYS
1602 Allow foreign key declaration to be saved in a separate file during
1603 schema export. By default foreign keys are exported into the main
1604 output file or in the CONSTRAINT_output.sql file. When enabled
1605 foreign keys will be exported into a file named FKEYS_output.sql
1606
1607 FILE_PER_TABLE
1608 Allow data export to be saved in one file per table/view. The files
1609 will be named as tablename_OUTPUT, where OUTPUT is the value of the
1610 corresponding configuration directive. You can still use .gz xor
1611 .bz2 extension in the OUTPUT directive to enable compression.
1612 Default 0 will save all data in one file, set it to 1 to enable this
1613 feature. This is usable only during INSERT or COPY export type.
1614
1615 FILE_PER_FUNCTION
1616 Allow functions, procedures and triggers to be saved in one file per
1617 object. The files will be named as objectname_OUTPUT. Where OUTPUT
1618 is the value of the corresponding configuration directive. You can
1619 still use .gz xor .bz2 extension in the OUTPUT directive to enable
1620 compression. Default 0 will save all in one single file, set it to 1
1621 to enable this feature. This is usable only during the corresponding
1622 export type, the package body export has a special behavior.
1623
1624 When export type is PACKAGE and you've enabled this directive,
1625 Ora2Pg will create a directory per package, named with the lower
1626 case name of the package, and will create one file per
1627 function/procedure into that directory. If the configuration
1628 directive is not enabled, it will create one file per package as
1629 packagename_OUTPUT, where OUTPUT is the value of the corresponding
1630 directive.
1631
1632 TRUNCATE_TABLE
1633 If this directive is set to 1, a TRUNCATE TABLE instruction will be
1634 add before loading data. This is usable only during INSERT or COPY
1635 export type.
1636
1637 When activated, the instruction will be added only if there's no
1638 global DELETE clause or not one specific to the current table (see
1639 below).
1640
1641 DELETE
1642 Support for include a DELETE FROM ... WHERE clause filter before
1643 importing data and perform a delete of some lines instead of
1644 truncating tables. Value is construct as follow:
1645 TABLE_NAME[DELETE_WHERE_CLAUSE], or if you have only one where
1646 clause for all tables just put the delete clause as single value.
1647 Both are possible too. Here are some examples:
1648
1649 DELETE 1=1 # Apply to all tables and delete all tuples
1650 DELETE TABLE_TEST[ID1='001'] # Apply only on table TABLE_TEST
1651 DELETE TABLE_TEST[ID1='001' OR ID1='002] DATE_CREATE > '2001-01-01' TABLE_INFO[NAME='test']
1652
1653 The last applies two different delete where clause on tables
1654 TABLE_TEST and TABLE_INFO and a generic delete where clause on
1655 DATE_CREATE to all other tables. If TRUNCATE_TABLE is enabled it
1656 will be applied to all tables not covered by the DELETE definition.
1657
1658 These DELETE clauses might be useful with regular "updates".
1659
1660 STOP_ON_ERROR
1661 Set this parameter to 0 to not include the call to \set
1662 ON_ERROR_STOP ON in all SQL scripts generated by Ora2Pg. By default
1663 this order is always present so that the script will immediately
1664 abort when an error is encountered.
1665
1666 COPY_FREEZE
1667 Enable this directive to use COPY FREEZE instead of a simple COPY to
1668 export data with rows already frozen. This is intended as a
1669 performance option for initial data loading. Rows will be frozen
1670 only if the table being loaded has been created or truncated in the
1671 current sub-transaction. This will only work with export to file and
1672 when -J or ORACLE_COPIES is not set or default to 1. It can be used
1673 with direct import into PostgreSQL under the same condition but -j
1674 or JOBS must also be unset or default to 1.
1675
1676 CREATE_OR_REPLACE
1677 By default Ora2Pg uses CREATE OR REPLACE in functions and views DDL,
1678 if you need not to override existing functions or views disable this
1679 configuration directive, DDL will not include OR REPLACE.
1680
1681 DROP_IF_EXISTS
1682 To add a DROP <OBJECT> IF EXISTS before creating the object, enable
1683 this directive. Can be useful in an iterative work. Default is
1684 disabled.
1685
1686 NO_HEADER
1687 Enabling this directive will prevent Ora2Pg to print his header into
1688 output files. Only the translated code will be written.
1689
1690 PSQL_RELATIVE_PATH
1691 By default Ora2Pg use \i psql command to execute generated SQL files
1692 if you want to use a relative path following the script execution
1693 file enabling this option will use \ir. See psql help for more
1694 information.
1695
1696 When using Ora2Pg export type INSERT or COPY to dump data to file and
1697 that FILE_PER_TABLE is enabled, you will be warned that Ora2Pg will not
1698 export data again if the file already exists. This is to prevent
1699 downloading twice table with huge amount of data. To force the download
1700 of data from these tables you have to remove the existing output file
1701 first.
1702
1703 If you want to import data on the fly to the PostgreSQL database you
1704 have three configuration directives to set the PostgreSQL database
1705 connection. This is only possible with COPY or INSERT export type as for
1706 database schema there's no real interest to do that.
1707
1708 PG_DSN
1709 Use this directive to set the PostgreSQL data source namespace using
1710 DBD::Pg Perl module as follow:
1711
1712 dbi:Pg:dbname=pgdb;host=localhost;port=5432
1713
1714 will connect to database 'pgdb' on localhost at tcp port 5432.
1715
1716 Note that this directive is only used for data export, other export
1717 need to be imported manually through the use og psql or any other
1718 PostgreSQL client.
1719
1720 PG_USER and PG_PWD
1721 These two directives are used to set the login user and password.
1722
1723 If you do not supply a credential with PG_PWD and you have installed
1724 the Term::ReadKey Perl module, Ora2Pg will ask for the password
1725 interactively. If PG_USER is not set it will be asked interactively
1726 too.
1727
1728 SYNCHRONOUS_COMMIT
1729 Specifies whether transaction commit will wait for WAL records to be
1730 written to disk before the command returns a "success" indication to
1731 the client. This is the equivalent to set synchronous_commit
1732 directive of postgresql.conf file. This is only used when you load
1733 data directly to PostgreSQL, the default is off to disable
1734 synchronous commit to gain speed at writing data. Some modified
1735 version of PostgreSQL, like greenplum, do not have this setting, so
1736 in this set this directive to 1, ora2pg will not try to change the
1737 setting.
1738
1739 PG_INITIAL_COMMAND
1740 This directive can be used to send an initial command to PostgreSQL,
1741 just after the connection. For example to set some session
1742 parameters. This directive can be used multiple times.
1743
1744 Column type control
1745 PG_NUMERIC_TYPE
1746 If set to 1 replace portable numeric type into PostgreSQL internal
1747 type. Oracle data type NUMBER(p,s) is approximatively converted to
1748 real and float PostgreSQL data type. If you have monetary fields or
1749 don't want rounding issues with the extra decimals you should
1750 preserve the same numeric(p,s) PostgreSQL data type. Do that only if
1751 you need exactness because using numeric(p,s) is slower than using
1752 real or double.
1753
1754 PG_INTEGER_TYPE
1755 If set to 1 replace portable numeric type into PostgreSQL internal
1756 type. Oracle data type NUMBER(p) or NUMBER are converted to
1757 smallint, integer or bigint PostgreSQL data type following the value
1758 of the precision. If NUMBER without precision are set to
1759 DEFAULT_NUMERIC (see below).
1760
1761 DEFAULT_NUMERIC
1762 NUMBER without precision are converted by default to bigint only if
1763 PG_INTEGER_TYPE is true. You can overwrite this value to any PG
1764 type, like integer or float.
1765
1766 DATA_TYPE
1767 If you're experiencing any problem in data type schema conversion
1768 with this directive you can take full control of the correspondence
1769 between Oracle and PostgreSQL types to redefine data type
1770 translation used in Ora2pg. The syntax is a comma-separated list of
1771 "Oracle datatype:Postgresql datatype". Here are the default list
1772 used:
1773
1774 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
1775
1776 Note that the directive and the list definition must be a single
1777 line.
1778
1779 If you want to replace a type with a precision and scale you need to
1780 escape the coma with a backslash. For example, if you want to
1781 replace all NUMBER(*,0) into bigint instead of numeric(38) add the
1782 following:
1783
1784 DATA_TYPE NUMBER(*\,0):bigint
1785
1786 You don't have to recopy all default type conversion but just the
1787 one you want to rewrite.
1788
1789 There's a special case with BFILE when they are converted to type
1790 TEXT, they will just contains the full path to the external file. If
1791 you set the destination type to BYTEA, the default, Ora2Pg will
1792 export the content of the BFILE as bytea. The third case is when you
1793 set the destination type to EFILE, in this case, Ora2Pg will export
1794 it as an EFILE record: (DIRECTORY, FILENAME). Use the DIRECTORY
1795 export type to export the existing directories as well as privileges
1796 on those directories.
1797
1798 There's no SQL function available to retrieve the path to the BFILE.
1799 Ora2Pg have to create one using the DBMS_LOB package.
1800
1801 CREATE OR REPLACE FUNCTION ora2pg_get_bfilename( p_bfile IN BFILE )
1802 RETURN VARCHAR2
1803 AS
1804 l_dir VARCHAR2(4000);
1805 l_fname VARCHAR2(4000);
1806 l_path VARCHAR2(4000);
1807 BEGIN
1808 dbms_lob.FILEGETNAME( p_bfile, l_dir, l_fname );
1809 SELECT directory_path INTO l_path FROM all_directories
1810 WHERE directory_name = l_dir;
1811 l_dir := rtrim(l_path,'/');
1812 RETURN l_dir || '/' || l_fname;
1813 END;
1814
1815 This function is only created if Ora2Pg found a table with a BFILE
1816 column and that the destination type is TEXT. The function is
1817 dropped at the end of the export. This concern both, COPY and INSERT
1818 export type.
1819
1820 There's no SQL function available to retrieve BFILE as an EFILE
1821 record, then Ora2Pg have to create one using the DBMS_LOB package.
1822
1823 CREATE OR REPLACE FUNCTION ora2pg_get_efile( p_bfile IN BFILE )
1824 RETURN VARCHAR2
1825 AS
1826 l_dir VARCHAR2(4000);
1827 l_fname VARCHAR2(4000);
1828 BEGIN
1829 dbms_lob.FILEGETNAME( p_bfile, l_dir, l_fname );
1830 RETURN '(' || l_dir || ',' || l_fnamei || ')';
1831 END;
1832
1833 This function is only created if Ora2Pg found a table with a BFILE
1834 column and that the destination type is EFILE. The function is
1835 dropped at the end of the export. This concern both, COPY and INSERT
1836 export type.
1837
1838 To set the destination type, use the DATA_TYPE configuration
1839 directive:
1840
1841 DATA_TYPE BFILE:EFILE
1842
1843 for example.
1844
1845 The EFILE type is a user defined type created by the PostgreSQL
1846 extension external_file that can be found here:
1847 https://github.com/darold/external_file This is a port of the BFILE
1848 Oracle type to PostgreSQL.
1849
1850 There's no SQL function available to retrieve the content of a
1851 BFILE. Ora2Pg have to create one using the DBMS_LOB package.
1852
1853 CREATE OR REPLACE FUNCTION ora2pg_get_bfile( p_bfile IN BFILE ) RETURN
1854 BLOB
1855 AS
1856 filecontent BLOB := NULL;
1857 src_file BFILE := NULL;
1858 l_step PLS_INTEGER := 12000;
1859 l_dir VARCHAR2(4000);
1860 l_fname VARCHAR2(4000);
1861 offset NUMBER := 1;
1862 BEGIN
1863 IF p_bfile IS NULL THEN
1864 RETURN NULL;
1865 END IF;
1866
1867 DBMS_LOB.FILEGETNAME( p_bfile, l_dir, l_fname );
1868 src_file := BFILENAME( l_dir, l_fname );
1869 IF src_file IS NULL THEN
1870 RETURN NULL;
1871 END IF;
1872
1873 DBMS_LOB.FILEOPEN(src_file, DBMS_LOB.FILE_READONLY);
1874 DBMS_LOB.CREATETEMPORARY(filecontent, true);
1875 DBMS_LOB.LOADBLOBFROMFILE (filecontent, src_file, DBMS_LOB.LOBMAXSIZE, offset, offset);
1876 DBMS_LOB.FILECLOSE(src_file);
1877 RETURN filecontent;
1878 END;
1879
1880 This function is only created if Ora2Pg found a table with a BFILE
1881 column and that the destination type is bytea (the default). The
1882 function is dropped at the end of the export. This concern both,
1883 COPY and INSERT export type.
1884
1885 About the ROWID and UROWID, they are converted into OID by "logical"
1886 default but this will through an error at data import. There is no
1887 equivalent data type so you might want to use the DATA_TYPE
1888 directive to change the corresponding type in PostgreSQL. You should
1889 consider replacing this data type by a bigserial (autoincremented
1890 sequence), text or uuid data type.
1891
1892 MODIFY_TYPE
1893 Sometimes you need to force the destination type, for example a
1894 column exported as timestamp by Ora2Pg can be forced into type date.
1895 Value is a comma-separated list of TABLE:COLUMN:TYPE structure. If
1896 you need to use comma or space inside type definition you will have
1897 to backslash them.
1898
1899 MODIFY_TYPE TABLE1:COL3:varchar,TABLE1:COL4:decimal(9\,6)
1900
1901 Type of table1.col3 will be replaced by a varchar and table1.col4 by
1902 a decimal with precision and scale.
1903
1904 If the column's type is a user defined type Ora2Pg will autodetect
1905 the composite type and will export its data using ROW(). Some Oracle
1906 user defined types are just array of a native type, in this case you
1907 may want to transform this column in simple array of a PostgreSQL
1908 native type. To do so, just redefine the destination type as wanted
1909 and Ora2Pg will also transform the data as an array. For example,
1910 with the following definition in Oracle:
1911
1912 CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15);
1913 CREATE TABLE club (Name VARCHAR2(10),
1914 Address VARCHAR2(20),
1915 City VARCHAR2(20),
1916 Phone VARCHAR2(8),
1917 Members mem_type
1918 );
1919
1920 custom type "mem_type" is just a string array and can be translated
1921 into the following in PostgreSQL:
1922
1923 CREATE TABLE club (
1924 name varchar(10),
1925 address varchar(20),
1926 city varchar(20),
1927 phone varchar(8),
1928 members text[]
1929 ) ;
1930
1931 To do so, just use the directive as follow:
1932
1933 MODIFY_TYPE CLUB:MEMBERS:text[]
1934
1935 Ora2Pg will take care to transform all data of this column in the
1936 correct format. Only arrays of characters and numerics types are
1937 supported.
1938
1939 Taking export under control
1940 The following other configuration directives interact directly with the
1941 export process and give you fine granularity in database export control.
1942
1943 SKIP
1944 For TABLE export you may not want to export all schema constraints,
1945 the SKIP configuration directive allows you to specify a
1946 space-separated list of constraints that should not be exported.
1947 Possible values are:
1948
1949 - fkeys: turn off foreign key constraints
1950 - pkeys: turn off primary keys
1951 - ukeys: turn off unique column constraints
1952 - indexes: turn off all other index types
1953 - checks: turn off check constraints
1954
1955 For example:
1956
1957 SKIP indexes,checks
1958
1959 will removed indexes and check constraints from export.
1960
1961 PKEY_IN_CREATE
1962 Enable this directive if you want to add primary key definition
1963 inside the create table statement. If disabled (the default) primary
1964 key definition will be added with an alter table statement. Enable
1965 it if you are exporting to GreenPlum PostgreSQL database.
1966
1967 KEEP_PKEY_NAMES
1968 By default names of the primary and unique key in the source Oracle
1969 database are ignored and key names are autogenerated in the target
1970 PostgreSQL database with the PostgreSQL internal default naming
1971 rules. If you want to preserve Oracle primary and unique key names
1972 set this option to 1.
1973
1974 FKEY_ADD_UPDATE
1975 This directive allows you to add an ON UPDATE CASCADE option to a
1976 foreign key when a ON DELETE CASCADE is defined or always. Oracle do
1977 not support this feature, you have to use trigger to operate the ON
1978 UPDATE CASCADE. As PostgreSQL has this feature, you can choose how
1979 to add the foreign key option. There are three values to this
1980 directive: never, the default that mean that foreign keys will be
1981 declared exactly like in Oracle. The second value is delete, that
1982 mean that the ON UPDATE CASCADE option will be added only if the ON
1983 DELETE CASCADE is already defined on the foreign Keys. The last
1984 value, always, will force all foreign keys to be defined using the
1985 update option.
1986
1987 FKEY_DEFERRABLE
1988 When exporting tables, Ora2Pg normally exports constraints as they
1989 are, if they are non-deferrable they are exported as non-deferrable.
1990 However, non-deferrable constraints will probably cause problems
1991 when attempting to import data to Pg. The FKEY_DEFERRABLE option set
1992 to 1 will cause all foreign key constraints to be exported as
1993 deferrable.
1994
1995 DEFER_FKEY
1996 In addition to exporting data when the DEFER_FKEY option set to 1,
1997 it will add a command to defer all foreign key constraints during
1998 data export and the import will be done in a single transaction.
1999 This will work only if foreign keys have been exported as deferrable
2000 and you are not using direct import to PostgreSQL (PG_DSN is not
2001 defined). Constraints will then be checked at the end of the
2002 transaction.
2003
2004 This directive can also be enabled if you want to force all foreign
2005 keys to be created as deferrable and initially deferred during
2006 schema export (TABLE export type).
2007
2008 DROP_FKEY
2009 If deferring foreign keys is not possible due to the amount of data
2010 in a single transaction, you've not exported foreign keys as
2011 deferrable or you are using direct import to PostgreSQL, you can use
2012 the DROP_FKEY directive.
2013
2014 It will drop all foreign keys before all data import and recreate
2015 them at the end of the import.
2016
2017 DROP_INDEXES
2018 This directive allows you to gain lot of speed improvement during
2019 data import by removing all indexes that are not an automatic index
2020 (indexes of primary keys) and recreate them at the end of data
2021 import. Of course it is far better to not import indexes and
2022 constraints before having imported all data.
2023
2024 DISABLE_TRIGGERS
2025 This directive is used to disable triggers on all tables in COPY or
2026 INSERT export modes. Available values are USER (disable user-defined
2027 triggers only) and ALL (includes RI system triggers). Default is 0:
2028 do not add SQL statements to disable trigger before data import.
2029
2030 If you want to disable triggers during data migration, set the value
2031 to USER if your are connected as non superuser and ALL if you are
2032 connected as PostgreSQL superuser. A value of 1 is equal to USER.
2033
2034 DISABLE_SEQUENCE
2035 If set to 1 it disables alter of sequences on all tables during COPY
2036 or INSERT export mode. This is used to prevent the update of
2037 sequence during data migration. Default is 0, alter sequences.
2038
2039 NOESCAPE
2040 By default all data that are not of type date or time are escaped.
2041 If you experience any problem with that you can set it to 1 to
2042 disable character escaping during data export. This directive is
2043 only used during a COPY export. See STANDARD_CONFORMING_STRINGS for
2044 enabling/disabling escape with INSERT statements.
2045
2046 STANDARD_CONFORMING_STRINGS
2047 This controls whether ordinary string literals ('...') treat
2048 backslashes literally, as specified in SQL standard. This was the
2049 default before Ora2Pg v8.5 so that all strings was escaped first,
2050 now this is currently on, causing Ora2Pg to use the escape string
2051 syntax (E'...') if this parameter is not set to 0. This is the exact
2052 behavior of the same option in PostgreSQL. This directive is only
2053 used during data export to build INSERT statements. See NOESCAPE for
2054 enabling/disabling escape in COPY statements.
2055
2056 TRIM_TYPE
2057 If you want to convert CHAR(n) from Oracle into varchar(n) or text
2058 on PostgreSQL using directive DATA_TYPE, you might want to do some
2059 trimming on the data. By default Ora2Pg will auto-detect this
2060 conversion and remove any whitespace at both leading and trailing
2061 position. If you just want to remove the leadings character set the
2062 value to LEADING. If you just want to remove the trailing character,
2063 set the value to TRAILING. Default value is BOTH.
2064
2065 TRIM_CHAR
2066 The default trimming character is space, use this directive if you
2067 need to change the character that will be removed. For example, set
2068 it to - if you have leading - in the char(n) field. To use space as
2069 trimming charger, comment this directive, this is the default value.
2070
2071 PRESERVE_CASE
2072 If you want to preserve the case of Oracle object name set this
2073 directive to 1. By default Ora2Pg will convert all Oracle object
2074 names to lower case. I do not recommend to enable this unless you
2075 will always have to double-quote object names on all your SQL
2076 scripts.
2077
2078 ORA_RESERVED_WORDS
2079 Allow escaping of column name using Oracle reserved words. Value is
2080 a list of comma-separated reserved word. Default:
2081 audit,comment,references.
2082
2083 USE_RESERVED_WORDS
2084 Enable this directive if you have table or column names that are a
2085 reserved word for PostgreSQL. Ora2Pg will double quote the name of
2086 the object.
2087
2088 GEN_USER_PWD
2089 Set this directive to 1 to replace default password by a random
2090 password for all extracted user during a GRANT export.
2091
2092 PG_SUPPORTS_MVIEW
2093 Since PostgreSQL 9.3, materialized view are supported with the SQL
2094 syntax 'CREATE MATERIALIZED VIEW'. To force Ora2Pg to use the native
2095 PostgreSQL support you must enable this configuration - enable by
2096 default. If you want to use the old style with table and a set of
2097 function, you should disable it.
2098
2099 PG_SUPPORTS_IFEXISTS
2100 PostgreSQL version below 9.x do not support IF EXISTS in DDL
2101 statements. Disabling the directive with value 0 will prevent Ora2Pg
2102 to add those keywords in all generated statements. Default value is
2103 1, enabled.
2104
2105 PG_SUPPORTS_ROLE (Deprecated)
2106 This option is deprecated since Ora2Pg release v7.3.
2107
2108 By default Oracle roles are translated into PostgreSQL groups. If
2109 you have PostgreSQL 8.1 or more consider the use of ROLES and set
2110 this directive to 1 to export roles.
2111
2112 PG_SUPPORTS_INOUT (Deprecated)
2113 This option is deprecated since Ora2Pg release v7.3.
2114
2115 If set to 0, all IN, OUT or INOUT parameters will not be used into
2116 the generated PostgreSQL function declarations (disable it for
2117 PostgreSQL database version lower than 8.1), This is now enable by
2118 default.
2119
2120 PG_SUPPORTS_DEFAULT
2121 This directive enable or disable the use of default parameter value
2122 in function export. Until PostgreSQL 8.4 such a default value was
2123 not supported, this feature is now enable by default.
2124
2125 PG_SUPPORTS_WHEN (Deprecated)
2126 Add support to WHEN clause on triggers as PostgreSQL v9.0 now
2127 support it. This directive is enabled by default, set it to 0
2128 disable this feature.
2129
2130 PG_SUPPORTS_INSTEADOF (Deprecated)
2131 Add support to INSTEAD OF usage on triggers (used with PG >= 9.1),
2132 if this directive is disabled the INSTEAD OF triggers will be
2133 rewritten as Pg rules.
2134
2135 PG_SUPPORTS_CHECKOPTION
2136 When enabled, export views with CHECK OPTION. Disable it if you have
2137 PostgreSQL version prior to 9.4. Default: 1, enabled.
2138
2139 PG_SUPPORTS_IFEXISTS
2140 If disabled, do not export object with IF EXISTS statements. Enabled
2141 by default.
2142
2143 PG_SUPPORTS_PARTITION
2144 PostgreSQL version prior to 10.0 do not have native partitioning.
2145 Enable this directive if you want to use declarative partitioning.
2146 Enable by default.
2147
2148 PG_SUPPORTS_SUBSTR
2149 Some versions of PostgreSQL like Redshift doesn't support substr()
2150 and it need to be replaced by a call to substring(). In this case,
2151 disable it.
2152
2153 PG_SUPPORTS_NAMED_OPERATOR
2154 Disable this directive if you are using PG < 9.5, PL/SQL operator
2155 used in named parameter => will be replaced by PostgreSQL
2156 proprietary operator := Enable by default.
2157
2158 PG_SUPPORTS_IDENTITY
2159 Enable this directive if you have PostgreSQL >= 10 to use IDENTITY
2160 columns instead of serial or bigserial data type. If
2161 PG_SUPPORTS_IDENTITY is disabled and there is IDENTITY column in the
2162 Oracle table, they are exported as serial or bigserial columns. When
2163 it is enabled they are exported as IDENTITY columns like:
2164
2165 CREATE TABLE identity_test_tab (
2166 id bigint GENERATED ALWAYS AS IDENTITY,
2167 description varchar(30)
2168 ) ;
2169
2170 If there is non default sequence options set in Oracle, they will be
2171 appended after the IDENTITY keyword. Additionally in both cases,
2172 Ora2Pg will create a file AUTOINCREMENT_output.sql with a embedded
2173 function to update the associated sequences with the restart value
2174 set to "SELECT max(colname)+1 FROM tablename". Of course this file
2175 must be imported after data import otherwise sequence will be kept
2176 to start value. Enabled by default.
2177
2178 PG_SUPPORTS_PROCEDURE
2179 PostgreSQL v11 adds support of PROCEDURE, enable it if you use such
2180 version.
2181
2182 BITMAP_AS_GIN
2183 Use btree_gin extension to create bitmap like index with pg >= 9.4
2184 You will need to create the extension by yourself: create extension
2185 btree_gin; Default is to create GIN index, when disabled, a btree
2186 index will be created
2187
2188 PG_BACKGROUND
2189 Use pg_background extension to create an autonomous transaction
2190 instead of using a dblink wrapper. With pg >= 9.5 only. Default is
2191 to use dblink. See https://github.com/vibhorkum/pg_background about
2192 this extension.
2193
2194 DBLINK_CONN
2195 By default if you have an autonomous transaction translated using
2196 dblink extension instead of pg_background the connection is defined
2197 using the values set with PG_DSN, PG_USER and PG_PWD. If you want to
2198 fully override the connection string use this directive as follow to
2199 set the connection in the autonomous transaction wrapper function.
2200 For example:
2201
2202 DBLINK_CONN port=5432 dbname=pgdb host=localhost user=pguser password=pgpass
2203
2204 LONGREADLEN
2205 Use this directive to set the database handle's 'LongReadLen'
2206 attribute to a value that will be the larger than the expected size
2207 of the LOBs. The default is 1MB witch may not be enough to extract
2208 BLOBs or CLOBs. If the size of the LOB exceeds the 'LongReadLen'
2209 DBD::Oracle will return a 'ORA-24345: A Truncation' error. Default:
2210 1023*1024 bytes.
2211
2212 Take a look at this page to learn more:
2213 http://search.cpan.org/~pythian/DBD-Oracle-1.22/Oracle.pm#Data_Inter
2214 face_for_Persistent_LOBs
2215
2216 Important note: If you increase the value of this directive take
2217 care that DATA_LIMIT will probably needs to be reduced. Even if you
2218 only have a 1MB blob, trying to read 10000 of them (the default
2219 DATA_LIMIT) all at once will require 10GB of memory. You may extract
2220 data from those table separately and set a DATA_LIMIT to 500 or
2221 lower, otherwise you may experience some out of memory.
2222
2223 LONGTRUNKOK
2224 If you want to bypass the 'ORA-24345: A Truncation' error, set this
2225 directive to 1, it will truncate the data extracted to the
2226 LongReadLen value. Disable by default so that you will be warned if
2227 your LongReadLen value is not high enough.
2228
2229 USE_LOB_LOCATOR
2230 Disable this if you want to load full content of BLOB and CLOB and
2231 not use LOB locators. In this case you will have to set LONGREADLEN
2232 to the right value. Note that this will not improve speed of BLOB
2233 export as most of the time is always consumed by the bytea escaping
2234 and in this case export is done line by line and not by chunk of
2235 DATA_LIMIT rows. For more information on how it works, see
2236 http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm#Da
2237 ta_Interface_for_LOB_Locators
2238
2239 Default is enabled, it use LOB locators.
2240
2241 LOB_CHUNK_SIZE
2242 Oracle recommends reading from and writing to a LOB in batches using
2243 a multiple of the LOB chunk size. This chunk size defaults to 8k
2244 (8192). Recent tests shown that the best performances can be reach
2245 with higher value like 512K or 4Mb.
2246
2247 A quick benchmark with 30120 rows with different size of BLOB
2248 (200x5Mb, 19800x212k, 10000x942K, 100x17Mb, 20x156Mb), with
2249 DATA_LIMIT=100, LONGREADLEN=170Mb and a total table size of 20GB
2250 gives:
2251
2252 no lob locator : 22m46,218s (1365 sec., avg: 22 recs/sec)
2253 chunk size 8k : 15m50,886s (951 sec., avg: 31 recs/sec)
2254 chunk size 512k : 1m28,161s (88 sec., avg: 342 recs/sec)
2255 chunk size 4Mb : 1m23,717s (83 sec., avg: 362 recs/sec)
2256
2257 In conclusion it can be more than 10 time faster with LOB_CHUNK_SIZE
2258 set to 4Mb. Depending of the size of most BLOB you may want to
2259 adjust the value here. For example if you have a majority of small
2260 lobs bellow 8K, using 8192 is better to not waste space. Default
2261 value for LOB_CHUNK_SIZE is 512000.
2262
2263 XML_PRETTY
2264 Force the use getStringVal() instead of getClobVal() for XML data
2265 export. Default is 1, enabled for backward compatibility. Set it to
2266 0 to use extract method a la CLOB. Note that XML value extracted
2267 with getStringVal() must not exceed VARCHAR2 size limit (4000)
2268 otherwise it will return an error.
2269
2270 ENABLE_MICROSECOND
2271 Set it to O if you want to disable export of millisecond from Oracle
2272 timestamp columns. By default milliseconds are exported with the use
2273 of following format:
2274
2275 'YYYY-MM-DD HH24:MI:SS.FF'
2276
2277 Disabling will force the use of the following Oracle format:
2278
2279 to_char(..., 'YYYY-MM-DD HH24:MI:SS')
2280
2281 By default milliseconds are exported.
2282
2283 DISABLE_COMMENT
2284 Set this to 1 if you don't want to export comment associated to
2285 tables and columns definition. Default is enabled.
2286
2287 Control MySQL export behavior
2288 MYSQL_PIPES_AS_CONCAT
2289 Enable this if double pipe and double ampersand (|| and &&) should
2290 not be taken as equivalent to OR and AND. It depend of the variable
2291 @sql_mode, Use it only if Ora2Pg fail on auto detecting this
2292 behavior.
2293
2294 MYSQL_INTERNAL_EXTRACT_FORMAT
2295 Enable this directive if you want EXTRACT() replacement to use the
2296 internal format returned as an integer, for example DD HH24:MM:SS
2297 will be replaced with format; DDHH24MMSS::bigint, this depend of
2298 your apps usage.
2299
2300 Special options to handle character encoding
2301 NLS_LANG and NLS_NCHAR
2302 By default Ora2Pg will set NLS_LANG to AMERICAN_AMERICA.AL32UTF8 and
2303 NLS_NCHAR to AL32UTF8. It is not recommended to change those
2304 settings but in some case it could be useful. Using your own
2305 settings with those configuration directive will change the client
2306 encoding at Oracle side by setting the environment variables
2307 $ENV{NLS_LANG} and $ENV{NLS_NCHAR}.
2308
2309 BINMODE
2310 By default Ora2Pg will force Perl to use utf8 I/O encoding. This is
2311 done through a call to the Perl pragma:
2312
2313 use open ':utf8';
2314
2315 You can override this encoding by using the BINMODE directive, for
2316 example you can set it to :locale to use your locale or iso-8859-7,
2317 it will respectively use
2318
2319 use open ':locale';
2320 use open ':encoding(iso-8859-7)';
2321
2322 If you have change the NLS_LANG in non UTF8 encoding, you might want
2323 to set this directive. See http://perldoc.perl.org/5.14.2/open.html
2324 for more information. Most of the time, leave this directive
2325 commented.
2326
2327 CLIENT_ENCODING
2328 By default PostgreSQL client encoding is automatically set to UTF8
2329 to avoid encoding issue. If you have changed the value of NLS_LANG
2330 you might have to change the encoding of the PostgreSQL client.
2331
2332 You can take a look at the PostgreSQL supported character sets here:
2333 http://www.postgresql.org/docs/9.0/static/multibyte.html
2334
2335 PLSQL to PLPGSQL conversion
2336 Automatic code conversion from Oracle PLSQL to PostgreSQL PLPGSQL is a
2337 work in progress in Ora2Pg and surely you will always have manual work.
2338 The Perl code used for automatic conversion is all stored in a specific
2339 Perl Module named Ora2Pg/PLSQL.pm feel free to modify/add you own code
2340 and send me patches. The main work in on function, procedure, package
2341 and package body headers and parameters rewrite.
2342
2343 PLSQL_PGSQL
2344 Enable/disable PLSQL to PLPGSQL conversion. Enabled by default.
2345
2346 NULL_EQUAL_EMPTY
2347 Ora2Pg can replace all conditions with a test on NULL by a call to
2348 the coalesce() function to mimic the Oracle behavior where empty
2349 string are considered equal to NULL.
2350
2351 (field1 IS NULL) is replaced by (coalesce(field1::text, '') = '')
2352 (field2 IS NOT NULL) is replaced by (field2 IS NOT NULL AND field2::text <> '')
2353
2354 You might want this replacement to be sure that your application
2355 will have the same behavior but if you have control on you
2356 application a better way is to change it to transform empty string
2357 into NULL because PostgreSQL makes the difference.
2358
2359 EMPTY_LOB_NULL
2360 Force empty_clob() and empty_blob() to be exported as NULL instead
2361 as empty string for the first one and '\x' for the second. If NULL
2362 is allowed in your column this might improve data export speed if
2363 you have lot of empty lob. Default is to preserve the exact data
2364 from Oracle.
2365
2366 PACKAGE_AS_SCHEMA
2367 If you don't want to export package as schema but as simple
2368 functions you might also want to replace all call to
2369 package_name.function_name. If you disable the PACKAGE_AS_SCHEMA
2370 directive then Ora2Pg will replace all call to
2371 package_name.function_name() by package_name_function_name().
2372 Default is to use a schema to emulate package.
2373
2374 The replacement will be done in all kind of DDL or code that is
2375 parsed by the PLSQL to PLPGSQL converter. PLSQL_PGSQL must be
2376 enabled or -p used in command line.
2377
2378 REWRITE_OUTER_JOIN
2379 Enable this directive if the rewrite of Oracle native syntax (+) of
2380 OUTER JOIN is broken. This will force Ora2Pg to not rewrite such
2381 code, default is to try to rewrite simple form of right outer join
2382 for the moment.
2383
2384 UUID_FUNCTION
2385 By default Ora2Pg will convert call to SYS_GUID() Oracle function
2386 with a call to uuid_generate_v4 from uuid-ossp extension. You can
2387 redefined it to use the gen_random_uuid function from pgcrypto
2388 extension by changing the function name. Default to
2389 uuid_generate_v4.
2390
2391 Note that when a RAW(n) column has "SYS_GUID()" as default value
2392 Ora2Pg will automatically translate the type of the column into uuid
2393 which might be the right translation in most of the case.
2394
2395 FUNCTION_STABLE
2396 By default Oracle functions are marked as STABLE as they can not
2397 modify data unless when used in PL/SQL with variable assignment or
2398 as conditional expression. You can force Ora2Pg to create these
2399 function as VOLATILE by disabling this configuration directive.
2400
2401 COMMENT_COMMIT_ROLLBACK
2402 By default call to COMMIT/ROLLBACK are kept untouched by Ora2Pg to
2403 force the user to review the logic of the function. Once it is fixed
2404 in Oracle source code or you want to comment this calls enable the
2405 following directive.
2406
2407 COMMENT_SAVEPOINT
2408 It is common to see SAVEPOINT call inside PL/SQL procedure together
2409 with a ROLLBACK TO savepoint_name. When COMMENT_COMMIT_ROLLBACK is
2410 enabled you may want to also comment SAVEPOINT calls, in this case
2411 enable it.
2412
2413 STRING_CONSTANT_REGEXP
2414 Ora2Pg replace all string constant during the pl/sql to plpgsql
2415 translation, string constant are all text include between single
2416 quote. If you have some string placeholder used in dynamic call to
2417 queries you can set a list of regexp to be temporary replaced to not
2418 break the parser. For example:
2419
2420 STRING_CONSTANT_REGEXP <placeholder value=".*">
2421
2422 The list of regexp must use the semi colon as separator.
2423
2424 ALTERNATIVE_QUOTING_REGEXP
2425 To support the Alternative Quoting Mechanism ('Q' or 'q') for String
2426 Literals set the regexp with the text capture to use to extract the
2427 text part. For example with a variable declared as
2428
2429 c_sample VARCHAR2(100 CHAR) := q'{This doesn't work.}';
2430
2431 the regexp to use must be:
2432
2433 ALTERNATIVE_QUOTING_REGEXP q'{(.*)}'
2434
2435 ora2pg will use the $$ delimiter, with the example the result will
2436 be:
2437
2438 c_sample varchar(100) := $$This doesn't work.$$;
2439
2440 The value of this configuration directive can be a list of regexp
2441 separated by a semi colon. The capture part (between parenthesis) is
2442 mandatory in each regexp if you want to restore the string constant.
2443
2444 USE_ORAFCE
2445 If you want to use functions defined in the Orafce library and
2446 prevent Ora2Pg to translate call to these functions, enable this
2447 directive. The Orafce library can be found here:
2448 https://github.com/orafce/orafce
2449
2450 By default Ora2pg rewrite add_month(), add_year(), date_trunc() and
2451 to_char() functions, but you may prefer to use the orafce version of
2452 these function that do not need any code transformation.
2453
2454 AUTONOMOUS_TRANSACTION
2455 Enable translation of autonomous transactions into a wrapper
2456 function using dblink or pg_background extension. If you don't want
2457 to use this translation and just want the function to be exported as
2458 a normal one without the pragma call, disable this directive.
2459
2460 Materialized view
2461 Materialized views are exported as snapshot "Snapshot Materialized
2462 Views" as PostgreSQL only supports full refresh.
2463
2464 If you want to import the materialized views in PostgreSQL prior to 9.3
2465 you have to set configuration directive PG_SUPPORTS_MVIEW to 0. In this
2466 case Ora2Pg will export all materialized views as explain in this
2467 document:
2468
2469 http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views.
2470
2471 When exporting materialized view Ora2Pg will first add the SQL code to
2472 create the "materialized_views" table:
2473
2474 CREATE TABLE materialized_views (
2475 mview_name text NOT NULL PRIMARY KEY,
2476 view_name text NOT NULL,
2477 iname text,
2478 last_refresh TIMESTAMP WITH TIME ZONE
2479 );
2480
2481 all materialized views will have an entry in this table. It then adds
2482 the plpgsql code to create tree functions:
2483
2484 create_materialized_view(text, text, text) used to create a materialized view
2485 drop_materialized_view(text) used to delete a materialized view
2486 refresh_full_materialized_view(text) used to refresh a view
2487
2488 then it adds the SQL code to create the view and the materialized view:
2489
2490 CREATE VIEW mviewname_mview AS
2491 SELECT ... FROM ...;
2492
2493 SELECT create_materialized_view('mviewname','mviewname_mview', change with the name of the column to used for the index);
2494
2495 The first argument is the name of the materialized view, the second the
2496 name of the view on which the materialized view is based and the third
2497 is the column name on which the index should be build (aka most of the
2498 time the primary key). This column is not automatically deduced so you
2499 need to replace its name.
2500
2501 As said above Ora2Pg only supports snapshot materialized views so the
2502 table will be entirely refreshed by issuing first a truncate of the
2503 table and then by load again all data from the view:
2504
2505 refresh_full_materialized_view('mviewname');
2506
2507 To drop the materialized view you just have to call the
2508 drop_materialized_view() function with the name of the materialized view
2509 as parameter.
2510
2511 Other configuration directives
2512 DEBUG
2513 Set it to 1 will enable verbose output.
2514
2515 IMPORT
2516 You can define common Ora2Pg configuration directives into a single
2517 file that can be imported into other configuration files with the
2518 IMPORT configuration directive as follow:
2519
2520 IMPORT commonfile.conf
2521
2522 will import all configuration directives defined into
2523 commonfile.conf into the current configuration file.
2524
2525 Exporting views as PostgreSQL tables
2526 You can export any Oracle view as a PostgreSQL table simply by setting
2527 TYPE configuration option to TABLE to have the corresponding create
2528 table statement. Or use type COPY or INSERT to export the corresponding
2529 data. To allow that you have to specify your views in the VIEW_AS_TABLE
2530 configuration option.
2531
2532 Then if Ora2Pg finds the view it will extract its schema (if TYPE=TABLE)
2533 into a PG create table form, then it will extract the data (if TYPE=COPY
2534 or INSERT) following the view schema.
2535
2536 For example, with the following view:
2537
2538 CREATE OR REPLACE VIEW product_prices (category_id, product_count, low_price, high_price) AS
2539 SELECT category_id, COUNT(*) as product_count,
2540 MIN(list_price) as low_price,
2541 MAX(list_price) as high_price
2542 FROM product_information
2543 GROUP BY category_id;
2544
2545 Setting VIEW_AS_TABLE to product_prices and using export type TABLE,
2546 will force Ora2Pg to detect columns returned types and to generate a
2547 create table statement:
2548
2549 CREATE TABLE product_prices (
2550 category_id bigint,
2551 product_count integer,
2552 low_price numeric,
2553 high_price numeric
2554 );
2555
2556 Data will be loaded following the COPY or INSERT export type and the
2557 view declaration.
2558
2559 You can use the ALLOW and EXCLUDE directive in addition to filter other
2560 objects to export.
2561
2562 Export as Kettle transformation XML files
2563 The KETTLE export type is useful if you want to use Penthalo Data
2564 Integrator (Kettle) to import data to PostgreSQL. With this type of
2565 export Ora2Pg will generate one XML Kettle transformation files (.ktr)
2566 per table and add a line to manually execute the transformation in the
2567 output.sql file. For example:
2568
2569 ora2pg -c ora2pg.conf -t KETTLE -j 12 -a MYTABLE -o load_mydata.sh
2570
2571 will generate one file called 'HR.MYTABLE.ktr' and add a line to the
2572 output file (load_mydata.sh):
2573
2574 #!/bin/sh
2575
2576 KETTLE_TEMPLATE_PATH='.'
2577
2578 JAVAMAXMEM=4096 ./pan.sh -file $KETTLE_TEMPLATE_PATH/HR.MYTABLE.ktr -level Detailed
2579
2580 The -j 12 option will create a template with 12 processes to insert data
2581 into PostgreSQL. It is also possible to specify the number of parallel
2582 queries used to extract data from the Oracle with the -J command line
2583 option as follow:
2584
2585 ora2pg -c ora2pg.conf -t KETTLE -J 4 -j 12 -a EMPLOYEES -o load_mydata.sh
2586
2587 This is only possible if you have defined the technical key to used to
2588 split the query between cores in the DEFINED_PKEY configuration
2589 directive. For example:
2590
2591 DEFINED_PK EMPLOYEES:employee_id
2592
2593 will force the number of Oracle connection copies to 4 and defined the
2594 SQL query as follow in the Kettle XML transformation file:
2595
2596 <sql>SELECT * FROM HR.EMPLOYEES WHERE ABS(MOD(employee_id,${Internal.Step.Unique.Count}))=${Internal.Step.Unique.Number}</sql>
2597
2598 The KETTLE export type requires that the Oracle and PostgreSQL DSN are
2599 defined. You can also activate the TRUNCATE_TABLE directive to force a
2600 truncation of the table before data import.
2601
2602 The KETTLE export type is an original work of Marc Cousin.
2603
2604 Migration cost assessment
2605 Estimating the cost of a migration process from Oracle to PostgreSQL is
2606 not easy. To obtain a good assessment of this migration cost, Ora2Pg
2607 will inspect all database objects, all functions and stored procedures
2608 to detect if there's still some objects and PL/SQL code that can not be
2609 automatically converted by Ora2Pg.
2610
2611 Ora2Pg has a content analysis mode that inspect the Oracle database to
2612 generate a text report on what the Oracle database contains and what can
2613 not be exported.
2614
2615 To activate the "analysis and report" mode, you have to use the export
2616 de type SHOW_REPORT like in the following command:
2617
2618 ora2pg -t SHOW_REPORT
2619
2620 Here is a sample report obtained with this command:
2621
2622 --------------------------------------
2623 Ora2Pg: Oracle Database Content Report
2624 --------------------------------------
2625 Version Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
2626 Schema HR
2627 Size 880.00 MB
2628
2629 --------------------------------------
2630 Object Number Invalid Comments
2631 --------------------------------------
2632 CLUSTER 2 0 Clusters are not supported and will not be exported.
2633 FUNCTION 40 0 Total size of function code: 81992.
2634 INDEX 435 0 232 index(es) are concerned by the export, others are automatically generated and will
2635 do so on PostgreSQL. 1 bitmap index(es). 230 b-tree index(es). 1 reversed b-tree index(es)
2636 Note that bitmap index(es) will be exported as b-tree index(es) if any. Cluster, domain,
2637 bitmap join and IOT indexes will not be exported at all. Reverse indexes are not exported
2638 too, you may use a trigram-based index (see pg_trgm) or a reverse() function based index
2639 and search. You may also use 'varchar_pattern_ops', 'text_pattern_ops' or 'bpchar_pattern_ops'
2640 operators in your indexes to improve search with the LIKE operator respectively into
2641 varchar, text or char columns.
2642 MATERIALIZED VIEW 1 0 All materialized view will be exported as snapshot materialized views, they
2643 are only updated when fully refreshed.
2644 PACKAGE BODY 2 1 Total size of package code: 20700.
2645 PROCEDURE 7 0 Total size of procedure code: 19198.
2646 SEQUENCE 160 0 Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL
2647 will be transformed into NEXTVAL('sequence_name') or CURRVAL('sequence_name').
2648 TABLE 265 0 1 external table(s) will be exported as standard table. See EXTERNAL_TO_FDW configuration
2649 directive to export as file_fdw foreign tables or use COPY in your code if you just
2650 want to load data from external files. 2 binary columns. 4 unknown types.
2651 TABLE PARTITION 8 0 Partitions are exported using table inheritance and check constraint. 1 HASH partitions.
2652 2 LIST partitions. 6 RANGE partitions. Note that Hash partitions are not supported.
2653 TRIGGER 30 0 Total size of trigger code: 21677.
2654 TYPE 7 1 5 type(s) are concerned by the export, others are not supported. 2 Nested Tables.
2655 2 Object type. 1 Subtype. 1 Type Boby. 1 Type inherited. 1 Varrays. Note that Type
2656 inherited and Subtype are converted as table, type inheritance is not supported.
2657 TYPE BODY 0 3 Export of type with member method are not supported, they will not be exported.
2658 VIEW 7 0 Views are fully supported, but if you have updatable views you will need to use
2659 INSTEAD OF triggers.
2660 DATABASE LINK 1 0 Database links will not be exported. You may try the dblink perl contrib module or use
2661 the SQL/MED PostgreSQL features with the different Foreign Data Wrapper (FDW) extensions.
2662
2663 Note: Invalid code will not be exported unless the EXPORT_INVALID configuration directive is activated.
2664
2665 Once the database can be analysed, Ora2Pg, by his ability to convert SQL
2666 and PL/SQL code from Oracle syntax to PostgreSQL, can go further by
2667 estimating the code difficulties and estimate the time necessary to
2668 operate a full database migration.
2669
2670 To estimate the migration cost in man-days, Ora2Pg allow you to use a
2671 configuration directive called ESTIMATE_COST that you can also enabled
2672 at command line:
2673
2674 --estimate_cost
2675
2676 This feature can only be used with the SHOW_REPORT, FUNCTION, PROCEDURE,
2677 PACKAGE and QUERY export type.
2678
2679 ora2pg -t SHOW_REPORT --estimate_cost
2680
2681 The generated report is same as above but with a new 'Estimated cost'
2682 column as follow:
2683
2684 --------------------------------------
2685 Ora2Pg: Oracle Database Content Report
2686 --------------------------------------
2687 Version Oracle Database 10g Express Edition Release 10.2.0.1.0
2688 Schema HR
2689 Size 890.00 MB
2690
2691 --------------------------------------
2692 Object Number Invalid Estimated cost Comments
2693 --------------------------------------
2694 DATABASE LINK 3 0 9 Database links will be exported as SQL/MED PostgreSQL's Foreign Data Wrapper (FDW) extensions
2695 using oracle_fdw.
2696 FUNCTION 2 0 7 Total size of function code: 369 bytes. HIGH_SALARY: 2, VALIDATE_SSN: 3.
2697 INDEX 21 0 11 11 index(es) are concerned by the export, others are automatically generated and will do so
2698 on PostgreSQL. 11 b-tree index(es). Note that bitmap index(es) will be exported as b-tree
2699 index(es) if any. Cluster, domain, bitmap join and IOT indexes will not be exported at all.
2700 Reverse indexes are not exported too, you may use a trigram-based index (see pg_trgm) or a
2701 reverse() function based index and search. You may also use 'varchar_pattern_ops', 'text_pattern_ops'
2702 or 'bpchar_pattern_ops' operators in your indexes to improve search with the LIKE operator
2703 respectively into varchar, text or char columns.
2704 JOB 0 0 0 Job are not exported. You may set external cron job with them.
2705 MATERIALIZED VIEW 1 0 3 All materialized view will be exported as snapshot materialized views, they
2706 are only updated when fully refreshed.
2707 PACKAGE BODY 0 2 54 Total size of package code: 2487 bytes. Number of procedures and functions found
2708 inside those packages: 7. two_proc.get_table: 10, emp_mgmt.create_dept: 4,
2709 emp_mgmt.hire: 13, emp_mgmt.increase_comm: 4, emp_mgmt.increase_sal: 4,
2710 emp_mgmt.remove_dept: 3, emp_mgmt.remove_emp: 2.
2711 PROCEDURE 4 0 39 Total size of procedure code: 2436 bytes. TEST_COMMENTAIRE: 2, SECURE_DML: 3,
2712 PHD_GET_TABLE: 24, ADD_JOB_HISTORY: 6.
2713 SEQUENCE 3 0 0 Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL
2714 will be transformed into NEXTVAL('sequence_name') or CURRVAL('sequence_name').
2715 SYNONYM 3 0 4 SYNONYMs will be exported as views. SYNONYMs do not exists with PostgreSQL but a common workaround
2716 is to use views or set the PostgreSQL search_path in your session to access
2717 object outside the current schema.
2718 user1.emp_details_view_v is an alias to hr.emp_details_view.
2719 user1.emp_table is an alias to hr.employees@other_server.
2720 user1.offices is an alias to hr.locations.
2721 TABLE 17 0 8.5 1 external table(s) will be exported as standard table. See EXTERNAL_TO_FDW configuration
2722 directive to export as file_fdw foreign tables or use COPY in your code if you just want to
2723 load data from external files. 2 binary columns. 4 unknown types.
2724 TRIGGER 1 1 4 Total size of trigger code: 123 bytes. UPDATE_JOB_HISTORY: 2.
2725 TYPE 7 1 5 5 type(s) are concerned by the export, others are not supported. 2 Nested Tables. 2 Object type.
2726 1 Subtype. 1 Type Boby. 1 Type inherited. 1 Varrays. Note that Type inherited and Subtype are
2727 converted as table, type inheritance is not supported.
2728 TYPE BODY 0 3 30 Export of type with member method are not supported, they will not be exported.
2729 VIEW 1 1 1 Views are fully supported, but if you have updatable views you will need to use INSTEAD OF triggers.
2730 --------------------------------------
2731 Total 65 8 162.5 162.5 cost migration units means approximatively 2 man day(s).
2732
2733 The last line shows the total estimated migration code in man-days
2734 following the number of migration units estimated for each object. This
2735 migration unit represent around five minutes for a PostgreSQL expert. If
2736 this is your first migration you can get it higher with the
2737 configuration directive COST_UNIT_VALUE or the --cost_unit_value command
2738 line option:
2739
2740 ora2pg -t SHOW_REPORT --estimate_cost --cost_unit_value 10
2741
2742 Ora2Pg is also able to give you a migration difficulty level assessment,
2743 here a sample:
2744
2745 Migration level: B-5
2746
2747 Migration levels:
2748 A - Migration that might be run automatically
2749 B - Migration with code rewrite and a human-days cost up to 5 days
2750 C - Migration with code rewrite and a human-days cost above 5 days
2751 Technical levels:
2752 1 = trivial: no stored functions and no triggers
2753 2 = easy: no stored functions but with triggers, no manual rewriting
2754 3 = simple: stored functions and/or triggers, no manual rewriting
2755 4 = manual: no stored functions but with triggers or views with code rewriting
2756 5 = difficult: stored functions and/or triggers with code rewriting
2757
2758 This assessment consist in a letter A or B to specify if the migration
2759 needs manual rewriting or not. And a number from 1 up to 5 to give you a
2760 technical difficulty level. You have an additional option
2761 --human_days_limit to specify the number of human-days limit where the
2762 migration level should be set to C to indicate that it need a huge
2763 amount of work and a full project management with migration support.
2764 Default is 10 human-days. You can use the configuration directive
2765 HUMAN_DAYS_LIMIT to change this default value permanently.
2766
2767 This feature has been developed to help you or your boss to decide which
2768 database to migrate first and the team that must be mobilized to operate
2769 the migration.
2770
2771 Global Oracle and MySQL migration assessment
2772 Ora2Pg come with a script ora2pg_scanner that can be used when you have
2773 a huge number of instances and schema to scan for migration assessment.
2774
2775 Usage: ora2pg_scanner -l CSVFILE [-o OUTDIR]
2776
2777 -b | --binpath DIR: full path to directory where the ora2pg binary stays.
2778 Might be useful only on Windows OS.
2779 -c | --config FILE: set custom configuration file to use otherwise ora2pg
2780 will use the default: /etc/ora2pg/ora2pg.conf.
2781 -l | --list FILE : CSV file containing a list of databases to scan with
2782 all required information. The first line of the file
2783 can contain the following header that describes the
2784 format that must be used:
2785
2786 "type","schema/database","dsn","user","password"
2787
2788 -o | --outdir DIR : (optional) by default all reports will be dumped to a
2789 directory named 'output', it will be created automatically.
2790 If you want to change the name of this directory, set the name
2791 at second argument.
2792
2793 -t | --test : just try all connections by retrieving the required schema
2794 or database name. Useful to validate your CSV list file.
2795 -u | --unit MIN : redefine globally the migration cost unit value in minutes.
2796 Default is taken from the ora2pg.conf (default 5 minutes).
2797
2798 Here is a full example of a CSV databases list file:
2799
2800 "type","schema/database","dsn","user","password"
2801 "MYSQL","sakila","dbi:mysql:host=192.168.1.10;database=sakila;port=3306","root","secret"
2802 "ORACLE","HR","dbi:Oracle:host=192.168.1.10;sid=XE;port=1521","system","manager"
2803
2804 The CSV field separator must be a comma.
2805
2806 Note that if you want to scan all schemas from an Oracle instance you just
2807 have to leave the schema field empty, Ora2Pg will automatically detect all
2808 available schemas and generate a report for each one. Of course you need to
2809 use a connection user with enough privileges to be able to scan all schemas.
2810 For example:
2811
2812 "ORACLE","","dbi:Oracle:host=192.168.1.10;sid=XE;port=1521","system","manager"
2813
2814 will generate a report for all schema in the XE instance. Note that in this
2815 case the SCHEMA directive in ora2pg.conf must not be set.
2816
2817 It will generate a CSV file with the assessment result, one line per
2818 schema or database and a detailed HTML report for each database scanned.
2819
2820 Hint: Use the -t | --test option before to test all your connections in
2821 your CSV file.
2822
2823 For Windows users you must use the -b command line option to set the
2824 directory where ora2pg_scanner stays otherwise the ora2pg command calls
2825 will fail.
2826
2827 In the migration assessment details about functions Ora2Pg always
2828 include per default 2 migration units for TEST and 1 unit for SIZE per
2829 1000 characters in the code. This mean that by default it will add 15
2830 minutes in the migration assessment per function. Obviously if you have
2831 unitary tests or very simple functions this will not represent the real
2832 migration time.
2833
2834 Migration assessment method
2835 Migration unit scores given to each type of Oracle database object are
2836 defined in the Perl library lib/Ora2Pg/PLSQL.pm in the %OBJECT_SCORE
2837 variable definition.
2838
2839 The number of PL/SQL lines associated to a migration unit is also
2840 defined in this file in the $SIZE_SCORE variable value.
2841
2842 The number of migration units associated to each PL/SQL code
2843 difficulties can be found in the same Perl library lib/Ora2Pg/PLSQL.pm
2844 in the hash %UNCOVERED_SCORE initialization.
2845
2846 This assessment method is a work in progress so I'm expecting feedbacks
2847 on migration experiences to polish the scores/units attributed in those
2848 variables.
2849
2850 Improving indexes and constraints creation speed
2851 Using the LOAD export type and a file containing SQL orders to perform,
2852 it is possible to dispatch those orders over multiple PostgreSQL
2853 connections. To be able to use this feature, the PG_DSN, PG_USER and
2854 PG_PWD must be set. Then:
2855
2856 ora2pg -t LOAD -c config/ora2pg.conf -i schema/tables/INDEXES_table.sql -j 4
2857
2858 will dispatch indexes creation over 4 simultaneous PostgreSQL
2859 connections.
2860
2861 This will considerably accelerate this part of the migration process
2862 with huge data size.
2863
2864 Exporting LONG RAW
2865 If you still have columns defined as LONG RAW, Ora2Pg will not be able
2866 to export these kind of data. The OCI library fail to export them and
2867 always return the same first record. To be able to export the data you
2868 need to transform the field as BLOB by creating a temporary table before
2869 migrating data. For example, the Oracle table:
2870
2871 SQL> DESC TEST_LONGRAW
2872 Name NULL ? Type
2873 -------------------- -------- ----------------------------
2874 ID NUMBER
2875 C1 LONG RAW
2876
2877 need to be "translated" into a table using BLOB as follow:
2878
2879 CREATE TABLE test_blob (id NUMBER, c1 BLOB);
2880
2881 And then copy the data with the following INSERT query:
2882
2883 INSERT INTO test_blob SELECT id, to_lob(c1) FROM test_longraw;
2884
2885 Then you just have to exclude the original table from the export (see
2886 EXCLUDE directive) and to renamed the new temporary table on the fly
2887 using the REPLACE_TABLES configuration directive.
2888
2889 Global variables
2890 Oracle allow the use of global variables defined in packages. Ora2Pg
2891 will export these variables for PostgreSQL as user defined custom
2892 variables available in a session. Oracle variables assignment are
2893 exported as call to:
2894
2895 PERFORM set_config('pkgname.varname', value, false);
2896
2897 Use of these variables in the code is replaced by:
2898
2899 current_setting('pkgname.varname')::global_variables_type;
2900
2901 where global_variables_type is the type of the variable extracted from
2902 the package definition.
2903
2904 If the variable is a constant or have a default value assigned at
2905 declaration, Ora2Pg will create a file global_variables.conf with the
2906 definition to include in the postgresql.conf file so that their values
2907 will already be set at database connection. Note that the value can
2908 always modified by the user so you can not have exactly a constant.
2909
2910 Hints
2911 Converting your queries with Oracle style outer join (+) syntax to ANSI
2912 standard SQL at the Oracle side can save you lot of time for the
2913 migration. You can use TOAD Query Builder can re-write these using the
2914 proper ANSI syntax, see:
2915 http://www.toadworld.com/products/toad-for-oracle/f/10/t/9518.aspx
2916
2917 There's also an alternative with SQL Developer Data Modeler, see
2918 http://www.thatjeffsmith.com/archive/2012/01/sql-developer-data-modeler-
2919 quick-tip-use-oracle-join-syntax-or-ansi/
2920
2921 Toad is also able to rewrite the native Oracle DECODE() syntax into ANSI
2922 standard SQL CASE statement. You can find some slide about this in a
2923 presentation given at PgConf.RU:
2924 http://ora2pg.darold.net/slides/ora2pg_the_hard_way.pdf
2925
2926 Test the migration
2927 The type of action called TEST allow you to check that all objects from
2928 Oracle database have been created under PostgreSQL. Of course PG_DSN
2929 must be set to be able to check PostgreSQL side.
2930
2931 Note that this feature respect the schema name limitation if
2932 EXPORT_SCHEMA and SCHEMA or PG_SCHEMA are defined. If only EXPORT_SCHEMA
2933 is set all schemes from Oracle and PostgreSQL are scanned. You can
2934 filter to a single schema using SCHEMA and/or PG_SCHEMA but you can not
2935 filter on a list of schema. To test a list of schema you will have to
2936 repeat the calls to Ora2Pg by specifying a single schema each time.
2937
2938 For example command:
2939
2940 ora2pg -t TEST -c config/ora2pg.conf > migration_diff.txt
2941
2942 Will create a file containing the report of all object and row count on
2943 both side, Oracle and PostgreSQL, with an error section giving you the
2944 detail of the differences for each kind of object. Here is a sample
2945 result:
2946
2947 [TEST INDEXES COUNT]
2948 ORACLEDB:DEPARTMENTS:2
2949 POSTGRES:departments:1
2950 ORACLEDB:EMPLOYEES:6
2951 POSTGRES:employees:6
2952 [ERRORS INDEXES COUNT]
2953 Table departments doesn't have the same number of indexes in Oracle (2) and in PostgreSQL (1).
2954
2955 [TEST UNIQUE CONSTRAINTS COUNT]
2956 ORACLEDB:DEPARTMENTS:1
2957 POSTGRES:departments:1
2958 ORACLEDB:EMPLOYEES:1
2959 POSTGRES:employees:1
2960 [ERRORS UNIQUE CONSTRAINTS COUNT]
2961 OK, Oracle and PostgreSQL have the same number of unique constraints.
2962
2963 [TEST PRIMARY KEYS COUNT]
2964 ORACLEDB:DEPARTMENTS:1
2965 POSTGRES:departments:1
2966 ORACLEDB:EMPLOYEES:1
2967 POSTGRES:employees:1
2968 [ERRORS PRIMARY KEYS COUNT]
2969 OK, Oracle and PostgreSQL have the same number of primary keys.
2970
2971 [TEST CHECK CONSTRAINTS COUNT]
2972 ORACLEDB:DEPARTMENTS:1
2973 POSTGRES:departments:1
2974 ORACLEDB:EMPLOYEES:1
2975 POSTGRES:employees:1
2976 [ERRORS CHECK CONSTRAINTS COUNT]
2977 OK, Oracle and PostgreSQL have the same number of check constraints.
2978
2979 [TEST NOT NULL CONSTRAINTS COUNT]
2980 ORACLEDB:DEPARTMENTS:1
2981 POSTGRES:departments:1
2982 ORACLEDB:EMPLOYEES:1
2983 POSTGRES:employees:1
2984 [ERRORS NOT NULL CONSTRAINTS COUNT]
2985 OK, Oracle and PostgreSQL have the same number of not null constraints.
2986
2987 [TEST COLUMN DEFAULT VALUE COUNT]
2988 ORACLEDB:DEPARTMENTS:1
2989 POSTGRES:departments:1
2990 ORACLEDB:EMPLOYEES:1
2991 POSTGRES:employees:1
2992 [ERRORS COLUMN DEFAULT VALUE COUNT]
2993 OK, Oracle and PostgreSQL have the same number of column default value.
2994
2995 [TEST IDENTITY COLUMN COUNT]
2996 ORACLEDB:DEPARTMENTS:1
2997 POSTGRES:departments:1
2998 ORACLEDB:EMPLOYEES:0
2999 POSTGRES:employees:0
3000 [ERRORS IDENTITY COLUMN COUNT]
3001 OK, Oracle and PostgreSQL have the same number of identity column.
3002
3003 [TEST FOREIGN KEYS COUNT]
3004 ORACLEDB:DEPARTMENTS:0
3005 POSTGRES:departments:0
3006 ORACLEDB:EMPLOYEES:1
3007 POSTGRES:employees:1
3008 [ERRORS FOREIGN KEYS COUNT]
3009 OK, Oracle and PostgreSQL have the same number of foreign keys.
3010
3011 [TEST TABLE COUNT]
3012 ORACLEDB:TABLE:2
3013 POSTGRES:TABLE:2
3014 [ERRORS TABLE COUNT]
3015 OK, Oracle and PostgreSQL have the same number of TABLE.
3016
3017 [TEST TABLE TRIGGERS COUNT]
3018 ORACLEDB:DEPARTMENTS:0
3019 POSTGRES:departments:0
3020 ORACLEDB:EMPLOYEES:1
3021 POSTGRES:employees:1
3022 [ERRORS TABLE TRIGGERS COUNT]
3023 OK, Oracle and PostgreSQL have the same number of table triggers.
3024
3025 [TEST TRIGGER COUNT]
3026 ORACLEDB:TRIGGER:2
3027 POSTGRES:TRIGGER:2
3028 [ERRORS TRIGGER COUNT]
3029 OK, Oracle and PostgreSQL have the same number of TRIGGER.
3030
3031 [TEST VIEW COUNT]
3032 ORACLEDB:VIEW:1
3033 POSTGRES:VIEW:1
3034 [ERRORS VIEW COUNT]
3035 OK, Oracle and PostgreSQL have the same number of VIEW.
3036
3037 [TEST MVIEW COUNT]
3038 ORACLEDB:MVIEW:0
3039 POSTGRES:MVIEW:0
3040 [ERRORS MVIEW COUNT]
3041 OK, Oracle and PostgreSQL have the same number of MVIEW.
3042
3043 [TEST SEQUENCE COUNT]
3044 ORACLEDB:SEQUENCE:1
3045 POSTGRES:SEQUENCE:0
3046 [ERRORS SEQUENCE COUNT]
3047 SEQUENCE does not have the same count in Oracle (1) and in PostgreSQL (0).
3048
3049 [TEST TYPE COUNT]
3050 ORACLEDB:TYPE:1
3051 POSTGRES:TYPE:0
3052 [ERRORS TYPE COUNT]
3053 TYPE does not have the same count in Oracle (1) and in PostgreSQL (0).
3054
3055 [TEST FDW COUNT]
3056 ORACLEDB:FDW:0
3057 POSTGRES:FDW:0
3058 [ERRORS FDW COUNT]
3059 OK, Oracle and PostgreSQL have the same number of FDW.
3060
3061 [TEST FUNCTION COUNT]
3062 ORACLEDB:FUNCTION:3
3063 POSTGRES:FUNCTION:3
3064 [ERRORS FUNCTION COUNT]
3065 OK, Oracle and PostgreSQL have the same number of functions.
3066
3067 [TEST SEQUENCE VALUES]
3068 ORACLEDB:EMPLOYEES_NUM_SEQ:1285
3069 POSTGRES:employees_num_seq:1285
3070 [ERRORS SEQUENCE VALUES COUNT]
3071 OK, Oracle and PostgreSQL have the same values for sequences
3072
3073 [TEST ROWS COUNT]
3074 ORACLEDB:DEPARTMENTS:27
3075 POSTGRES:departments:27
3076 ORACLEDB:EMPLOYEES:854
3077 POSTGRES:employees:854
3078 [ERRORS ROWS COUNT]
3079 OK, Oracle and PostgreSQL have the same number of rows.
3080
3081SUPPORT
3082 Author / Maintainer
3083 Gilles Darold <gilles AT darold DOT net>
3084
3085 Please report any bugs, patches, help, etc. to <gilles AT darold DOT
3086 net>.
3087
3088 Feature request
3089 If you need new features let me know at <gilles AT darold DOT net>. This
3090 help a lot to develop a better/useful tool.
3091
3092 How to contribute ?
3093 Any contribution to build a better tool is welcome, you just have to
3094 send me your ideas, features request or patches and there will be
3095 applied.
3096
3097LICENSE
3098 Copyright (c) 2000-2021 Gilles Darold - All rights reserved.
3099
3100 This program is free software: you can redistribute it and/or modify
3101 it under the terms of the GNU General Public License as published by
3102 the Free Software Foundation, either version 3 of the License, or
3103 any later version.
3104
3105 This program is distributed in the hope that it will be useful,
3106 but WITHOUT ANY WARRANTY; without even the implied warranty of
3107 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
3108 GNU General Public License for more details.
3109
3110 You should have received a copy of the GNU General Public License
3111 along with this program. If not, see < http://www.gnu.org/licenses/ >.
3112
3113ACKNOWLEDGEMENT
3114 I must thanks a lot all the great contributors, see changelog for all
3115 acknowledgments.
3116
3117