[root@localhost /usr/local]$ tar -xzvf DBI-1.643.tar.gz
[root@localhost /usr/local/DBI-1.643]$ cd DBI-1.643/
[root@localhost /usr/local/DBI-1.643]$ perl Makefile.PL
[root@localhost /usr/local/DBI-1.643]$ make
[root@localhost /usr/local/DBI-1.643]$ make install
# tar -xvf ora2pg-22.1.tar.bz2
# cd ora2pg-22.1/
# ls
changelog doc INSTALL lib LICENSE Makefile.PL MANIFEST packaging README scripts
# perl Makefile.PL
# make
# make install
Installing default configuration file (ora2pg.conf.dist) to /etc/ora2pg
#
1.7 查看软件是否安装成功
# cat check.pl
#!/usr/bin/perl
use strict;
use ExtUtils::Installed;
my $inst=ExtUtils::Installed->new();
my @modules = $inst->modules();
foreach(@modules){
my $ver = $inst->version($_) || "???";
printf("%-12s -- %s\n",$_,$ver);
}
exit;
[root@pgserver sztech]# ora2pg -t SHOW_VERSION -c ora2pg_oracle.conf
WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11
[========================>] 10/10 tables (100.0%) end of scanning.
Retrieving table partitioning information...
[========================>] 10/10 tables (100.0%) end of table export.
Fixing function calls in output files...
WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11
[========================>] 10/10 tables (100.0%) end of scanning.
[========================>] 25/25 rows (100.0%) Table COUNTRIES (25 recs/sec)
[====> ] 25/131 total rows (19.1%) - (0 sec., avg: 25 recs/sec).
[========================>] 8/8 rows (100.0%) Table DEPARTMENTS (8 recs/sec)
[======> ] 33/131 total rows (25.2%) - (0 sec., avg: 33 recs/sec).
[========================>] 20/20 rows (100.0%) Table EMPLOYEES (20 recs/sec)
[=========> ] 53/131 total rows (40.5%) - (0 sec., avg: 53 recs/sec).
[========================>] 3/1 rows (300.0%) Table FLIGHTS (3 recs/sec)
[==========> ] 56/131 total rows (42.7%) - (0 sec., avg: 56 recs/sec).
[========================>] 19/19 rows (100.0%) Table JOBS (19 recs/sec)
[=============> ] 75/131 total rows (57.3%) - (0 sec., avg: 75 recs/sec).
[========================>] 6/6 rows (100.0%) Table JOB_GRADES (6 recs/sec)
[==============> ] 81/131 total rows (61.8%) - (0 sec., avg: 81 recs/sec).
[========================>] 10/10 rows (100.0%) Table JOB_HISTORY (10 recs/sec)
[================> ] 91/131 total rows (69.5%) - (0 sec., avg: 91 recs/sec).
[========================>] 23/23 rows (100.0%) Table LOCATIONS (23 recs/sec)
[====================> ] 114/131 total rows (87.0%) - (0 sec., avg: 114 recs/sec).
[========================>] 4/4 rows (100.0%) Table REGIONS (4 recs/sec)
[=====================> ] 118/131 total rows (90.1%) - (0 sec., avg: 118 recs/sec).
[========================>] 15/15 rows (100.0%) Table RETIRED_EMPLOYEES (15 recs/sec)
[========================>] 133/131 total rows (101.5%) - (0 sec., avg: 133 recs/sec).
[========================>] 131/131 rows (100.0%) on total estimated data (1 sec., avg: 131 recs/sec)
Fixing function calls in output files...
查看导出的sql
[root@pgserver sztech]# cat hr.sql
-- Generated by Ora2Pg, the Oracle database Schema converter, version 22.1
-- Copyright 2000-2021 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=192.168.133.130;sid=emrep;port=1521
SET client_encoding TO 'UTF8';
\set ON_ERROR_STOP ON
SET check_function_bodies = false;
CREATE TABLE countries (
country_id char(2) NOT NULL,
country_name varchar(40),
region_id bigint
) ;
COMMENT ON TABLE countries IS E'country table. Contains 25 rows. References with locations table.';
COMMENT ON COLUMN countries.country_id IS E'Primary key of countries table.';
COMMENT ON COLUMN countries.country_name IS E'Country name';
COMMENT ON COLUMN countries.region_id IS E'Region ID for the country. Foreign key to region_id column in the departments table.';
CREATE TABLE departments (
department_id smallint NOT NULL,
department_name varchar(30) NOT NULL,
manager_id integer,
location_id smallint
) ;
COMMENT ON TABLE departments IS E'Departments table that shows details of departments where employees
work. Contains 27 rows; references with locations, employees, and job_history tables.';
COMMENT ON COLUMN departments.department_id IS E'Primary key column of departments table.';
COMMENT ON COLUMN departments.department_name IS E'A not null column that shows name of a department. Administration,
Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
Relations, Sales, Finance, and Accounting. ';
COMMENT ON COLUMN departments.location_id IS E'Location id where a department is located. Foreign key to location_id column of locations table.';
COMMENT ON COLUMN departments.manager_id IS E'Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.';
CREATE TABLE employees (
employee_id integer NOT NULL,
first_name varchar(20),
last_name varchar(25) NOT NULL,
email varchar(25) NOT NULL,
phone_number varchar(20),
hire_date timestamp NOT NULL,
job_id varchar(10) NOT NULL,
salary double precision,
commission_pct real,
manager_id integer,
department_id smallint
) ;
COMMENT ON TABLE employees IS E'employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.';
COMMENT ON COLUMN employees.commission_pct IS E'Commission percentage of the employee; Only employees in sales
department elgible for commission percentage';
COMMENT ON COLUMN employees.department_id IS E'Department id where employee works; foreign key to department_id
column of the departments table';
COMMENT ON COLUMN employees.email IS E'Email id of the employee';
COMMENT ON COLUMN employees.employee_id IS E'Primary key of employees table.';
COMMENT ON COLUMN employees.first_name IS E'First name of the employee. A not null column.';
COMMENT ON COLUMN employees.hire_date IS E'Date when the employee started on this job. A not null column.';
COMMENT ON COLUMN employees.job_id IS E'Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.';
COMMENT ON COLUMN employees.last_name IS E'Last name of the employee. A not null column.';
COMMENT ON COLUMN employees.manager_id IS E'Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)';
COMMENT ON COLUMN employees.phone_number IS E'Phone number of the employee; includes country code and area code';
COMMENT ON COLUMN employees.salary IS E'Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)';
CREATE TABLE jobs (
job_id varchar(10) NOT NULL,
job_title varchar(35) NOT NULL,
min_salary integer,
max_salary integer
) ;
COMMENT ON TABLE jobs IS E'jobs table with job titles and salary ranges. Contains 19 rows.
References with employees and job_history table.';
COMMENT ON COLUMN jobs.job_id IS E'Primary key of jobs table.';
COMMENT ON COLUMN jobs.job_title IS E'A not null column that shows job title, e.g. AD_VP, FI_ACCOUNTANT';
COMMENT ON COLUMN jobs.max_salary IS E'Maximum salary for a job title';
COMMENT ON COLUMN jobs.min_salary IS E'Minimum salary for a job title.';
CREATE TABLE job_history (
employee_id integer NOT NULL,
start_date timestamp NOT NULL,
end_date timestamp NOT NULL,
job_id varchar(10) NOT NULL,
department_id smallint
) ;
COMMENT ON TABLE job_history IS E'Table that stores job history of the employees. If an employee
changes departments within the job or changes jobs within the department,
new rows get inserted into this table with old job information of the
employee. Contains a complex primary key: employee_id+start_date.
Contains 25 rows. References with jobs, employees, and departments tables.';
COMMENT ON COLUMN job_history.department_id IS E'Department id in which the employee worked in the past; foreign key to deparment_id column in the departments table';
COMMENT ON COLUMN job_history.employee_id IS E'A not null column in the complex primary key employee_id+start_date.
Foreign key to employee_id column of the employee table';
COMMENT ON COLUMN job_history.end_date IS E'Last day of the employee in this job role. A not null column. Must be
greater than the start_date of the job_history table.
(enforced by constraint jhist_date_interval)';
COMMENT ON COLUMN job_history.job_id IS E'Job role in which the employee worked in the past; foreign key to
job_id column in the jobs table. A not null column.';
COMMENT ON COLUMN job_history.start_date IS E'A not null column in the complex primary key employee_id+start_date.
Must be less than the end_date of the job_history table. (enforced by
constraint jhist_date_interval)';
CREATE TABLE locations (
location_id smallint NOT NULL,
street_address varchar(40),
postal_code varchar(12),
city varchar(30) NOT NULL,
state_province varchar(25),
country_id char(2)
) ;
COMMENT ON TABLE locations IS E'Locations table that contains specific address of a specific office,
warehouse, and/or production site of a company. Does not store addresses /
locations of customers. Contains 23 rows; references with the
departments and countries tables. ';
COMMENT ON COLUMN locations.city IS E'A not null column that shows city where an office, warehouse, or
production site of a company is located. ';
COMMENT ON COLUMN locations.country_id IS E'Country where an office, warehouse, or production site of a company is
located. Foreign key to country_id column of the countries table.';
COMMENT ON COLUMN locations.location_id IS E'Primary key of locations table';
COMMENT ON COLUMN locations.postal_code IS E'Postal code of the location of an office, warehouse, or production site
of a company. ';
COMMENT ON COLUMN locations.state_province IS E'State or Province where an office, warehouse, or production site of a
company is located.';
COMMENT ON COLUMN locations.street_address IS E'Street address of an office, warehouse, or production site of a company.
Contains building number and street name';
CREATE TABLE regions (
region_id bigint NOT NULL,
region_name varchar(25)
) ;
COPY countries (country_id,country_name,region_id) FROM STDIN;
AR Argentina 2
AU Australia 3
BE Belgium 1
BR Brazil 2
CA Canada 2
CH Switzerland 1
CN China 3
DE Germany 1
DK Denmark 1
EG Egypt 4
FR France 1
IL Israel 4
IN India 3
IT Italy 1
JP Japan 3
KW Kuwait 4
ML Malaysia 3
MX Mexico 2
NG Nigeria 4
NL Netherlands 1
SG Singapore 3
UK United Kingdom 1
US United States of America 2
ZM Zambia 4
ZW Zimbabwe 4
\.
COPY locations (location_id,street_address,postal_code,city,state_province,country_id) FROM STDIN;
1000 1297 Via Cola di Rie 00989 Roma \N IT
1100 93091 Calle della Testa 10934 Venice \N IT
1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefecture JP
1300 9450 Kamiya-cho 6823 Hiroshima \N JP
1400 2014 Jabberwocky Rd 26192 Southlake Texas US
1500 2011 Interiors Blvd 99236 South San Francisco California US
1600 2007 Zagora St 50090 South Brunswick New Jersey US
1700 2004 Charade Rd 98199 Seattle Washington US
1800 147 Spadina Ave M5V 2L7 Toronto Ontario CA
1900 6092 Boxwood St YSW 9T2 Whitehorse Yukon CA
2000 40-5-12 Laogianggen 190518 Beijing \N CN
2100 1298 Vileparle (E) 490231 Bombay Maharashtra IN
2200 12-98 Victoria Street 2901 Sydney New South Wales AU
2300 198 Clementi North 540198 Singapore \N SG
2400 8204 Arthur St \N London \N UK
2500 Magdalen Centre, The Oxford Science Park OX9 9ZB Oxford Oxford UK
2600 9702 Chester Road 09629850293 Stretford Manchester UK
2700 Schwanthalerstr. 7031 80925 Munich Bavaria DE
2800 Rua Frei Caneca 1360 01307-002 Sao Paulo Sao Paulo BR
2900 20 Rue des Corps-Saints 1730 Geneva Geneve CH
3000 Murtenstrasse 921 3095 Bern BE CH
3100 Pieter Breughelstraat 837 3029SK Utrecht Utrecht NL
3200 Mariano Escobedo 9991 11932 Mexico City Distrito Federal, MX
\.
COPY regions (region_id,region_name) FROM STDIN;
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
\.
ALTER SEQUENCE IF EXISTS locations_seq RESTART WITH 3400;
ALTER SEQUENCE IF EXISTS departments_seq RESTART WITH 290;
ALTER SEQUENCE IF EXISTS employees_seq RESTART WITH 208;
COMMIT;
[root@pgserver sztech]#
2.1.3 导入到pg
su - postgres
$ psql postgres postgres -f hr_table.sql
Ora2Pg - Database Migration Report
Version Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Schema HR
Size 1.75 MB
Object Number Invalid Estimated cost Comments Details
DATABASE LINK 0 0 0.00 Database links will be exported as SQL/MED PostgreSQL's Foreign Data Wrapper (FDW) extensions using oracle_fdw.
GLOBAL TEMPORARY TABLE 0 0 0.00 Global temporary table are not supported by PostgreSQL and will not be exported. You will have to rewrite some application code to match the PostgreSQL temporary table behavior.
INDEX 19 0 3.80 19 index(es) are concerned by the export, others are automatically generated and will do so on PostgreSQL. Bitmap will be exported as btree_gin index(es). Domain index are exported as b-tree but commented to be edited to mainly use FTS. Cluster, 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. 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.
See details
JOB 0 0 0.00 Job are not exported. You may set external cron job with them.
PROCEDURE 2 0 8.00 Total size of procedure code: 772 bytes.
See details
SEQUENCE 3 0 1.00 Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL will be transformed into NEXTVAL('sequence_name') or CURRVAL('sequence_name').
SYNONYM 0 0 0.00 SYNONYMs will be exported as views. SYNONYMs do not exists with PostgreSQL but a common workaround is to use views or set the PostgreSQL search_path in your session to access object outside the current schema.
TABLE 10 0 2.50 15 check constraint(s).
See details
TRIGGER 2 0 5.00 Total size of trigger code: 127 bytes.
See details
Total 36 0 20.30 20.30 cost migration units means approximatively 1 man-day(s). The migration unit was set to 5 minute(s)
Migration level: A-3
Migration levels:
A - Migration that might be run automatically
B - Migration with code rewrite and a human-days cost up to 5 days
C - Migration with code rewrite and a human-days cost above 5 days
Technical levels:
1 = trivial: no stored functions and no triggers
2 = easy: no stored functions but with triggers, no manual rewriting
3 = simple: stored functions and/or triggers, no manual rewriting
4 = manual: no stored functions but with triggers or views with code rewriting
5 = difficult: stored functions and/or triggers with code rewriting
Details of cost assessment per function