MYSQL數據庫遷移到ORACLE數據庫

一、概述

現在有個需求，想把mysql數據庫中的數據轉移到oracle數據庫中，網上找了好幾種方法，最后決定使用oracel sql developer 這種工具來實現。

Mysql和oracle屬于兩種不同的數據庫，具體使用差異也有很多，不能簡單使用sql文件進行導出導入。以下幾個示例比較一下Mysql與oracle數據庫的差異

l SQL基本語法差異

MySQL的SQL語句中，要求表名、表的別名必須區分大小寫（可通過修改mysql配置文件my.cnf，增加lower_case_table_names=1來忽略大小寫，從而與Oracle盡量保持一致），尤其是表的別名。建議通過組內開發規范統一起來，以消除差異性。

MySQL的Insert語句，支持ignore語法忽略主鍵沖突報錯，如：insert ignore into table_name...；支持insert into ... on duplicate key update...的用法，此時要求表必須有主鍵或唯一性約束字段。

與insert語句類似，MySQL還支持replace into語句，若插入表中已存在數據時，則用新數據記錄替換舊的記錄。

l 建表語句差異

創建表時，MySQL不支持 create or replace，但可以通過drop table if exists tbl_name來重新創建表。

另外，在建表語句、建索引語句中，MySQL不支持tablespace后綴。此時，可以將Oracle導出的DDL語句中，tablespace部分注釋掉；同時，在導入 MySQL數據庫時，先執行set @@sql_mode='oracle'以盡量保持與Oracle語法一致。具體sql_mode，可參考MySQL官方手冊等文檔。

l 內置函數差異

具體參考https://www.cnblogs.com/HondaHsu/p/3641190.html

l 表主鍵取值差異

MySQL不支持Sequence，在表中可設置自增主鍵。如與Oracle兼容，可考慮統一采用 function來封裝主鍵取值差異。

l 存儲過程差異

對于存儲過程而言，Oracle 的PLSQL與 MySQL也是有較大不同的，比如，MySQL不支持包、不支持常量定義、不支持dbms_output調試……具體細節以后另外整理。

l 視圖差異

MySQL的視圖from語句中不允許存在子查詢，可以通過將子查詢創建為新的視圖的方式解決。Oracle的物化視圖相關概念，在MySQL中也不存在。

二、環境準備

1、mysql準備工作

mysql創建測試數據，建立一個庫，再建立一個表，隨便插入兩條數據

[root@DB mysqldb]# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.1.73 Source distribution
 
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |      |
| mysql              |
| test               |
+--------------------+
3rows in set (0.12 sec)
 
mysql> create database test1;
Query OK, 1 row affected (0.03 sec)
 
mysql> use test1
Database changed
mysql> create table tb1(name varchar(20),age int);
Query OK, 0 rows affected (0.06 sec)
 
mysql> insert into tb1 values("tom",18);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into tb1 values("jerry",20);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from tb1;
+-------+------+
| name  | age  |
+-------+------+
| tom |   18 |
| jerry |   20 |
+-------+------+
2 rows in set (0.03 sec)
 
mysql> exit
Bye

2、Oracle準備工作

在Mysql中，數據庫和用戶之間的關系不是特別明顯，只要我們鏈接到Mysql服務器，可以看到所有的數據庫和表，而Oracle新建一個用戶是沒有任何資源和權限的，需要我們為其分配資源權限，也可以在這個用戶下面建立表，但不能向Oracle那樣建數據庫，因為對oracle來說,一個用戶就對應Mysql的一個數據庫。所以遷移數據庫,需要在Oracle中創建一個與相應mysql數據庫同名的用戶（我這里要遷移的mysql數據庫是test1，創建的oracle用戶名是test1）.

這種情況要注意oracle數據庫的用戶名。因為sql developer把mysql轉換到oracle數據庫中時，會把mysql的數據存放到一個用戶下，這個用戶名一定會和mysql的數據庫名相同。如果oracle中已有這個用戶，則數據直接導入到該用戶下；如果沒有這個用戶，則sql developer會直接創建這樣的用戶，并把數據導入到該用戶下。需要注意的是，sql developer默認創建的oracle數據庫用戶的默認表空間是user，如果不注意，很有可能會導致user表空間爆滿！所以這種情況最好先建一個和mysql數據庫名一樣的oracle用戶，以防止user表空間爆滿影響數據導入。

--創建表空間

CREATE TABLESPACE MY_TEST DATAFILE '/opt/oracle/oradata/test/MY_TEST.dbf' SIZE 100M 
    AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

--創建用戶

CREATE USER test1 IDENTIFIED BY  test1 DEFAULT TABLESPACE MY_TEST TEMPORARY TABLESPACE TEMP;
GRANT CONNECT TO test1;
GRANT RESOURCE TO test1;
GRANT DBA TO test1;
GRANT UNLIMITED TABLESPACE TO test1;

3、所需要工具的安裝配置

oracel sql developer 獲取地址：

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

mysql-connector-java-5.1.44-bin.jar獲取地址

http://download.softagency.net/mysql/Downloads/Connector-J/

1是集成jdk的安裝程序，支持windows64位的操作系統，不用自己額外安裝jdk環境；

2是不包含jdk的安裝程序，可以支持windows64位和windows32位的操作；

3可以選擇之前的幾個版本。

我這里下載的是sqldeveloper-4.1.3.20.78-x64.zip

注：

1如果安裝過程中出現一下錯誤，需要用360掃一下注冊表，重新安裝一些補丁包就行了：

2移植過程最后可能出現下面提示：捕獲期間出錯，調用中的無效參數

移植報告內容如下：

我這里查了網上資料也沒找到具體原因，只是換了一個低版本的oracle sql developer再次移植操作就OK了。所以勸大家最好不要使用最新版的oracle sql developer。不知道是不是涉及了兼容性的問題

4、配置oracle sql developer連接mysql：

依次選擇“工具”-->“首選項”，在彈出的界面選擇“數據庫”-->“第三方JDBC驅動程序”

創建oracle的連接

創建mysql的連接

可以看到已經連接上兩個數據庫了

三、開始移植

1. 刪除之前的移植資料庫

為充分能完成移植，不知數據庫之前有沒有移植過，所以先把原來的移植資料庫

2. 開始移植操作

選擇要轉換的mysql數據庫，添加到列表中：

指定轉換規則，可以根據自己的情況設定字段屬性的轉換，也可以新添加規則。不過一般選擇默認的就能滿足需求

如果只導表結構不導數據，要勾選截斷數據

可以看到在輸出目錄下面生成了幾個文件，打開前兩個看一下：

四、數據驗證

Oracle sql developer中HSDB下可以看到在數據庫中能多了一個test1用戶，執行查詢語句也能查到數據，和上面在mysql中查到的結果一致

我嘗試移植過另一個數據量比較大的庫。如果是導入的mysql庫中的數據量比較大，并不會立即就能查到數據，這是由于oracle有個統計分析的機制。

統計信息主要是描述數據庫中表，索引的大小，規模，數據分布狀況等的一類信息。例如，表的行數，塊數，平均每行的大小，索引的leaf blocks，索引字段的行數，不同值的大小等，都屬于統計信息。CBO正是根據這些統計信息數據，計算出不同訪問路徑下，不同join 方式下，各種計劃的成本，最后選擇出成本最小的計劃。

可以使用oracle自帶的dbms_stats 程序包進行手動收集系統統計信息。DBMS_STATS包，主要提供了搜集（gather），刪除（delete），導出（export），導入（import），修改（set）統計信息的方法。我在這里使用的語句如下：

exec dbms_stats.delete_schema_stats('TEST1');
exec dbms_stats.gather_schema_stats(ownname =>'TEST1',degree => 4,cascade => true);