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);