使用Ora2Pg工具把数据从Oracle导入到PostgreSQL

本文只介绍如何使用Ora2Pg从Oracle导出数据到Postgresql，但是在操作前需要先安装先决软件DBD::Oracle、DBI、Ora2Pg。

安装参考：Linux下安装DBD::Oracle、DBI和Ora2Pg

安装Ora2Pg完成会在/etc目录下生成一个ora2pg目录里面有使用Ora2Pg的配置文件。

1、在Oracle上创建测试用户并创建测试表

sys@ORCL>createuserzhaoxuidentifiedbyzhaoxu;
 
Usercreated.
 
sys@ORCL>grantdbatozhaoxu;
 
Grantsucceeded.
 
sys@ORCL>connzhaoxu/zhaoxu;
Connected.
 
zhaoxu@ORCL>createtableempasselect*fromscott.emp;
 
Tablecreated.
 
zhaoxu@ORCL>select*fromemp;
 
EMPNOENAME			JOB				MGRHIREDATE		SALCOMMDEPTNO
------------------------------------------------------------------------------------------------------------------------------
7369SMITH			CLERK			79021980-12-1700:00:00	800		20
7499ALLEN			SALESMAN			76981981-02-2000:00:00	1600300	30
7521WARD			SALESMAN			76981981-02-2200:00:00	1250500	30
7566JONES			MANAGER			78391981-04-0200:00:00	2975		20
7654MARTIN			SALESMAN			76981981-09-2800:00:00	12501400	30
7698BLAKE			MANAGER			78391981-05-0100:00:00	2850		30
7782CLARK			MANAGER			78391981-06-0900:00:00	2450		10
7788SCOTT			ANALYST			75661987-04-1900:00:00	3000		20
7839KING			PRESIDENT				1981-11-1700:00:00	5000		10
7844TURNER			SALESMAN			76981981-09-0800:00:00	1500	0	30
7876ADAMS			CLERK			77881987-05-2300:00:00	1100		20
7900JAMES			CLERK			76981981-12-0300:00:00	950		30
7902FORD			ANALYST			75661981-12-0300:00:00	3000		20
7934MILLER			CLERK			77821982-01-2300:00:00	1300		10
 
14rowsselected.

2、修改参数文件

[oracle@rhel6ora2pg]$cp/etc/ora2pg/ora2pg.conf/home/oracle/ora2pg/
[oracle@rhel6ora2pg]$cd/home/oracle/ora2pg/
[oracle@rhel6ora2pg]$viora2pg.conf
[oracle@rhel6ora2pg]$catora2pg.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
SCHEMAzhaoxu
USER_GRANTS0
DEBUG		0
ORA_INITIAL_COMMAND
EXPORT_SCHEMA	0
CREATE_SCHEMA	1
COMPILE_SCHEMA	0
TYPE		TABLE,INSERT
OUTPUT		output.sql

3、使用上面修改的参数导出数据

[oracle@rhel6ora2pg]$ora2pg-cora2pg.conf
[========================>]1/1tables(100.0%)endofscanning.
[>]0/1tables(0.0%)endofscanning.
[========================>]1/1tables(100.0%)endoftableexport.
[========================>]14/1rows(1400.0%)TableEMP(14recs/sec)
[========================>]14/1totalrows(1400.0%)-(0sec.,avg:14recs/sec).
[========================>]1/1rows(100.0%)ontotalestimateddata(1sec.,avg:1recs/sec)
[oracle@rhel6ora2pg]$catoutput.sql
--GeneratedbyOra2Pg,theOracledatabaseSchemaconverter,version17.6b
--Copyright2000-2016GillesDAROLD.Allrightsreserved.
--DATASOURCE:dbi:Oracle:host=192.168.56.2;sid=orcl
 
SETclient_encodingTO'UTF8';
 
\setON_ERROR_STOPON
 
 
CREATETABLEemp(
	empnosmallint,enamevarchar(10),jobvarchar(9),mgrsmallint,hiredatetimestamp,saldecimal(7,2),commdecimal(7,deptnosmallint
);
--GeneratedbyOra2Pg,version17.6b
--Copyright2000-2016GillesDAROLD.Allrightsreserved.
--DATASOURCE:dbi:Oracle:host=192.168.56.2;sid=orcl
 
SETclient_encodingTO'UTF8';
 
\setON_ERROR_STOPON
 
 
BEGIN;
INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7369,E'SMITH',E'CLERK',7902,'1980-12-1700:00:00',800,NULL,20);
INSERTINTOemp(empno,deptno)VALUES(7499,E'ALLEN',E'SALESMAN',7698,'1981-02-2000:00:00',1600,300,30);
INSERTINTOemp(empno,deptno)VALUES(7521,E'WARD','1981-02-2200:00:00',1250,500,deptno)VALUES(7566,E'JONES',E'MANAGER',7839,'1981-04-0200:00:00',2975,deptno)VALUES(7654,E'MARTIN','1981-09-2800:00:00',1400,deptno)VALUES(7698,E'BLAKE','1981-05-0100:00:00',2850,deptno)VALUES(7782,E'CLARK','1981-06-0900:00:00',2450,10);
INSERTINTOemp(empno,deptno)VALUES(7788,E'SCOTT',E'ANALYST',7566,'1987-04-1900:00:00',3000,deptno)VALUES(7839,E'KING',E'PRESIDENT','1981-11-1700:00:00',5000,deptno)VALUES(7844,E'TURNER','1981-09-0800:00:00',1500,deptno)VALUES(7876,E'ADAMS',7788,'1987-05-2300:00:00',1100,deptno)VALUES(7900,E'JAMES','1981-12-0300:00:00',950,deptno)VALUES(7902,E'FORD',deptno)VALUES(7934,E'MILLER',7782,'1982-01-2300:00:00',1300,10);
 
COMMIT;

4、把生成的output.sql传到Postgresql服务器上

[oracle@rhel6ora2pg]$scpoutput.sqlpguser@192.168.56.25:/home/pguser/
pguser@192.168.56.25'spassword:
output.sql100%25992.5KB/s00:00

5、在Postgresql 数据库上创建对应的数据库、用户和Schema

#创建数据库zhaoxu
postgres=#createdatabasezhaoxu;
CREATEDATABASE
postgres=#\l
Listofdatabases
Name|Owner|Encoding|Collate|Ctype|Accessprivileges
-----------+--------+----------+-------------+-------------+-------------------
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|
(5rows)
 
#创建用户zhaoxu
postgres=#createuserzhaoxusuperuser;
CREATEROLE
postgres=#\dg
Listofroles
Rolename|Attributes|Memberof
-----------+------------------------------------------------------------+-----------
lx|Superuser,Cannotlogin|{}
pguser|Superuser,Createrole,CreateDB,Replication,BypassRLS|{}
sq|Superuser,CreateDB|{}
zhaoxu|Superuser|{}
zx|Superuser|{}
 
#在zhaoxu库下创建Schemazhaoxu
postgres=#\czhaoxuzhaoxu
Youarenowconnectedtodatabase"zhaoxu"asuser"zhaoxu".
zhaoxu=#createschemazhaoxu;
CREATESCHEMA
zhaoxu=#\dn
Listofschemas
Name|Owner
--------+--------
public|pguser
zhaoxu|zhaoxu
(2rows)

6、使用output.sql导入从Oracle导出的数据

[pguser@rhel7~]$psqlzhaoxuzhaoxu<output.sql
SET
CREATETABLE
SET
BEGIN
INSERT01
INSERT01
INSERT01
INSERT01
INSERT01
INSERT01
INSERT01
INSERT01
INSERT01
INSERT01
INSERT01
INSERT01
INSERT01
INSERT01
COMMIT
[pguser@rhel7~]$psqlzhaoxuzhaoxu
psql(9.6.1)
Type"help"forhelp.
 
zhaoxu=#\d
Listofrelations
Schema|Name|Type|Owner
--------+------+-------+--------
zhaoxu|emp|table|zhaoxu
(1row)
 
zhaoxu=#select*fromemp;
empno|ename|job|mgr|hiredate|sal|comm|deptno
-------+--------+-----------+------+---------------------+---------+---------+--------
7369|SMITH|CLERK|7902|1980-12-1700:00:00|800.00||20
7499|ALLEN|SALESMAN|7698|1981-02-2000:00:00|1600.00|300.00|30
7521|WARD|SALESMAN|7698|1981-02-2200:00:00|1250.00|500.00|30
7566|JONES|MANAGER|7839|1981-04-0200:00:00|2975.00||20
7654|MARTIN|SALESMAN|7698|1981-09-2800:00:00|1250.00|1400.00|30
7698|BLAKE|MANAGER|7839|1981-05-0100:00:00|2850.00||30
7782|CLARK|MANAGER|7839|1981-06-0900:00:00|2450.00||10
7788|SCOTT|ANALYST|7566|1987-04-1900:00:00|3000.00||20
7839|KING|PRESIDENT||1981-11-1700:00:00|5000.00||10
7844|TURNER|SALESMAN|7698|1981-09-0800:00:00|1500.00|0.00|30
7876|ADAMS|CLERK|7788|1987-05-2300:00:00|1100.00||20
7900|JAMES|CLERK|7698|1981-12-0300:00:00|950.00||30
7902|FORD|ANALYST|7566|1981-12-0300:00:00|3000.00||20
7934|MILLER|CLERK|7782|1982-01-2300:00:00|1300.00||10
(14rows)

数据导入完成。

使用Ora2Pg工具把数据从Oracle导入到PostgreSQL

猜你在找的Oracle相关文章