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

2025-04-12 技术教程

本文只介绍如何使用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;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO------------------------------------------------------------------------------------------------------------------------------7369SMITHCLERK79021980-12-1700:00:00800207499ALLENSALESMAN76981981-02-2000:00:001600300307521WARDSALESMAN76981981-02-2200:00:001250500307566JONESMANAGER78391981-04-0200:00:002975207654MARTINSALESMAN76981981-09-2800:00:0012501400307698BLAKEMANAGER78391981-05-0100:00:002850307782CLARKMANAGER78391981-06-0900:00:002450107788SCOTTANALYST75661987-04-1900:00:003000207839KINGPRESIDENT1981-11-1700:00:005000107844TURNERSALESMAN76981981-09-0800:00:0015000307876ADAMSCLERK77881987-05-2300:00:001100207900JAMESCLERK76981981-12-0300:00:00950307902FORDANALYST75661981-12-0300:00:003000207934MILLERCLERK77821982-01-2300:00:0013001014rowsselected.

2、修改参数文件

[oracle@rhel6ora2pg]$cp/etc/ora2pg/ora2pg.conf/home/oracle/ora2pg/[oracle@rhel6ora2pg]$cd/home/oracle/ora2pg/[oracle@rhel6ora2pg]$viora2pg.conf[oracle@rhel6ora2pg]$catora2pg.confORACLE_HOME/u02/app/oracle/product/11.2.4/db1ORACLE_DSNdbi:Oracle:host=192.168.56.2;sid=orclORACLE_USERzhaoxuORACLE_PWDzhaoxuSCHEMAzhaoxuUSER_GRANTS0DEBUG0ORA_INITIAL_COMMANDEXPORT_SCHEMA0CREATE_SCHEMA1COMPILE_SCHEMA0TYPETABLE,INSERTOUTPUToutput.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=orclSETclient_encodingTO'UTF8';\setON_ERROR_STOPONCREATETABLEemp(empnosmallint,enamevarchar(10),jobvarchar(9),mgrsmallint,hiredatetimestamp,saldecimal(7,2),commdecimal(7,2),deptnosmallint);--GeneratedbyOra2Pg,theOracledatabaseSchemaconverter,version17.6b--Copyright2000-2016GillesDAROLD.Allrightsreserved.--DATASOURCE:dbi:Oracle:host=192.168.56.2;sid=orclSETclient_encodingTO'UTF8';\setON_ERROR_STOPONBEGIN;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,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7499,E'ALLEN',E'SALESMAN',7698,'1981-02-2000:00:00',1600,300,30);INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7521,E'WARD',E'SALESMAN',7698,'1981-02-2200:00:00',1250,500,30);INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7566,E'JONES',E'MANAGER',7839,'1981-04-0200:00:00',2975,NULL,20);INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7654,E'MARTIN',E'SALESMAN',7698,'1981-09-2800:00:00',1250,1400,30);INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7698,E'BLAKE',E'MANAGER',7839,'1981-05-0100:00:00',2850,NULL,30);INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7782,E'CLARK',E'MANAGER',7839,'1981-06-0900:00:00',2450,NULL,10);INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7788,E'SCOTT',E'ANALYST',7566,'1987-04-1900:00:00',3000,NULL,20);INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7839,E'KING',E'PRESIDENT',NULL,'1981-11-1700:00:00',5000,NULL,10);INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7844,E'TURNER',E'SALESMAN',7698,'1981-09-0800:00:00',1500,0,30);INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7876,E'ADAMS',E'CLERK',7788,'1987-05-2300:00:00',1100,NULL,20);INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7900,E'JAMES',E'CLERK',7698,'1981-12-0300:00:00',950,NULL,30);INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7902,E'FORD',E'ANALYST',7566,'1981-12-0300:00:00',3000,NULL,20);INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7934,E'MILLER',E'CLERK',7782,'1982-01-2300:00:00',1300,NULL,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

#创建数据库zhaoxupostgres=#createdatabasezhaoxu;CREATEDATABASEpostgres=#\lListofdatabasesName|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/pgusertemplate1|pguser|UTF8|en_US.UTF-8|en_US.UTF-8|=c/pguser+|||||pguser=CTc/pguserzhaoxu|pguser|UTF8|en_US.UTF-8|en_US.UTF-8|zx|pguser|UTF8|en_US.UTF-8|en_US.UTF-8|(5rows)#创建用户zhaoxupostgres=#createuserzhaoxusuperuser;CREATEROLEpostgres=#\dgListofrolesRolename|Attributes|Memberof-----------+------------------------------------------------------------+-----------lx|Superuser,Cannotlogin|{}pguser|Superuser,Createrole,CreateDB,Replication,BypassRLS|{}sq|Superuser,Createrole,CreateDB|{}zhaoxu|Superuser|{}zx|Superuser|{}#在zhaoxu库下创建Schemazhaoxupostgres=#\czhaoxuzhaoxuYouarenowconnectedtodatabase"zhaoxu"asuser"zhaoxu".zhaoxu=#createschemazhaoxu;CREATESCHEMAzhaoxu=#\dnListofschemasName|Owner--------+--------public|pguserzhaoxu|zhaoxu(2rows)

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

[pguser@rhel7~]$psqlzhaoxuzhaoxu<output.sqlSETCREATETABLESETBEGININSERT01INSERT01INSERT01INSERT01INSERT01INSERT01INSERT01INSERT01INSERT01INSERT01INSERT01INSERT01INSERT01INSERT01COMMIT[pguser@rhel7~]$psqlzhaoxuzhaoxupsql(9.6.1)Type"help"forhelp.zhaoxu=#\dListofrelationsSchema|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||207499|ALLEN|SALESMAN|7698|1981-02-2000:00:00|1600.00|300.00|307521|WARD|SALESMAN|7698|1981-02-2200:00:00|1250.00|500.00|307566|JONES|MANAGER|7839|1981-04-0200:00:00|2975.00||207654|MARTIN|SALESMAN|7698|1981-09-2800:00:00|1250.00|1400.00|307698|BLAKE|MANAGER|7839|1981-05-0100:00:00|2850.00||307782|CLARK|MANAGER|7839|1981-06-0900:00:00|2450.00||107788|SCOTT|ANALYST|7566|1987-04-1900:00:00|3000.00||207839|KING|PRESIDENT||1981-11-1700:00:00|5000.00||107844|TURNER|SALESMAN|7698|1981-09-0800:00:00|1500.00|0.00|307876|ADAMS|CLERK|7788|1987-05-2300:00:00|1100.00||207900|JAMES|CLERK|7698|1981-12-0300:00:00|950.00||307902|FORD|ANALYST|7566|1981-12-0300:00:00|3000.00||207934|MILLER|CLERK|7782|1982-01-2300:00:00|1300.00||10(14rows)

数据导入完成。