一、概述

现在有个需求，想把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创建测试数据，建立一个库，再建立一个表，随便插入两条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
[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表空间爆满影响数据导入。

 

--创建表空间

1
2
3
4
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;


--创建用户

1
2
3
4
5
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/

 blob.png

 

1是集成jdk的安装程序，支持windows64位的操作系统，不用自己额外安装jdk环境；

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

3可以选择之前的几个版本。

我这里下载的是sqldeveloper-4.1.3.20.78-x64.zip

注：

1如果安装过程中出现一下错误，需要用360扫一下注册表，重新安装一些补丁包就行了：

 blob.png

2移植过程最后可能出现下面提示：捕获期间出错，调用中的无效参数

 blob.png

移植报告内容如下：

 blob.png

我这里查了网上资料也没找到具体原因，只是换了一个低版本的oracle sql developer再次移植操作就OK了。所以劝大家最好不要使用最新版的oracle sql developer。不知道是不是涉及了兼容性的问题

 

4、配置oracle sql developer连接mysql：

依次选择“工具”-->“首选项”，在弹出的界面选择“数据库”-->“第三方JDBC驱动程序”

blob.png

创建oracle的连接

 blob.png

创建mysql的连接

 blob.png

可以看到已经连接上两个数据库了

blob.png 

三、开始移植

1. 删除之前的移植资料库

为充分能完成移植，不知数据库之前有没有移植过，所以先把原来的移植资料库

 blob.png

 

 blob.png

2. 开始移植操作

 blob.png


 blob.png


 blob.png

blob.png


 blob.png

选择要转换的mysql数据库，添加到列表中：

 blob.png

指定转换规则，可以根据自己的情况设定字段属性的转换，也可以新添加规则。不过一般选择默认的就能满足需求

 

 blob.png

 blob.png

blob.png

如果只导表结构不导数据，要勾选截断数据

 

 blob.png

 blob.png

 

 blob.png

 

blob.png

可以看到在输出目录下面生成了几个文件，打开前两个看一下：

 blob.png

 

 blob.png

 

 blob.png

四、数据验证

Oracle sql developer中HSDB下可以看到在数据库中能多了一个test1用户，执行查询语句也能查到数据，和上面在mysql中查到的结果一致

 blob.png

我尝试移植过另一个数据量比较大的库。如果是导入的mysql库中的数据量比较大，并不会立即就能查到数据，这是由于oracle有个统计分析的机制。

统计信息主要是描述数据库中表，索引的大小，规模，数据分布状况等的一类信息。例如，表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等，都属于统计信息。CBO正是根据这些统计信息数据，计算出不同访问路径下，不同join 方式下，各种计划的成本，最后选择出成本最小的计划。

可以使用oracle自带的dbms_stats 程序包进行手动收集系统统计信息。DBMS_STATS包，主要提供了搜集（gather），删除（delete），导出（export），导入（import），修改（set）统计信息的方法。我在这里使用的语句如下：

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