本文只介绍如何使用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.@H_502_9@[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@H_502_9@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;@H_502_9@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@H_502_9@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)@H_502_9@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)@H_502_9@数据导入完成。
