本文只介绍如何使用Ora2Pg从Oracle导出数据到Postgresql,但是在操作前需要先安装先决软件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;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
------------------------------------------------------------------------------------------------------------------------------
7369SMITHCLERK79021980-12-1700:00:0080020
7499ALLENSALESMAN76981981-02-2000:00:00160030030
7521WARDSALESMAN76981981-02-2200:00:00125050030
7566JONESMANAGER78391981-04-0200:00:00297520
7654MARTINSALESMAN76981981-09-2800:00:001250140030
7698BLAKEMANAGER78391981-05-0100:00:00285030
7782CLARKMANAGER78391981-06-0900:00:00245010
7788SCOTTANALYST75661987-04-1900:00:00300020
7839KINGPRESIDENT1981-11-1700:00:00500010
7844TURNERSALESMAN76981981-09-0800:00:001500030
7876ADAMSCLERK77881987-05-2300:00:00110020
7900JAMESCLERK76981981-12-0300:00:0095030
7902FORDANALYST75661981-12-0300:00:00300020
7934MILLERCLERK77821982-01-2300:00:00130010
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_DSNdbi:Oracle:host=192.168.56.2;sid=orcl
ORACLE_USERzhaoxu
ORACLE_PWDzhaoxu
SCHEMAzhaoxu
USER_GRANTS0
DEBUG0
ORA_INITIAL_COMMAND
EXPORT_SCHEMA0
CREATE_SCHEMA1
COMPILE_SCHEMA0
TYPETABLE,INSERT
OUTPUToutput.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
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)
数据导入完成。
总结
如果觉得编程之家网站内容还不错,欢迎将编程之家网站推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。