本文只介绍如何使用Ora2Pg从Oracle导出数据到PostgreSQL，但是在操作前需要先安装先决软件DBD::Oracle、DBI、Ora2Pg。
安装参考：Linux下安装DBD::Oracle、DBI和Ora2Pg
安装Ora2Pg完成会在/etc目录下生成一个ora2pg目录里面有使用Ora2Pg的配置文件。
1、在Oracle上创建测试用户并创建测试表
sys@ORCL>create user zhaoxu identified by zhaoxu;
User created.
sys@ORCL>grant dba to zhaoxu;
Grant succeeded.
sys@ORCL>conn zhaoxu/zhaoxu;
Connected.
zhaoxu@ORCL>create table emp as select * from scott.emp;
Table created.
zhaoxu@ORCL>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.2、修改参数文件
[oracle@rhel6 ora2pg]$ cp /etc/ora2pg/ora2pg.conf /home/oracle/ora2pg/
[oracle@rhel6 ora2pg]$ cd /home/oracle/ora2pg/
[oracle@rhel6 ora2pg]$ vi ora2pg.conf
[oracle@rhel6 ora2pg]$ cat ora2pg.conf
ORACLE_HOME /u02/app/oracle/product/11.2.4/db1
ORACLE_DSN dbi:Oracle:host=192.168.56.2;sid=orcl
ORACLE_USER zhaoxu
ORACLE_PWD zhaoxu
SCHEMA zhaoxu
USER_GRANTS 0
DEBUG 0
ORA_INITIAL_COMMAND
EXPORT_SCHEMA 0
CREATE_SCHEMA 1
COMPILE_SCHEMA 0
TYPE TABLE,INSERT
OUTPUT output.sql3、使用上面修改的参数导出数据
[oracle@rhel6 ora2pg]$ ora2pg -c ora2pg.conf
[========================>] 1/1 tables (100.0%) end of scanning.
[> ] 0/1 tables (0.0%) end of scanning.
[========================>] 1/1 tables (100.0%) end of table export.
[========================>] 14/1 rows (1400.0%) Table EMP (14 recs/sec)
[========================>] 14/1 total rows (1400.0%) - (0 sec., avg: 14 recs/sec).
[========================>] 1/1 rows (100.0%) on total estimated data (1 sec., avg: 1 recs/sec)
[oracle@rhel6 ora2pg]$ cat output.sql
-- Generated by Ora2Pg, the Oracle database Schema converter, version 17.6b
-- Copyright 2000-2016 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=192.168.56.2;sid=orcl
SET client_encoding TO 'UTF8';
\set ON_ERROR_STOP ON
CREATE TABLE emp (
empno smallint,
ename varchar(10),
job varchar(9),
mgr smallint,
hiredate timestamp,
sal decimal(7,2),
comm decimal(7,2),
deptno smallint
) ;
-- Generated by Ora2Pg, the Oracle database Schema converter, version 17.6b
-- Copyright 2000-2016 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=192.168.56.2;sid=orcl
SET client_encoding TO 'UTF8';
\set ON_ERROR_STOP ON
BEGIN;
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7369,E'SMITH',E'CLERK',7902,'1980-12-17 00:00:00',800,NULL,20);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,E'ALLEN',E'SALESMAN',7698,'1981-02-20 00:00:00',1600,300,30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7521,E'WARD',E'SALESMAN',7698,'1981-02-22 00:00:00',1250,500,30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7566,E'JONES',E'MANAGER',7839,'1981-04-02 00:00:00',2975,NULL,20);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7654,E'MARTIN',E'SALESMAN',7698,'1981-09-28 00:00:00',1250,1400,30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7698,E'BLAKE',E'MANAGER',7839,'1981-05-01 00:00:00',2850,NULL,30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7782,E'CLARK',E'MANAGER',7839,'1981-06-09 00:00:00',2450,NULL,10);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7788,E'SCOTT',E'ANALYST',7566,'1987-04-19 00:00:00',3000,NULL,20);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7839,E'KING',E'PRESIDENT',NULL,'1981-11-17 00:00:00',5000,NULL,10);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7844,E'TURNER',E'SALESMAN',7698,'1981-09-08 00:00:00',1500,0,30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7876,E'ADAMS',E'CLERK',7788,'1987-05-23 00:00:00',1100,NULL,20);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7900,E'JAMES',E'CLERK',7698,'1981-12-03 00:00:00',950,NULL,30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7902,E'FORD',E'ANALYST',7566,'1981-12-03 00:00:00',3000,NULL,20);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7934,E'MILLER',E'CLERK',7782,'1982-01-23 00:00:00',1300,NULL,10);
COMMIT;4、把生成的output.sql传到PostgreSQL服务器上
[oracle@rhel6 ora2pg]$ scp output.sql pguser@192.168.56.25:/home/pguser/
pguser@192.168.56.25's password:
output.sql 100% 2599 2.5KB/s 00:005、在PostgreSQL数据库上创建对应的数据库、用户和Schema
#创建数据库 zhaoxu
postgres=# create database zhaoxu;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+--------+----------+-------------+-------------+-------------------
postgres | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/pguser +
| | | | | pguser=CTc/pguser
template1 | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/pguser +
| | | | | pguser=CTc/pguser
zhaoxu | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
zx | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
#创建用户 zhaoxu
postgres=# create user zhaoxu superuser;
CREATE ROLE
postgres=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
lx | Superuser, Cannot login | {}
pguser | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
sq | Superuser, Create role, Create DB | {}
zhaoxu | Superuser | {}
zx | Superuser | {}
#在zhaoxu库下创建Schema zhaoxu
postgres=# \c zhaoxu zhaoxu
You are now connected to database "zhaoxu" as user "zhaoxu".
zhaoxu=# create schema zhaoxu;
CREATE SCHEMA
zhaoxu=# \dn
List of schemas
Name | Owner
--------+--------
public | pguser
zhaoxu | zhaoxu
(2 rows)6、使用output.sql导入从Oracle导出的数据
[pguser@rhel7 ~]$ psql zhaoxu zhaoxu < output.sql
SET
CREATE TABLE
SET
BEGIN
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
COMMIT
[pguser@rhel7 ~]$ psql zhaoxu zhaoxu
psql (9.6.1)
Type "help" for help.
zhaoxu=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+--------
zhaoxu | emp | table | zhaoxu
(1 row)
zhaoxu=# select * from emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+---------------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | | 10
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | | 20
7839 | KING | PRESIDENT | | 1981-11-17 00:00:00 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | | 10
(14 rows)数据导入完成。
