使用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、修改参数文件

@H_403_12@[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、使用上面修改的参数导出数据

@H_403_12@[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服务器上

@H_403_12@[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)

数据导入完成。

原文链接：https://www.f2er.com/oracle/211411.html

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

猜你在找的Oracle相关文章