Oracle至PostgreSQL數(shù)據(jù)庫遷移方案

1、Oracle至PostgreSQL數(shù)據(jù)庫遷移方案Ora2PG簡介Ora2PG Introduction01Ora2PGOra2PG是一個Perl語言編寫的開源工具，用于將Oracle或MySQL數(shù)據(jù)庫遷移到PostgreSQL數(shù)據(jù)庫。它連接Oracle數(shù)據(jù)庫，自動掃描并提取其結(jié)構(gòu)或數(shù)據(jù)，然后 生成可以加載到PostgreSQL數(shù)據(jù)庫中的SQL腳本。Ora2PG通過配置ora2pg.conf文件，指定連接Oracle的相關(guān)參數(shù)，設(shè)置導出選項（導出表，數(shù)據(jù)，其他對象），將轉(zhuǎn)換后的SQL語句導出文件，再手工導入到Postgresql。或者也可以設(shè)置連接Postgresql的相關(guān)參數(shù)，直接導入到Pos

2、tgresql。雖然Ora2PG可以支持大多數(shù)SQL的轉(zhuǎn)換，但某些情況下仍需要人工校正語法，使 其兼容Postgresql。功能導出具有唯一，主要，外鍵和檢查約束的完整數(shù)據(jù)庫模式（表，視圖，序列，索引）。導出用戶和組的授予/權(quán)限。導出范圍/列表分區(qū)和子分區(qū)。導出預定義的函數(shù)，觸發(fā)器，過程，包和包體。導出完整數(shù)據(jù)或遵循WHERE子句。導出Oracle用戶定義的類型。提供一些PLSQL代碼的基本自動轉(zhuǎn)換到PLPGSQL。將DBLINK導出為Oracle_FDW。將SYNONYMS導出為視圖。將PACKAGE導出為SCHEMA，PACKAGE BODY導出為FUNCTION。將Oracle定位器和空

3、間幾何導出到PostGis中。顯示Oracle數(shù)據(jù)庫內(nèi)容的詳細報告。Mysql遷移與Oracle相同。Ora2PG 安裝配置各操作系統(tǒng)平臺皆可Oracle或Oracle Instant ClientPerl v5.10.1DBD:OracleDBIDBD:PG- 如果需要將結(jié)果直接導入到PG需要安裝PostgreSQL 1.0 -基于PG11Ora2PG 19.1Ora2PG批量對象處理Ora2PG Batch Processing02創(chuàng)建Ora2PG工程創(chuàng)建命令：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目錄下，并且將轉(zhuǎn)換成PG語法的DDL腳本存放至./schema目錄，每個對 象腳本以獨立sql文件存放；rootSoftSlaver38 ora2pg_BMPS# ./export_schema.sh工程報告創(chuàng)建Ora2PG工程的一個好處 是，導出Oracle對象結(jié)構(gòu)后， 會生成一份html格式可直觀 閱讀的導出報告，如圖：批量導入導出數(shù)據(jù)以上命令總結(jié)如下：批量生成導出表數(shù)據(jù)的配置文件rootSoftSlaver38 myscriptexportdata#./00_export_conf.sh批量導出表數(shù)據(jù)rootSoftSl

6、aver38 myscriptexportdata#./01_export_data.confbmps.txt批量導入表數(shù)據(jù)rootSoftSlaver38 myscriptexportdata#./02_import_data.shOra2PG遷移問題Ora2PG Transfer Questions03Ora2PG遷移問題序號項目OraclePG1當前時間函數(shù)sysdatecurrent_timestamp替換2dblink可直接tabledblink需要先創(chuàng)建mapper映射表3時間截斷函數(shù)trunc()date trunc()4序列sequencename.nextvalnextval

7、(sequencename)5Instr函數(shù)Instr(str1,str2)Strops(str1,str2)6外連接Oracle簡寫(+)改造為left join等語句7樹級查詢Start with 語句Connect by 語句With cursive替換8數(shù)據(jù)庫對象名不區(qū)分大小寫區(qū)分大小寫，且默認為小寫9同義詞支持使用視圖代替10Rownum返回行號翻頁使用limit或row_number() over()替換11To_char()支持To_char(type,string)傳入多個參 數(shù)，其中還可以涉及到時間類型12Null和Oracle認為相同不相同13MinusMinus求不相交部

8、分Except代替14子查詢別名From后面子查詢可以不要別名必須帶上別名15字段別名Na字段顯示為name Select na name from tabSelect na as name from tabOra2PG遷移問題的數(shù)量與Oracle和PG的 兼容程度成反比，兩者 比較顯著的差異如右表所示：Ora2PG遷移問題1、建表語句問題：PG關(guān)鍵字描述：Oracle表中current_time字段，在PG中建表時被識別為時間函數(shù)關(guān)鍵字，導致建 表失??；解決方法：給current_time, current_date, current_timestamp等關(guān)鍵字加引號，如 create ta

9、ble T (current_time varchar(20);2、存儲過程語句問題1：Insert into Select句式描述：存儲過程中【insert into table(id,name) select 0, john from tab】句式經(jīng)Ora2PG轉(zhuǎn)換出錯，誤轉(zhuǎn)換為【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 ，經(jīng)Ora2PG轉(zhuǎn)換生 成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未經(jīng)轉(zhuǎn)換，而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默認數(shù)值類型描述：Oracle中number類型被Ora2PG默認轉(zhuǎn)換為bigint，而number類型可存 放小數(shù)，bigint只能存放整數(shù)，造成小數(shù)位丟失；處理方法：調(diào)整Or

12、a2PG配置參數(shù)DEFAULT_NUMERIC，從bigint改為numeric， 同時 PG_NUMERIC_TYPE 與PG_INTEGER_TYPE 都設(shè)為0；問題6：delete單寫描述：Oracle中DML語句delete等同于delete from，但PG中不支持delete單寫；處理方法：手工修改DML語句或使用PostgreSQL代替PG;問題7：Null與空字符描述：Oracle中null等同于空字符 ，但PG中兩者不同； 雖然Ora2PG遷移時已 將Oracle空字符轉(zhuǎn)為null，但無法處理應用代碼層面或PG Function寫入空字符到PG數(shù)據(jù)庫的問題；處理方法：對表添加

13、觸發(fā)器并調(diào)整應用代碼，或使用PostgreSQL代替PG；問題8：Sequece語法描述：Oracle中【select sequencename.nextval from dual】句式PG不支持，Ora2PG未作改寫；處理方法：修改句式【 select nextval(sequencename) from dual; 】;(自增列)問題9：數(shù)值字段like查詢描述：Oracle中支持數(shù)值字段like 123%寫法，PG中則提示語法錯誤 ；處理方法：創(chuàng)建數(shù)值類型與字符串的映射規(guī)則，或使用PostgreSQL代替PG；問題10：number與char關(guān)聯(lián)查詢描述：例如tab(id number, name char(10)，兩者關(guān)聯(lián)查詢Oracle寫法：select * from tab where id=name；PG寫法：select* from tab where id:char=name;處理方法：同問題9；3、C#應用程序兼容問題1：結(jié)果集列名大寫描述：Oracle查詢時，對象名稱大小寫不敏感，且返回結(jié)果集默認為大寫列名；PG中若對象名不 加雙引號標志，則自動將對象名轉(zhuǎn)為小寫進行查詢并返回以小寫列名展示的結(jié)果集；若C#應用代 碼中datatable類型寫定需要獲取大寫列名，則切換Oracle為PG時會出現(xiàn)應用無法獲取數(shù)據(jù)的情 況；處