Windows下安装Ora2Pg并从oracle中导出数据到postgresql

作者：瀚高PG实验室（Highgo PG Lab）

下载安装perl，下载地址：

https://www.activestate.com/activeperl/downloads/thank-you?dl=http://downloads.activestate.com/ActivePerl/releases/5.24.3.2404/ActivePerl-5.24.3.2404-MSWin32-x64-404865.exe

安装dmake，ppm install dmake

下载ora2pg并解压，下载地址

https://sourceforge.net/projects/ora2pg/

编译安装ora2pg

D:\highgo\ora2pg-18.2>perl Makefile.PL

Invalid LICENSE value 'GPLv3' ignored

Generating a dmake-style Makefile

Writing Makefile for Ora2Pg

Invalid LICENSE value 'GPLv3' ignored

Writing MYMETA.yml and MYMETA.json

Done...

------------------------------------------------------------------------------

Please read documentation at http://ora2pg.darold.net/ before asking for help

------------------------------------------------------------------------------

Now type: dmake && dmake install

D:\highgo\ora2pg-18.2>dmake && dmake install

cp lib/Ora2Pg/MySQL.pm blib\lib\Ora2Pg\MySQL.pm

cp lib/Ora2Pg.pm blib\lib\Ora2Pg.pm

cp lib/Ora2Pg/GEOM.pm blib\lib\Ora2Pg\GEOM.pm

cp lib/Ora2Pg/PLSQL.pm blib\lib\Ora2Pg\PLSQL.pm

"C:\Perl64\bin\perl.exe" -MExtUtils::Command -e cp -- scripts/ora2pg blib\script\ora2pg

pl2bat.bat blib\script\ora2pg

"C:\Perl64\bin\perl.exe" -MExtUtils::Command -e cp -- scripts/ora2pg_scanner blib\script\ora2pg_scanner

pl2bat.bat blib\script\ora2pg_scanner

Installing C:\Perl64\html\site\lib\Ora2Pg.html

Installing C:\Perl64\html\site\lib\Ora2Pg\MySQL.html

Installing C:\Perl64\html\site\lib\Ora2Pg\PLSQL.html

Installing C:\Perl64\site\lib\Ora2Pg.pm

Installing C:\Perl64\site\lib\Ora2Pg\GEOM.pm

Installing C:\Perl64\site\lib\Ora2Pg\MySQL.pm

Installing C:\Perl64\site\lib\Ora2Pg\PLSQL.pm

Installing C:\Perl64\site\bin\ora2pg

Installing C:\Perl64\site\bin\ora2pg.bat

Installing C:\Perl64\site\bin\ora2pg_scanner

Installing C:\Perl64\site\bin\ora2pg_scanner.bat

"Installing default configuration file (ora2pg_dist.conf) to C:\ora2pg"

Appending installation info to C:\Perl64\lib/perllocal.pod

dmake.exe: Warning: -- Target [install] was made but the time stamp has not been updated.

安装oracle驱动

C:\Perl64\cpan\sources\authors\id\P\PY\PYTHIAN\DBD-Oracle-1.74>cpan

Loading internal null logger. Install Log::Log4perl for logging messages

Unable to get Terminal Size. The Win32 GetConsoleScreenBufferInfo call didn't work. The COLUMNS and LINES environment variables didn't work. at C:\Perl64\lib/Term/ReadLine/readline.pm line 410.

cpan shell -- CPAN exploration and modules installation (v2.1101)

Enter 'h' for help.

cpan> get DBD::Oracle

Fetching with LWP:

http://ppm.activestate.com/CPAN/authors/01mailrc.txt.gz

Reading 'C:\Perl64\cpan\sources\authors\01mailrc.txt.gz'

............................................................................DONE

Fetching with LWP:

http://ppm.activestate.com/CPAN/modules/02packages.details.txt.gz

Reading 'C:\Perl64\cpan\sources\modules\02packages.details.txt.gz'

Database was generated on Tue, 27 Feb 2018 06:29:03 GMT

.............

New CPAN.pm version (v2.16) available.

[Currently running version is v2.1101]

You might want to try

install CPAN

reload cpan

to both upgrade CPAN.pm and run the new version without leaving

the current session.

...............................................................DONE

Fetching with LWP:

http://ppm.activestate.com/CPAN/modules/03modlist.data.gz

Reading 'C:\Perl64\cpan\sources\modules\03modlist.data.gz'

DONE

Writing C:\Perl64\cpan\Metadata

Running get for module 'DBD::Oracle'

Fetching with LWP:

http://ppm.activestate.com/CPAN/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.74.tar.gz

Fetching with LWP:

http://ppm.activestate.com/CPAN/authors/id/P/PY/PYTHIAN/CHECKSUMS

Checksum for C:\Perl64\cpan\sources\authors\id\P\PY\PYTHIAN\DBD-Oracle-1.74.tar.gz ok

Scanning cache C:\Perl64/cpan/build for sizes

DONE

……

DBD-Oracle-1.74/dbdimp.c

DBD-Oracle-1.74/README

cpan> install DBD::Oracle

示例：使用Ora2pg导出Scott用户下的表

配置Ora2pg配置文件：C:\ora2pg\ora2pg.conf

ORACLE_HOME D:\app\Administrator\product\11.2.0\dbhome_1

ORACLE_DSN dbi:Oracle:host=127.0.0.1;sid=orcl

ORACLE_USER system --导出使用的用户

ORACLE_PWD oracle

SCHEMA scott --将要导出的用户

USER_GRANTS 0

DEBUG 0

ORA_INITIAL_COMMAND

EXPORT_SCHEMA 0

CREATE_SCHEMA 1

COMPILE_SCHEMA 0

TYPE TABLE,INSERT

OUTPUT C:\ora2pg\output.sql --导出文件放置位置

执行导出

C:\Perl64\cpan\sources\authors\id\P\PY\PYTHIAN\DBD-Oracle-1.74>ora2pg -c C:\ora2pg\ora2pg.conf

[========================>] 4/4 tables (100.0%) end of scanning.

[> ] 0/4 tables (0.0%) end of scanning.

[========================>] 4/4 tables (100.0%) end of table export.

[========================>] 0/0 rows (100.0%) Table BONUS (0 recs/sec)

[> ] 0/23 total rows (0.0%) - (0 sec., avg: 0 recs/sec).

[========================>] 4/4 rows (100.0%) Table DEPT (4 recs/sec)

[====> ] 4/23 total rows (17.4%) - (1 sec., avg: 4 recs/sec).

[========================>] 14/14 rows (100.0%) Table EMP (14 recs/sec)

[==================> ] 18/23 total rows (78.3%) - (3 sec., avg: 6 recs/sec).

[========================>] 5/5 rows (100.0%) Table SALGRADE (5 recs/sec)

[========================>] 23/23 total rows (100.0%) - (5 sec., avg: 4 recs/sec).

[========================>] 23/23 rows (100.0%) on total estimated data (5 sec., avg: 4 recs/sec)

导出的文件内容如下：

-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.2

-- DATASOURCE: dbi:Oracle:host=127.0.0.1;sid=orcl

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON

SET check_function_bodies = false;

CREATE TABLE salgrade (

grade bigint,

losal bigint,

hisal bigint

) ;

CREATE TABLE dept (

deptno smallint NOT NULL,

dname varchar(14),

loc varchar(13)

) ;

ALTER TABLE dept ADD PRIMARY KEY (deptno);

CREATE TABLE emp (

empno smallint NOT NULL,

ename varchar(10),

job varchar(9),

mgr smallint,

hiredate timestamp,

sal decimal(7,2),

comm decimal(7,2),

deptno smallint

) ;

ALTER TABLE emp ADD PRIMARY KEY (empno);

CREATE TABLE bonus (

ename varchar(10),

job varchar(9),

sal bigint,

comm bigint

) ;

ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;

-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.2

-- DATASOURCE: dbi:Oracle:host=127.0.0.1;sid=orcl

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON

SET check_function_bodies = false;

BEGIN;

INSERT INTO dept (deptno,dname,loc) VALUES (10,E'ACCOUNTING',E'NEW YORK');

INSERT INTO dept (deptno,dname,loc) VALUES (20,E'RESEARCH',E'DALLAS');

INSERT INTO dept (deptno,dname,loc) VALUES (30,E'SALES',E'CHICAGO');

INSERT INTO dept (deptno,dname,loc) VALUES (40,E'OPERATIONS',E'BOSTON');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7369,E'SMITH',E'CLERK',7902,'1980-12-17 00:00:00',800,NULL,20);

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,E'ALLEN',E'SALESMAN',7698,'1981-02-20 00:00:00',1600,300,30);

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7521,E'WARD',E'SALESMAN',7698,'1981-02-22 00:00:00',1250,500,30);

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7566,E'JONES',E'MANAGER',7839,'1981-04-02 00:00:00',2975,NULL,20);

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7654,E'MARTIN',E'SALESMAN',7698,'1981-09-28 00:00:00',1250,1400,30);

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7698,E'BLAKE',E'MANAGER',7839,'1981-05-01 00:00:00',2850,NULL,30);

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7782,E'CLARK',E'MANAGER',7839,'1981-06-09 00:00:00',2450,NULL,10);

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7788,E'SCOTT',E'ANALYST',7566,'1987-04-19 00:00:00',3000,NULL,20);

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7839,E'KING',E'PRESIDENT',NULL,'1981-11-17 00:00:00',5000,NULL,10);

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7844,E'TURNER',E'SALESMAN',7698,'1981-09-08 00:00:00',1500,0,30);

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7876,E'ADAMS',E'CLERK',7788,'1987-05-23 00:00:00',1100,NULL,20);

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7900,E'JAMES',E'CLERK',7698,'1981-12-03 00:00:00',950,NULL,30);

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7902,E'FORD',E'ANALYST',7566,'1981-12-03 00:00:00',3000,NULL,20);

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7934,E'MILLER',E'CLERK',7782,'1982-01-23 00:00:00',1300,NULL,10);

INSERT INTO salgrade (grade,losal,hisal) VALUES (1,700,1200);

INSERT INTO salgrade (grade,losal,hisal) VALUES (2,1201,1400);

INSERT INTO salgrade (grade,losal,hisal) VALUES (3,1401,2000);

INSERT INTO salgrade (grade,losal,hisal) VALUES (4,2001,3000);

INSERT INTO salgrade (grade,losal,hisal) VALUES (5,3001,9999);

COMMIT;

将以上语句在hgdb中执行即可：

执行结果

Windows下安装Ora2Pg并从oracle中导出数据到postgresql

继续阅读

监控PostgreSQL的磁盘使用

PG（HGDB）中创建schema及修改schema的属主及名称

pg查看每个数据库对应的目录

HGDB（pg）建表时指定默认值

PostgreSQL9.6的新特性并行查询

PostgreSQL常见名词解释

pg_dump不能导出访问权限 pg_dumpall可以导出访问权限

PostgreSQL中的统计信息、选择度和基数

ERROR: 42883: operator does not exist: integer !=- integer(APP)

PostgreSQL数据库修改行外存储（TOAST）可能遇到的坑

HGDB参数设置

PostgreSQL13.1-CN-v1.0中文手册.chm下载

瀚高数据库用户默认密码有效期

Highgo Database 企业版单机安装指导手册一、安装前准备二、安装Highgo Database软件

HGDB日志管理

postgresql 关于GBK和utf-8字符集问题