Oracle至PostgreSQL数据库迁移方案

1、Oracle至PostgreSQL数据库迁移方案Ora2PG简介Ora2PG Introduction01Ora2PGOra2PG是一个Perl语言编写的开源工具，用于将Oracle或MySQL数据库迁移到PostgreSQL数据库。它连接Oracle数据库，自动扫描并提取其结构或数据，然后 生成可以加载到PostgreSQL数据库中的SQL脚本。Ora2PG通过配置ora2pg.conf文件，指定连接Oracle的相关参数，设置导出选项（导出表，数据，其他对象），将转换后的SQL语句导出文件，再手工导入到Postgresql。或者也可以设置连接Postgresql的相关参数，直接导入到Pos

2、tgresql。虽然Ora2PG可以支持大多数SQL的转换，但某些情况下仍需要人工校正语法，使 其兼容Postgresql。功能导出具有唯一，主要，外键和检查约束的完整数据库模式（表，视图，序列，索引）。导出用户和组的授予/权限。导出范围/列表分区和子分区。导出预定义的函数，触发器，过程，包和包体。导出完整数据或遵循WHERE子句。导出Oracle用户定义的类型。提供一些PLSQL代码的基本自动转换到PLPGSQL。将DBLINK导出为Oracle_FDW。将SYNONYMS导出为视图。将PACKAGE导出为SCHEMA，PACKAGE BODY导出为FUNCTION。将Oracle定位器和空

3、间几何导出到PostGis中。显示Oracle数据库内容的详细报告。Mysql迁移与Oracle相同。Ora2PG 安装配置各操作系统平台皆可Oracle或Oracle Instant ClientPerl v5.10.1DBD:OracleDBIDBD:PG- 如果需要将结果直接导入到PG需要安装PostgreSQL 1.0 -基于PG11Ora2PG 19.1Ora2PG批量对象处理Ora2PG Batch Processing02创建Ora2PG工程创建命令：ora2pg -init_project project_name -project_base /data/ora2pg_proj

4、ectproject_name为project名称，/data/ora2pg_project/为导入导出的文件目录工程子目录及文件：Config，Schema，Source，Tables，Report，export_schema.sh,import_schema.sh 配置./config/ora2pg.conf如下ORACLE_HOME/u01/app/oracle/product/11.2.4/db_1ORACLE_DSN dbi:Oracle:host=10.51.17.192;sid=szy;port=1521ORACLE_USER systemORACLE_PWD oracleSCH

5、EMA bmps导出Oracle对象源DDL脚本到./sources目录下，并且将转换成PG语法的DDL脚本存放至./schema目录，每个对 象脚本以独立sql文件存放；rootSoftSlaver38 ora2pg_BMPS# ./export_schema.sh工程报告创建Ora2PG工程的一个好处 是，导出Oracle对象结构后， 会生成一份html格式可直观 阅读的导出报告，如图：批量导入导出数据以上命令总结如下：批量生成导出表数据的配置文件rootSoftSlaver38 myscriptexportdata#./00_export_conf.sh批量导出表数据rootSoftSl

6、aver38 myscriptexportdata#./01_export_data.confbmps.txt批量导入表数据rootSoftSlaver38 myscriptexportdata#./02_import_data.shOra2PG迁移问题Ora2PG Transfer Questions03Ora2PG迁移问题序号项目OraclePG1当前时间函数sysdatecurrent_timestamp替换2dblink可直接tabledblink需要先创建mapper映射表3时间截断函数trunc()date trunc()4序列sequencename.nextvalnextval

7、(sequencename)5Instr函数Instr(str1,str2)Strops(str1,str2)6外连接Oracle简写(+)改造为left join等语句7树级查询Start with 语句Connect by 语句With cursive替换8数据库对象名不区分大小写区分大小写，且默认为小写9同义词支持使用视图代替10Rownum返回行号翻页使用limit或row_number() over()替换11To_char()支持To_char(type,string)传入多个参 数，其中还可以涉及到时间类型12Null和Oracle认为相同不相同13MinusMinus求不相交部

8、分Except代替14子查询别名From后面子查询可以不要别名必须带上别名15字段别名Na字段显示为name Select na name from tabSelect na as name from tabOra2PG迁移问题的数量与Oracle和PG的 兼容程度成反比，两者 比较显著的差异如右表所示：Ora2PG迁移问题1、建表语句问题：PG关键字描述：Oracle表中current_time字段，在PG中建表时被识别为时间函数关键字，导致建 表失败；解决方法：给current_time, current_date, current_timestamp等关键字加引号，如 create ta

9、ble T (current_time varchar(20);2、存储过程语句问题1：Insert into Select句式描述：存储过程中【insert into table(id,name) select 0, john from tab】句式经Ora2PG转换出错，误转换为【insert into table(id,name) PERFORM 0, john fromtab】解决方法：暂时手工修改PERFORM为select；问题2：Oracle包头定义丢失描述：Oracle中package语句，先在package中定义类型type type_cur is ref cursor ，然

10、后package body中定义变量v_cur out type_cur ，经Ora2PG转换生 成function时，丢失了type type_cur is ref cursor这一句定义，直接使用了v_cur out type_cur，故报错”类型type_cur不存在“；处理方法：将v_cur out type_cur 修改为v_cur out refcursor 即可；问题3：Rownum支持描述：Oracle中rownum字段，Ora2PG未经转换，而PG中无法识别；处理方法：替换rownum为row_number() over() ;如select rownum from tab

11、改为 Select row_number() over() as rn from tab；问题4：PG子查询别名描述：Oracle中子查询不带别名，而PG中子查询需要加上别名，否则提示语法错 误；处理方法：手工修改ddl脚本，加上别名；如【select * from (select id fromtable)】 改为【select * from (select id from table) A 】;问题5：Ora2PG默认数值类型描述：Oracle中number类型被Ora2PG默认转换为bigint，而number类型可存 放小数，bigint只能存放整数，造成小数位丢失；处理方法：调整Or

12、a2PG配置参数DEFAULT_NUMERIC，从bigint改为numeric， 同时 PG_NUMERIC_TYPE 与PG_INTEGER_TYPE 都设为0；问题6：delete单写描述：Oracle中DML语句delete等同于delete from，但PG中不支持delete单写；处理方法：手工修改DML语句或使用PostgreSQL代替PG;问题7：Null与空字符描述：Oracle中null等同于空字符 ，但PG中两者不同； 虽然Ora2PG迁移时已 将Oracle空字符转为null，但无法处理应用代码层面或PG Function写入空字符到PG数据库的问题；处理方法：对表添加

13、触发器并调整应用代码，或使用PostgreSQL代替PG；问题8：Sequece语法描述：Oracle中【select sequencename.nextval from dual】句式PG不支持，Ora2PG未作改写；处理方法：修改句式【 select nextval(sequencename) from dual; 】;(自增列)问题9：数值字段like查询描述：Oracle中支持数值字段like 123%写法，PG中则提示语法错误 ；处理方法：创建数值类型与字符串的映射规则，或使用PostgreSQL代替PG；问题10：number与char关联查询描述：例如tab(id number, name char(10)，两者关联查询Oracle写法：select * from tab where id=name；PG写法：select* from tab where id:char=name;处理方法：同问题9；3、C#应用程序兼容问题1：结果集列名大写描述：Oracle查询时，对象名称大小写不敏感，且返回结果集默认为大写列名；PG中若对象名不 加双引号标志，则自动将对象名转为小写进行查询并返回以小写列名展示的结果集；若C#应用代 码中datatable类型写定需要获取大写列名，则切换Oracle为PG时会出现应用无法获取数据的情 况；处