文档课题： oracle到mysql数据迁移之navicat方法.
源端：oracle 19.13
目标端：mysql 8.0.27
1、源端数据
sys@TMIS> select table_name from dba_tables where owner='RPTUSER';
TABLE_NAME
------------------------------
FRUITS
BOOKS
sys@TMIS> select * from rptuser.fruits;
F_ID S_ID F_NAME F_PRICE
---------- ---------- ------------------------------ ----------
a1 101 apple 5.2
b1 101 blackberry 10.2
bs1 102 orange 11.2
bs2 105 melon 8.2
t1 102 banana 10.3
t2 102 grape 5.3
o2 103 coconut 9.2
c0 101 cherry 3.2
a2 103 apricot 2.2
l2 104 lemon 6.4
b2 104 berry 7.6
m1 106 mango 15.7
m2 105 xbabay 2.6
t4 107 xbababa 3.6
m3 105 xxtt 11.6
b5 107 xxxx 3.6
16 rows selected.
sys@TMIS> select * from rptuser.books;
BK_ID BK_TITLE COPYRIGHT
---------- -------------------------------------------------- -------------------
11078 Learning MYSQL 2010-01-01 00:00:00
11033 Study Html 2011-01-01 00:00:00
11035 How to use php 2003-01-01 00:00:00
11072 Teach yourself javascript 2005-01-01 00:00:00
11028 Learning C++ 2005-01-01 00:00:00
11069 MYSQL professional 2009-01-01 00:00:00
11026 Guide to MySQL 5.7 2008-01-01 00:00:00
11041 Inside VC++ 2011-01-01 00:00:00
8 rows selected.
2、目标端授权
mysql> grant select,create,update,delete on `rptdb`.* to 'root'@'192.168.133.%';
Query OK, 0 rows affected (0.00 sec)
mysql> grant insert on `rptdb`.* to 'root'@'192.168.133.%';
Query OK, 0 rows affected (0.00 sec)
mysql> grant drop on `rptdb`.* to 'root'@'192.168.133.%';
Query OK, 0 rows affected (0.07 sec)
3、navicat迁移数据
--navicat连接oracle与mysql数据库.
--点击菜单栏的"工具"按钮，然后点击第一个"数据传输".
--在弹出的"数据传输对话框中"，选择需要导出的oracle数据库和将要导入的mysql数据库.
4、验证数据
mysql> select database();
+------------+
| database() |
+------------+
| rptdb |
+------------+
1 row in set (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_rptdb |
+-----------------+
| BOOKS |
| FRUITS |
+-----------------+
2 rows in set (0.00 sec)
mysql> select * from BOOKS;
+-------+---------------------------+---------------------+
| BK_ID | BK_TITLE | COPYRIGHT |
+-------+---------------------------+---------------------+
| 11026 | Guide to MySQL 5.7 | 2008-01-01 00:00:00 |
| 11028 | Learning C++ | 2005-01-01 00:00:00 |
| 11033 | Study Html | 2011-01-01 00:00:00 |
| 11035 | How to use php | 2003-01-01 00:00:00 |
| 11041 | Inside VC++ | 2011-01-01 00:00:00 |
| 11069 | MYSQL professional | 2009-01-01 00:00:00 |
| 11072 | Teach yourself javascript | 2005-01-01 00:00:00 |
| 11078 | Learning MYSQL | 2010-01-01 00:00:00 |
+-------+---------------------------+---------------------+
8 rows in set (0.00 sec)
mysql> select * from FRUITS;
+------+------------------------------------+------------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------------------------------------+------------+---------+
| a1 | 101.000000000000000000000000000000 | apple | 5.20 |
| a2 | 103.000000000000000000000000000000 | apricot | 2.20 |
| b1 | 101.000000000000000000000000000000 | blackberry | 10.20 |
| b2 | 104.000000000000000000000000000000 | berry | 7.60 |
| b5 | 107.000000000000000000000000000000 | xxxx | 3.60 |
| bs1 | 102.000000000000000000000000000000 | orange | 11.20 |
| bs2 | 105.000000000000000000000000000000 | melon | 8.20 |
| c0 | 101.000000000000000000000000000000 | cherry | 3.20 |
| l2 | 104.000000000000000000000000000000 | lemon | 6.40 |
| m1 | 106.000000000000000000000000000000 | mango | 15.70 |
| m2 | 105.000000000000000000000000000000 | xbabay | 2.60 |
| m3 | 105.000000000000000000000000000000 | xxtt | 11.60 |
| o2 | 103.000000000000000000000000000000 | coconut | 9.20 |
| t1 | 102.000000000000000000000000000000 | banana | 10.30 |
| t2 | 102.000000000000000000000000000000 | grape | 5.30 |
| t4 | 107.000000000000000000000000000000 | xbababa | 3.60 |
+------+------------------------------------+------------+---------+
16 rows in set (0.00 sec)
mysql> DESC FRUITS;
+---------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------+------+-----+---------+-------+
| F_ID | varchar(10) | NO | PRI | NULL | |
| S_ID | decimal(65,30) | NO | | NULL | |
| F_NAME | varchar(255) | NO | | NULL | |
| F_PRICE | decimal(8,2) | NO | | NULL | |
+---------+----------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> desc BOOKS
-> ;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| BK_ID | decimal(38,0) | NO | PRI | NULL | |
| BK_TITLE | varchar(50) | NO | | NULL | |
| COPYRIGHT | datetime | NO | | NULL | |
+-----------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
说明：如上所示，数据成功导入到mysql目标端.
