forked from darold/ora2pg
-
Notifications
You must be signed in to change notification settings - Fork 0
/
README
1516 lines (1220 loc) · 74.8 KB
/
README
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
NAME
Ora2Pg - Oracle to PostgreSQL database schema converter
DESCRIPTION
Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL
compatible schema. It connects your Oracle database, scan it
automatically and extracts its structure or data, it then generates SQL
scripts that you can load into your PostgreSQL database.
Ora2Pg can be used from reverse engineering Oracle database to huge
enterprise database migration or simply to replicate some Oracle data
into a PostgreSQL database. It is really easy to used and doesn't need
any Oracle database knowledge than providing the parameters needed to
connect to the Oracle database.
FEATURES
Ora2Pg consist of a Perl script (ora2pg) and a Perl module (Ora2Pg.pm),
the only thing you have to modify is the configuration file ora2pg.conf
by setting the DSN to the Oracle database and optionaly the name of a
schema. Once that's done you just have to set the type of export you
want: TABLE with constraints, VIEW, MVIEW, TABLESPACE, SEQUENCE,
INDEXES, TRIGGER, GRANT, FUNCTION, PROCEDURE, PACKAGE, PARTITION, TYPE,
INSERT or COPY, FDW, QUERY.
By default Ora2Pg exports to a file that you can load into PostgreSQL
with the psql client, but you can also import directly into a PostgreSQL
database by setting its DSN into the configuration file. With all
configuration options of ora2pg.conf you have full control of what
should be exported and how.
Features included:
- Export full database schema (tables, views, sequences, indexes), with
unique, primary, foreign key and check constraints.
- Export grants/privileges for users and groups.
- Export range and list partition.
- Export a table selection (by specifying the table names).
- Export Oracle schema to a PostgreSQL 8.4+ schema.
- Export predefined functions, triggers, procedures, packages and
package bodies.
- Export full data or following a WHERE clause.
- Full support of Oracle BLOB object as PG BYTEA.
- Export Oracle views as PG tables.
- Export Oracle user defined types.
- Provide some basic automatic conversion of PLSQL code to PLPGSQL.
- Works on any plateform.
- Export Oracle tables as foreign data wrapper tables.
- Export materialized view.
- Show a detailled report of an Oracle database content.
- Migration cost assessment of an Oracle database.
- Migration cost assessment of PL/SQL code from a file.
- Migration cost assessment of Oracle SQL queries stored in a file.
Ora2Pg do its best to automatically convert your Oracle database to
PostgreSQL but there's still manual works to do. The Oracle specific
PL/SQL code generated for functions, procedures, packages and triggers
has to be reviewed to match the PostgreSQL syntax. You will find some
useful recommandations on porting Oracle PL/SQL code to PostgreSQL
PL/PGSQL at "Converting from other Databases to PostgreSQL", section:
Oracle (http://wiki.postgresql.org/wiki/Main_Page).
See http://ora2pg.darold.net/report.html for a HTML sample of an Oracle
database migration report.
INSTALLATION
All Perl modules can always be found at CPAN (http://search.cpan.org/).
Just type the full name of the module (ex: DBD::Oracle) into the search
input box, it will brings you the page for download.
Releases of Ora2Pg stay at SF.net
(https://sourceforge.net/projects/ora2pg/).
Under Windows you should install Strawberry Perl
(http://strawberryperl.com/) and the OSes corresponding Oracle clients.
It seems that compiling DBD::Oracle from CPAN on Windows can be a
struggle and there be little documentation on that (mostly outdated and
not working). Installing the free version of ActiveState Perl
(http://www.activestate.com/activeperl) could help as they seems to have
an already packaged DBD::Oracle easy to install.
Requirement
You need a modern Perl distribution (perl 5.6 and more), the DBI and
DBD::Oracle Perl modules to be installed. These are used to connect to
the Oracle database. To install DBD::Oracle and have it working you need
to have the Oracle client libraries installed and the ORACLE_HOME
environment variable must be defined.
Optional
By default Ora2Pg dumps export to flat files, to load them into your
PostgreSQL database you need the PostgreSQL client (psql). If you don't
have it on the host running Ora2Pg you can always transfer these files
to a host with the psql client installed. If you prefer to load export
'on the fly', the perl module DBD::Pg is required.
Ora2Pg allow to dump all output int a compressed gzip file, to do that
you need the Compress::Zlib Perl module or if you prefer using bzip2
compression, the program bzip2 must be available in your PATH.
Installing Ora2Pg
Like any other Perl Module Ora2Pg can be installed with the following
commands:
tar xzf ora2pg-10.x.tar.gz
cd ora2pg-10.x/
perl Makefile.PL
make && make install
This will install Ora2Pg.pm into your site Perl repository, ora2pg into
/usr/local/bin/ and ora2pg.conf into /etc/ora2pg/.
On Windows(tm) OSes you may use instead:
perl Makefile.PL
dmake && dmake install
This will install scripts and libraries into your Perl site installation
directory and the ora2pg.conf file as well as all documentation files
into C:\ora2pg\
Packaging
If you want to build binary package for your preferred Linux
distribution take a look at the packaging/ directory of the source
tarball. There's everything to build RPM, Slackware and Debian packages.
See README file in that directory.
CONFIGURATION
Ora2Pg configuration can be as simple as choose the Oracle database to
export and choose the export type. This can be done in the minute.
By reading this documentation you will also be able to:
- Select only certain tables and/or column for export.
- Rename some tables and/or column during export.
- Select data to export following a WHERE clause per table.
- Delay database constraints during data loading.
- Compress exported data to save disk space.
- and much more.
The full control of the Oracle database migration is taken though a
single configuration file named ora2pg.conf. The format of this file
consist in a directive name in upper case followed by tab character and
a value. Comments are lines beginning with a #.
Ora2Pg usage
By default Ora2Pg will look for /etc/ora2pg/ora2pg.conf configuration
file, if the file exist you can simply execute:
/usr/local/bin/ora2pg
If you want to call another configuration file, just give the path as
command line argument:
/usr/local/bin/ora2pg --config /etc/ora2pg/new_ora2pg.conf
Here are all command line parameters available when using ora2pg:
Usage: ora2pg [-dhpqsv] [--option value]
-a | --allow str : coma separated list of objects to allow from export.
Can be used with SHOW_COLUMN too.
-b | --basedir dir: Used to set the default output directory, where files
resulting from exports will be stored.
-c | --conf file : Used to set an alternate configuration file than the
default /etc/ora2pg/ora2pg.conf.
-d | --debug : Enable verbose output.
-e | --exclude str: coma separated list of objects to exclude from export.
Can be used with SHOW_COLUMN too.
-h | --help : Print this short help.
-i | --input file : File containing Oracle PL/SQL code to convert with
no Oracle database connection initiated.
-l | --log file : Used to set a log file. Default is stdout.
-n | --namespace schema : Used to set the Oracle schema to extract from.
-o | --out file : Used to set the path to the output file where SQL will
be written. Default: output.sql in running directory.
-p | --plsql : Enable PLSQL to PLPSQL code conversion.
-q | --quiet : disable progress bar.
-s | --source : Allow to set the Oracle DBI datasource.
-t | --type export: Used to set the export type. It will override the one
given in the configuration file (TYPE).
-u | --user name : Used to set the Oracle database connection user.
-v | --version : Show Ora2Pg Version and exit.
-w | --password pwd : Used to set the password of the Oracle database user.
--forceowner: if set to 1 force ora2pg to set tables and sequences owner
like in Oracle database. If the value is set to a username this
one will be used as the objects owner. By default it's the user
used to connect to the Pg database that will be the owner.
--nls_lang code: use this to set the Oracle NLS_LANG client encoding.
--client_encoding code: Use this to set the PostgreSQL client encoding.
--view_as_table str: coma separated list of view to export as table.
--estimate_cost : activate the migration cost evalution with SHOW_REPORT
--cost_unit_value minutes: number of minutes for a cost evalution unit.
default: 5 minutes, correspond to a migration conducted by a
PostgreSQL expert. Set it to 10 if this is your first migration.
--dump_as_html : force ora2pg to dump report in HTML, used only with
SHOW_REPORT. Default is to dump report as simple text.
See full documentation at http://ora2pg.darold.net/ for more help or see
manpage with 'man ora2pg'.
It is possible to add your own custom option(s) in the Perl script
ora2pg as any configuration directive from ora2pg.conf can be passed in
lower case to the new Ora2Pg object instance. See ora2pg code on how to
add your own option.
Oracle database connection
There's 5 configuration directives to control the access to the Oracle
database.
ORACLE_HOME
Used to set ORACLE_HOME environment variable to the Oracle libraries
required by the DBD::Oracle Perl module.
ORACLE_DSN
This directive is used to set the data source name in the form
standard DBI DSN. For example:
dbi:Oracle:host=oradb_host.myhost.com;sid=DB_SID
or
dbi:Oracle:DB_SID
for the second notation the SID should be declared in the well known
file $ORACLE_HOME/network/admin/tnsnames.ora.
ORACLE_USER et ORACLE_PWD
These two directives are used to define the user and password for
the Oracle database connection. Note that if you can it is better to
login as Oracle super admin to avoid grants problem during the
database scan and be sure that nothing is missing.
USER_GRANTS
Set this directive to 1 if you connect the Oracle database as simple
user and do not have enough grants to extract things from the
DBA_... tables. It will use tables ALL_... instead.
Warning: if you use export type GRANT, you must set this
configuration option to 0 or it will not works.
TRANSACTION
This directive may be used if you want to change the default
isolation level of the data export transaction. Default is now to
set the level to a serializable transaction to ensure data
consistency. The allowed values for this directive are:
readonly: 'SET TRANSACTION READ ONLY',
readwrite: 'SET TRANSACTION READ WRITE',
serializable: 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'
committed: 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED',
Releases before 6.2 used to set the isolation level to READ ONLY
transaction but in some case this was breaking data consistency so
now default is set to SERIALIZABLE.
INPUT_FILE
This directive did not control the Oracle database connection or
unless it purely disable the use of any Oracle database by accepting
a file as argument. Set this directive to a file containing PL/SQL
Oracle Code like function, procedure or full package body to prevent
Ora2Pg from connecting to an Oracle database end just apply his
convertion tool to the content of the file. This can only be used
with the following export type: PROCEDURE, FUNCTION or PACKAGE. If
you don't know what you do don't use this directive, I use it to
find PL/SQL parser and PL/PGSQL converter issues.
Data encryption with Oracle server
If your Oracle Client config file already includes the encryption
method, then DBD:Oracle uses those settings to encrypt the connection
while you extract the data. For example if you have configured the
Oracle Client config file (sqlnet.or or .sqlnet) with the following
information:
# Configure encryption of connections to Oracle
SQLNET.ENCRYPTION_CLIENT = required
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256, RC4_256)
SQLNET.CRYPTO_SEED = 'should be 10-70 random characters'
Any tool that uses the Oracle client to talk to the database will be
encrypted if you setup a session encryption like above.
For example, Perl's DBI uses DBD-Oracle, which uses the Oracle client
for actually handling database communication. If the installation of
Oracle client used by Perl is setup to request encrypted connections,
then your Perl connection to an Oracle database will also be encrypted.
Full details at
https://kb.berkeley.edu/jivekb/entry.jspa?externalID=1005
Testing
Once you have set the Oracle database DSN you can execute ora2pg to see
if it works. By default the configuration file will export the database
schema to a file called 'output.sql'. Take a look in it to see if the
schema has been exported.
Take some time here to test your installation as most of the problem
take place here, the other configuration step are more technical.
Trouble shooting
If the output.sql file has not exported anything else than the Pg
transaction header and footer there's two possible reasons. The perl
script ora2pg dump an ORA-XXX error, that mean that you DSN or login
information are wrong, check the error and your settings and try again.
The perl script says nothing and the output file is empty: the user has
not enough right to extract something from the database. Try to connect
Oracle as super user or take a look at directive USER_GRANTS above and
at next section, especiallly the SCHEMA directive.
LOGFILE
By default all message are sent to the standard output. If you give
a file path to that directive, all output will be appended to this
file.
Oracle schema to export
The Oracle database export can be limited to a specific Schema or
Namespace, this can be mandatory following the database connection user.
SCHEMA
This directive is used to set the schema name to use during export.
For example:
SCHEMA APPS
will extract objects associated to the APPS schema.
EXPORT_SCHEMA
By default the Oracle schema is not exported into the PostgreSQL
database and all objects are created under the default Pg namespace.
If you want to also export this schema and create all objects under
this namespace, set the EXPORT_SCHEMA directive to 1. This will set
the schema search_path at top of export SQL file to the schema name
set in the SCHEMA directive with the default pg_catalog schema. If
you want to change this path, use the directive PG_SCHEMA.
CREATE_SCHEMA
Enable/disable the CREATE SCHEMA SQL order at starting of the output
file. It is enable by default and concern on TABLE export type.
COMPILE_SCHEMA
By default Ora2Pg will only export valid PL/SQL code. You can force
Oracle to compile again the invalidated code to get a chance to have
it obtain the valid status and then be able to export it.
Enable this directive to force Oracle to compile schema before
exporting code. This will ask to Oracle to validate the PL/SQL that
could have been invalidate after a export/import for example. If you
set the value to 1 it will exec: DBMS_UTILITY.compile_schema(schema
=> sys_context('USERENV', 'SESSION_USER')); but if you provide the
name of a particular schema it will use the following command:
DBMS_UTILITY.compile_schema(schema => 'schemaname'); The 'VALID' or
'INVALID' status applies to functions, procedures, packages and user
defined types.
EXPORT_INVALID
If the above configuration directive is not enough to validate your
PL/SQL code enable this configuration directive to allow export of
all PL/SQL code even if it is marked as invalid. The 'VALID' or
'INVALID' status applies to functions, procedures, packages and user
defined types.
PG_SCHEMA
Allow you to defined/force the PostgreSQL schema to use. The value
can be a coma delimited list of schema name. By default if you set
EXPORT_SCHEMA to 1, the PostgreSQL schema search_path will be set to
the schema name set as value of the SCHEMA directive plus the
default pg_catalog schema as follow:
SET search_path = $SCHEMA, pg_catalog;
If you set PG_SCHEMA to something like "user_schema, public" for
example the search path will be set like this:
SET search_path = $PG_SCHEMA;
-- SET search_path = user_schema, public;
This will force to not use the Oracle schema set in the SCHEMA
directive.
SYSUSERS
Without explicit schema, Ora2Pg will export all objects that not
belongs to system schema or role: SYS, SYSTEM, DBSNMP, OUTLN,
PERFSTAT, CTXSYS, XDB, WMSYS, SYSMAN, SQLTXPLAIN, MDSYS, EXFSYS,
ORDSYS, DMSYS, OLAPSYS, FLOWS_020100, FLOWS_FILES, TSMSYS. Following
your Oracle installation you may have several other system role
defined. To append these users to the schema exclusion list, just
set the SYSUSERS configuration directive to a coma separated list of
system user to exclude. For example:
SYSUSERS INTERNAL,SYSDBA
will add users INTERNAL and SYSDBA to the schema exclusion list.
FORCE_OWNER
By default the owner of the database objects is the one you're using
to connect to PostgreSQL using the psql command. If you use an other
user (postgres for exemple) you can force Ora2Pg to set the object
owner to be the one used in the Oracle database by setting the
directive to 1, or to a completely different username by setting the
directive value to that username.
Export type
The export action is perform following a single configuration directive
'TYPE', some other add more control on what should be really exported.
TYPE
Here are the different values of the TYPE directive, default is
TABLE:
- TABLE: Extract all tables with indexes, primary keys, unique keys,
foreign keys and check constraints.
- VIEW: Extract only views.
- GRANT: Extract roles converted to Pg groups, users and grants on all
objects.
- SEQUENCE: Extract all sequence and their last position.
- TABLESPACE: Extract storage spaces for tables and indexes (Pg >= v8).
- TRIGGER: Extract triggers defined following actions.
- FUNCTION: Extract functions.
- PROCEDURE: Extract procedures.
- PACKAGE: Extract packages and package bodies.
- INSERT: Extract data as INSERT statement.
- COPY: Extract data as COPY statement.
- PARTITION: Extract range and list Oracle partitioning.
- TYPE: Extract user defined Oracle type.
- FDW: Export Oracle tables as foreign table for oracle_fdw.
- MVIEW: Export materialized view.
- QUERY: Try to automatically convert Oracle SQL queries.
Only one type of export can be perform at the same time so the TYPE
directive must be unique. If you have more than one only the last
found in the file will be registered.
Some export type can not or should not be load directly into the
PostgreSQL database and still require little manual editing. This is
the case for GRANT, TABLESPACE, TRIGGER, FUNCTION, PROCEDURE, TYPE,
QUERY and PACKAGE export types especially if you have PLSQL code or
Oracle specific SQL in it.
For TABLESPACE you must ensure that file path exist on the system.
Note that you can chained multiple export by giving to the TYPE
directive a coma separated list of export type.
The PARTITION export is a work in progress as table partition
support is not yet implemented into PostgreSQL. Ora2Pg will convert
Oracle partition using table inheritence, trigger and function
workaround. See document at Pg site:
http://www.postgresql.org/docs/current/interactive/ddl-partitioning.
html This new feature in Ora2Pg has not been widly tested so feel
free to report any bug and patch.
The TYPE export allow export of user defined Oracle type. If you
don't use the --plsql command line parameter it simply dump Oracle
user type asis else Ora2Pg will try to convert it to PostgreSQL
syntax.
Since Ora2Pg v8.1 there's three new export types:
SHOW_VERSION : display Oracle version
SHOW_SCHEMA : display the list of schema available in the database.
SHOW_TABLE : display the list of tables available.
SHOW_COLUMN : display the list of tables columns available and the
Ora2PG conversion type from Oracle to PostgreSQL that will be
applied. It will also warn you if there's PostgreSQL reserved
words in Oracle object names.
Here is an example of the SHOW_COLUMN output:
[2] TABLE CURRENT_SCHEMA (1 rows) (Warning: 'CURRENT_SCHEMA' is a reserved word in PostgreSQL)
CONSTRAINT : NUMBER(22) => bigint (Warning: 'CONSTRAINT' is a reserved word in PostgreSQL)
FREEZE : VARCHAR2(25) => varchar(25) (Warning: 'FREEZE' is a reserved word in PostgreSQL)
...
[6] TABLE LOCATIONS (23 rows)
LOCATION_ID : NUMBER(4) => smallint
STREET_ADDRESS : VARCHAR2(40) => varchar(40)
POSTAL_CODE : VARCHAR2(12) => varchar(12)
CITY : VARCHAR2(30) => varchar(30)
STATE_PROVINCE : VARCHAR2(25) => varchar(25)
COUNTRY_ID : CHAR(2) => char(2)
Those extraction keyword are use to only display the requested
information and exit. This allow you to quickly know on what you are
going to work.
The SHOW_COLUMN allow an other ora2pg command line option: '--allow
relname' or '-a relname' to limit the displayed information to the
given table.
Since Ora2Pg v8.2 there's a new export type:
SHOW_ENCODING : display the Oracle session encoding, useful to set NSL_LANG.
Since release v8.12, Ora2Pg allow you to export your Oracle Table
definition to be use with the oracle_fdw foreign data wrapper. By
using type FDW your Oracle tables will be exported as follow:
CREATE FOREIGN TABLE oratab (
id integer NOT NULL,
text character varying(30),
floating double precision NOT NULL
) SERVER oradb OPTIONS (table 'ORATAB');
Now you can use the table like a regular PostgreSQL table.
See http://pgxn.org/dist/oracle_fdw/ for more information on this
foreign data wrapper.
Release 10 adds a new export type destinated to evaluate the content
of the database to migrate, in terms of objects and cost to end the
migration:
SHOW_REPORT : show a detailled report of the Oracle database content.
Here is a sample of report:
--------------------------------------
Ora2Pg: Oracle Database Content Report
--------------------------------------
Version Oracle Database 10g Express Edition Release 10.2.0.1.0
Schema HR
Size 880.00 MB
--------------------------------------
Object Number Invalid Comments
--------------------------------------
CLUSTER 2 0 Clusters are not supported and will not be exported.
FUNCTION 40 0 Total size of function code: 81992.
INDEX 435 0 232 index(es) are concerned by the export, others are automatically generated and will
do so on PostgreSQL. 1 bitmap index(es). 230 b-tree index(es). 1 reversed b-tree index(es)
Note that bitmap index(es) will be exported as b-tree index(es) if any. Cluster, domain,
bitmap join and IOT indexes will not be exported at all. Reverse indexes are not exported
too, you may use a trigram-based index (see pg_trgm) or a reverse() function based index
and search. You may also use 'varchar_pattern_ops', 'text_pattern_ops' or 'bpchar_pattern_ops'
operators in your indexes to improve search with the LIKE operator respectively into
varchar, text or char columns.
MATERIALIZED VIEW 1 0 All materialized view will be exported as snapshot materialized views, they
are only updated when fully refreshed.
PACKAGE BODY 2 1 Total size of package code: 20700.
PROCEDURE 7 0 Total size of procedure code: 19198.
SEQUENCE 160 0 Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL
will be transformed into NEXTVAL('sequence_name') or CURRVAL('sequence_name').
TABLE 265 0 1 external table(s) will be exported as standard table. See EXTERNAL_TO_FDW configuration
directive to export as file_fdw foreign tables or use COPY in your code if you just
want to load data from external files. 2 binary columns. 4 unknow types.
TABLE PARTITION 8 0 Partitions are exported using table inheritance and check constraint. 1 HASH partitions.
2 LIST partitions. 6 RANGE partitions. Note that Hash partitions are not supported.
TRIGGER 30 0 Total size of trigger code: 21677.
TYPE 7 1 5 type(s) are concerned by the export, others are not supported. 2 Nested Tables.
2 Object type. 1 Subtype. 1 Type Boby. 1 Type inherited. 1 Varrays. Note that Type
inherited and Subtype are converted as table, type inheritance is not supported.
TYPE BODY 0 3 Export of type with member method are not supported, they will not be exported.
VIEW 7 0 Views are fully supported, but if you have updatable views you will need to use
INSTEAD OF triggers.
DATABASE LINK 1 0 Database links will not be exported. You may try the dblink perl contrib module or use
the SQL/MED PostgreSQL features with the different Foreign Data Wrapper (FDW) extentions.
Note: Invalid code will not be exported unless the EXPORT_INVALID configuration directive is activated.
There also a more advanced report with migration cost. See the
dedicated chapter about Migration Cost Evaluation.
ESTIMATE_COST
Activate the migration cost evaluation. Must only be used with
SHOW_REPORT, FUNCTION, PROCEDURE, PACKAGE and QUERY export type.
Default is disabled. You may wat to use the --estimate_cost command
line option instead to activate this functionnality.
COST_UNIT_VALUE
Set the value in minutes of the migration cost evaluation unit.
Default is five minutes per unit. See --cost_unit_value to change
the unit value at command line.
DUMP_AS_HTML
By default when using SHOW_REPORT the migration report is generated
as simple text, enabling this directive will force ora2pg to create
a report in HTML format.
See http://ora2pg.darold.net/report.html for a sample report.
THREAD_COUNT
This configuration directive adds multi-threading support to data
export type, the value is the number of threads to use. Default to
zero, disabled multi- threading.
It is only used to do the escaping to convert LOBs to byteas, as it
is very CPU hungry. Putting 6 threads will only triple your
throughput, if your machine has enough cores. If zero do not use
threads, do not waste CPU, but be slower with bytea.
Performance seems to peak at 5 threads, if you have enough cores,
and triples throughput on tables having LOB. Another important
thing: because of the way threading works in perl, threads consume a
lot of memory. Put a low (5000 for instance) DATA_LIMIT if you
activate threading.
If your Perl installation do not support threads, multi-threading
will not be enabled. This configuration directive is available since
Ora2Pg v8.7 thanks to the work of Marc Cousin.
FDW_SERVER
This directive is used to set the name of the foreign data server
that is used in the "CREATE SERVER name FOREIGN DATA WRAPPER
oracle_fdw ..." command. This name will then be used in the "CREATE
FOREIGN TABLE ..." SQL command. Default is arbitrary set to orcl.
This only concern export type FDW.
EXTERNAL_TO_FDW
This directive, enabled by default, allow to export Oracle's
External Tables as file_fdw foreign tables. To not export these
tables at all, set the directive to 0.
Limiting object to export
You may want to export only a part of an Oracle database, here are a set
of configuration directives that will allow you to control what parts of
the database should be exported.
ALLOW
This directive allow you to set a list of objects on witch the
export must be limited, excluding all other objects in the same type
of export. The value is a space separated list of objects name to
export. You can include valid regex into the list. For example:
ALLOW EMPLOYEES SALE_.* COUNTRIES .*_GEOM_SEQ
will export objects with name EMPLOYEES, COUNTRIES, all objects
begining with 'SALE_' and all objects with a name ending by
'_GEOM_SEQ'. The object depends of the export type.
This directive replace the obsolète 'TABLES' configuration
directive, this is just a renaming to be less confusing.
EXCLUDE
This directive is the opposite of the previous, it allow you to
define a space separated list of object name to exclude from the
export. You can include valid regex into the list. For example:
EXCLUDE EMPLOYEES TMP_.* COUNTRIES
will exclude object with name EMPLOYEES, COUNTRIES and all tables
begining with 'tmp_'.
For example, you can ban from export some unwanted function with
this directive:
EXCLUDE write_to_.* send_mail_.*
this example will exclude all functions, procedures or functions in
a package with the name begining with those regex.
VIEW_AS_TABLE
Set which view to export as table. By default none. Value must be a
list of view name or regexp separated by space. If the object name
is a view and the export type is TABLE, the view will be exported as
a create table statement. If export type is COPY or INSERT, the
corresponding data will be exported.
This was initialy done with the ALLOW (or old TABLES directive) but
that was not allow to use both object exclusion and view as table.
See chapter "Exporting Oracle views as PostgreSQL tables" for more
details.
WHERE
This directive allow you to specify a WHERE clause filter when
dumping the contents of tables. Value is construct as follow:
TABLE_NAME[WHERE_CLAUSE], or if you have only one where clause for
each table just put the where clause as value. Both are possible
too. Here are some examples:
# Global where clause applying to all tables included in the export
WHERE 1=1
# Apply the where clause only on table TABLE_NAME
WHERE TABLE_NAME[ID1='001']
# Applies two different clause on tables TABLE_NAME and OTHER_TABLE
# and a generic where clause on DATE_CREATE to all other tables
WHERE TABLE_NAME[ID1='001' AND ID1='002] DATE_CREATE > '2001-01-01' OTHER_TABLE[NAME='test']
Any where clause not included into a table name bracket clause will
be applied to all exported table including the tables defined in the
where clause. These WHERE clauses are very useful if you want to
archive some data or at the opposite only export some recent data.
Modifying object structure
One of the great usage of Ora2Pg is its flexibility to replicate Oracle
database into PostgreSQL database with a different structure or schema.
There's three configuration directives that allow you to map those
differences.
MODIFY_STRUCT
This directive allow you to limit the columns to extract for a given
table. The value consist in a space separated list of table name
with a set of column between parenthesis as follow:
MODIFY_STRUCT NOM_TABLE(nomcol1,nomcol2,...) ...
for example:
MODIFY_STRUCT T_TEST1(id,dossier) T_TEST2(id,fichier)
This will only extract columns 'id' and 'dossier' from table T_TEST1
and columns 'id' and 'fichier' from the T_TEST2 table.
REPLACE_TABLES
This directive allow you to remap a list of Oracle table name to a
PostgreSQL table name during export. The value is a list of space
separated values with the following structure:
REPLACE_TABLES ORIG_TBNAME1:DEST_TBNAME1 ORIG_TBNAME2:DEST_TBNAME2
Oracle tables ORIG_TBNAME1 and ORIG_TBNAME2 will be respectively
renamed into DEST_TBNAME1 and DEST_TBNAME2
REPLACE_COLS
Like table name, the name of the column can be remapped to a
different name using the following syntaxe:
REPLACE_COLS ORIG_TBNAME(ORIG_COLNAME1:NEW_COLNAME1,ORIG_COLNAME2:NEW_COLNAME2)
For example:
REPLACE_COLS T_TEST(dico:dictionary,dossier:folder)
will rename Oracle columns 'dico' and 'dossier' from table T_TEST
into new name 'dictionary' and 'folder'.
REPLACE_AS_BOOLEAN
If you want to change the type of some Oracle columns into
PostgreSQL boolean during the export you can define here a list of
tables and column separated by space as follow.
REPLACE_AS_BOOLEAN TB_NAME1:COL_NAME1 TB_NAME1:COL_NAME2 TB_NAME2:COL_NAME2
The values set in the boolean columns list will be replaced with the
't' and 'f' following the default replacement values and those
additionally set in directive BOOLEAN_VALUES.
You can also give a type and a precision to automatically convert
all fields of that type as a boolean. For example:
REPLACE_AS_BOOLEAN NUMBER:1 CHAR:1 TB_NAME1:COL_NAME1 TB_NAME1:COL_NAME2
will also replace any field of type number(1) or char(1) as a
boolean in all exported tables.
BOOLEAN_VALUES
Use this to add additional definition of the possible boolean values
used in Oracle fields. You must set a space separated list of
TRUE:FALSE values. By default here are the values recognized by
Ora2Pg:
BOOLEAN_VALUES yes:no y:n 1:0 true:false enabled:disabled
Any values defined here will be added to the default list.
PostgreSQL Import
By default conversion to PostgreSQL format is written to file
'output.sql'. The command:
psql mydb < output.sql
will import content of file output.sql into PostgreSQL mydb database.
DATA_LIMIT
When you are performing INSERT/COPY export Ora2Pg proceed by chunks
of DATA_LIMIT tuples for speed improvement. Tuples are stored in
memory before being written to disk, so if you want speed and have
enough system resources you can grow this limit to an upper value
for example: 100000 or 1000000. Before release 7.0 a value of 0 mean
no limit so that all tuples are stored in memory before being
flushed to disk. In 7.x branch this has been remove and chunk will
be set to the default: 10000
OUTPUT
The Ora2Pg output filename can be changed with this directive.
Default value is output.sql. if you set the file name with extension
.gz or .bz2 the output will be automatically compressed. This
require that the Compress::Zlib Perl module is installed if the
filename extension is .gz and that the bzip2 system command is
installed for the .bz2 extension.
OUTPUT_DIR
Since release 7.0, you can define a base directory where wfile will
be written. The directory must exists.
BZIP2
This directive allow you to specify the full path to the bzip2
program if it can not be found in the PATH environment variable.
FILE_PER_CONSTRAINT
Allow object constraints to be saved in a separate file during
schema export. The file will be named CONSTRAINTS_OUTPUT, where
OUTPUT is the value of the corresponding configuration directive.
You can use .gz xor .bz2 extension to enable compression. Default is
to save all data in the OUTPUT file. This directive is usable only
with TABLE export type.
FILE_PER_INDEX
Allow indexes to be saved in a separate file during schema export.
The file will be named INDEXES_OUTPUT, where OUTPUT is the value of
the corresponding configuration directive. You can use .gz xor .bz2
file extension to enable compression. Default is to save all data in
the OUTPUT file. This directive is usable only with TABLE AND
TABLESPACE export type. With the TABLESPACE export, it is used to
write "ALTER INDEX ... TABLESPACE ..." into a separate file named
TBSP_INDEXES_OUPUT that can be loaded at end of the migration after
the indexes creation to move the indexes.
FILE_PER_TABLE
Allow data export to be saved in one file per table/view. The files
will be named as tablename_OUTPUT, where OUTPUT is the value of the
corresponding configuration directive. You can still use .gz xor
.bz2 extension in the OUTPUT directive to enable compression.
Default 0 will save all data in one file, set it to 1 to enable this
feature. This is usable only during INSERT or COPY export type.
FILE_PER_FUNCTION
Allow functions, procedures and triggers to be saved in one file per
object. The files will be named as objectname_OUTPUT. Where OUTPUT
is the value of the corresponding configuration directive. You can
still use .gz xor .bz2 extension in the OUTPUT directive to enable
compression. Default 0 will save all in one single file, set it to 1
to enable this feature. This is usable only during the corresponding
export type, the package body export has a special behavior.
When export type is PACKAGE and you've enabled this directive,
Ora2Pg will create a directory per package, named with the lower
case name of the package, and will create one file per
function/procedure into that directory. If the configuration
directive is not enabled, it will create one file per package as
packagename_OUTPUT, where OUTPUT is the value of the corresponding
directive.
TRUNCATE_TABLE
If this directive is set to 1, a TRUNCATE TABLE instruction will be
add before loading data. This is usable only during INSERT or COPY
export type.
If you want to import data on the fly to the PostgreSQL database you
have three configuration directives to set the PostgreSQL database
connection. This is only possible with COPY or INSERT export type as for
database schema there's no real interest to do that.
PG_DSN
Use this directive to set the PostgreSQL data source namespace using
DBD::Pg Perl module as follow:
dbi:Pg:dbname=pgdb;host=localhost;port=5432
will connect to database 'pgdb' on localhost at tcp port 5432.
PG_USER and PG_PWD
These two directives are used to set the login user and password.
Taking export under control
The following other configuration directives interact directly with the
export process and give you fine granuality in database export control.
SKIP
For TABLE export you may not want to export all schema constraints,
the SKIP configuration directive allow you to specify a space
separated list of constraints that should not be exported. Possible
values are:
- fkeys: turn off foreign key constraints
- pkeys: turn off primary keys
- ukeys: turn off unique column constraints
- indexes: turn off all other index types
- checks: turn off check constraints
For example:
SKIP indexes,checks
will removed indexes ans check constraints from export.
PKEY_IN_CREATE
Enable this directive if you want to add primary key definition
inside the create table statement. If disabled (the default) primary
key definition will be add with an alter table statement. Enable it
if you are exporting to GreenPlum PostgreSQL database.
KEEP_PKEY_NAMES
By default names of the primary key in the source Oracle database
are ignored and key names are created in the target PostgreSQL
database with the PostgreSQL internal default naming rules. If you
want to preserve Oracle primary key names set this option to 1.
FKEY_DEFERRABLE
When exporting tables, Ora2Pg normally exports constraints as they
are, if they are non-deferrable they are exported as non-deferrable.
However, non-deferrable constraints will probably cause problems
when attempting to import data to Pg. The FKEY_DEFERRABLE option set
to 1 will cause all foreign key constraints to be exported as
deferrable.
DEFER_FKEY
In addition, when exporting data the DEFER_FKEY option set to 1 will
add a command to defer all foreign key constraints during data
export. Constraints will then be checked at the end of each
transaction. Note that this will works only if foreign keys are
deferrable and that all data can stay in a single transaction.
DROP_FKEY
When this directive is enabled Ora2Pg forces the deletion of all
foreign keys before data import and to recreate them at end of the
data import.
DROP_INDEXES
This direction is also introduce since version 7.0 and allow you to
gain lot of speed improvement during data import by removing all
indexes that are not an automatic index (ex: indexes of primary
keys) and recreate them at the end of data import.
DISABLE_TRIGGERS
This directive is used to disable triggers on all tables in COPY or
INSERT export modes. Available values are iO, USER (disable
userdefined triggers only) and ALL (includes RI system triggers).
Default is 0: do not add SQL statements to disable trigger before
data import.
If you want to disable triggers during data migration, set the value
to USER if your are connected as non superuser and ALL if you are
connected as PostgreSQL superuser. A value of 1 is equal to USER.
DISABLE_SEQUENCE
If set to 1 disables alter of sequences on all tables during COPY or
INSERT export mode. This is used to prevent the update of sequence
during data migration. Default is 0, alter sequences.
NOESCAPE
By default all data that are not of type date or time are escaped.
If you experience any problem with that you can set it to 1 to
disable character escaping during data export. This directive is
only used during a COPY export. See STANDARD_CONFORMING_STRINGS for
enabling/disabling escape with INSERT statements.
STANDARD_CONFORMING_STRINGS
This controls whether ordinary string literals ('...') treat
backslashes literally, as specified in SQL standard. This was the
default before Ora2Pg v8.5 so that all strings was escaped first,
now this is currently on, causing Ora2Pg to use the escape string
syntax (E'...') if this parameter is not set to 0. This is the exact
behavior of the same option in PostgreSQL. This directive is only
used during data export to build INSERT statements. See NOESCAPE for
enabling/disabling escape in COPY statements.
PG_NUMERIC_TYPE
If set to 1 replace portable numeric type into PostgreSQL internal
type. Oracle data type NUMBER(p,s) is approximatively converted to
real and float PostgreSQL data type. If you have monetary fields or
don't want rounding issues with the extra decimals you should
preserve the same numeric(p,s) PostgreSQL data type. Do that only if
you need very good precision because using numeric(p,s) is slower
than using real or double.
PG_INTEGER_TYPE
If set to 1 replace portable numeric type into PostgreSQL internal
type. Oracle data type NUMBER(p) or NUMBER are converted to
smallint, integer or bigint PostgreSQL data type following the
length of the precision. If NUMBER without precision are set to
DEFAULT_NUMERIC (see bellow).
DEFAULT_NUMERIC
NUMBER without precision are converted by default to bigint only if
PG_INTEGER_TYPE is true. You can overwrite this value to any PG
type, like integer or float.
DATA_TYPE
If you're experiencing any problem in data type schema conversion
with this directive you can take full control of the correspondence
between Oracle and PostgreSQL types to redefine data type
translation used in Ora2pg. The syntax is a coma separated list of
"Oracle datatype:Postgresql datatype". Here are the default list
used:
DATA_TYPE DATE:timestamp,LONG:text,LONG RAW:bytea,CLOB:text,NCLOB:text,BLOB:bytea,BFILE:bytea,RAW:bytea,ROWID:oid,FLOAT:double precision,DEC:decimal,DECIMAL:decimal,DOUBLE PRECISION:double precision,INT:integer,INTEGER:integer,REAL:real,SMALLINT:smallint,BINARY_FLOAT:double precision,BINARY_DOUBLE:double precision,TIMESTAMP:timestamp,XMLTYPE:xml,BINARY_INTEGER:integer,PLS_INTEGER:integer,TIMESTAMP WITH TIME ZONE:timestamp with time zone,TIMESTAMP WITH LOCAL TIME ZONE:timestamp with time zone
Note that the directive and the list definition must be a single
line.
There's a special case with BFILE when they are converted to text
field, they will contains the path to the external file. If you set
the destination type to bytea, the default, Ora2Pg will export the
BFILE as bytea.
There's no SQL function available to retrieve the path to the BFILE,
then Ora2Pg have to create one using the DBMS_LOB package.
CREATE OR REPLACE FUNCTION ora2pg_get_bfilename( p_bfile IN BFILE )
RETURN VARCHAR2
AS
l_dir VARCHAR2(4000);
l_fname VARCHAR2(4000);
l_path VARCHAR2(4000);
BEGIN
dbms_lob.FILEGETNAME( p_bfile, l_dir, l_fname );
SELECT directory_path INTO l_path FROM all_directories
WHERE directory_name = l_dir;
l_dir := rtrim(l_path,'/');
RETURN l_dir || '/' || l_fname;