mysql 导数 sqlloader_sqlldr 完成mysql到oracle的数据迁移

sql loader可以把文本文件倒进oracle数据库中，是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具，但是速度比较慢，另外对blob等类型的数据就有点麻烦了

下面演示利用sqlldr从mysql迁移某个表到oracle数据库。

一：在mysql把表search_test_zh_cn.Industry导出成.txt文件。

mysql> select * from search_test_zh_cn.Industry;

+------------+-----------------+----------+

| industryId | industryName    | langType |

+------------+-----------------+----------+

|          1 | 建材与冶金      | zh_CN    |

|          2 | 化工与石化      | zh_CN    |

|          3 | 矿产与能源      | zh_CN    |

|          4 | 橡胶与塑料      | zh_CN    |

|          5 | 机械与设备      | zh_CN    |

|          6 | 照明与安防      | zh_CN    |

|          7 | 电器与电子      | zh_CN    |

|          8 | 包装与文教      | zh_CN    |

|          9 | 纺织与皮革      | zh_CN    |

|         10 | 服装与饰品      | zh_CN    |

|         11 | 食品与农业      | zh_CN    |

|         12 | 家居与百货      | zh_CN    |

|         13 | 美妆与美容      | zh_CN    |

|         14 | 车船与交通      | zh_CN    |

|         15 | 医药与保健      | zh_CN    |

|         16 | 商务与服务      | zh_CN    |

+------------+-----------------+----------+

16 rows in set (0.00 sec)

导出语句：

mysql> select * from search_test_zh_cn.Industry into outfile '/tmp/liuwenhe.txt' fields terminated  by ",";

Query OK, 16 rows affected (0.00 sec)

####题外话：如果要倒进mysql的某张表语句：mysql> load data infile '/tmp/liuwenhe.txt' into table search_test_zh_cn.Industryaa fields terminated  by ",";

Query OK, 16 rows affected (0.00 sec)

Records: 16  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from search_test_zh_cn.Industryaa;

+------------+-----------------+----------+

| industryId | industryName    | langType |

+------------+-----------------+----------+

|          1 | 建材与冶金      | zh_CN    |

|          2 | 化工与石化      | zh_CN    |

|          3 | 矿产与能源      | zh_CN    |

|          4 | 橡胶与塑料      | zh_CN    |

|          5 | 机械与设备      | zh_CN    |

|          6 | 照明与安防      | zh_CN    |

|          7 | 电器与电子      | zh_CN    |

|          8 | 包装与文教      | zh_CN    |

|          9 | 纺织与皮革      | zh_CN    |

|         10 | 服装与饰品      | zh_CN    |

|         11 | 食品与农业      | zh_CN    |

|         12 | 家居与百货      | zh_CN    |

|         13 | 美妆与美容      | zh_CN    |

|         14 | 车船与交通      | zh_CN    |

|         15 | 医药与保健      | zh_CN    |

|         16 | 商务与服务      | zh_CN    |

+------------+-----------------+----------+

16 rows in set (0.00 sec)     ####

在相应目录下查看导出的内容，确定导出成功。

[root@localhost tmp]# cat liuwenhe.txt

1,建材与冶金,zh_CN

2,化工与石化,zh_CN

3,矿产与能源,zh_CN

4,橡胶与塑料,zh_CN

5,机械与设备,zh_CN

6,照明与安防,zh_CN

7,电器与电子,zh_CN

8,包装与文教,zh_CN

9,纺织与皮革,zh_CN

10,服装与饰品,zh_CN

11,食品与农业,zh_CN

12,家居与百货,zh_CN

13,美妆与美容,zh_CN

14,车船与交通,zh_CN

15,医药与保健,zh_CN

16,商务与服务,zh_CN

二：把相应文件scp到目的服务器上，

[root@localhost tmp]# scp /tmp/liuwenhe.txt   oracle@192.168.4.225:/backup

oracle@192.168.4.225's password:

liuwenhe.txt                                                                                                                                  100%  391     0.4KB/s   00:00

You have new mail in /var/spool/mail/root

三：在目标服务器上利用sql lorder 进行数据导入。

1，在目标端创建好要目的表，字段和mysql的search_test_zh_cn.Industry保持一致。

SQL> create table INDUSTRY

2  (

3    industryid   INT,

4    industryname VARCHAR2(50),

5    langtype     VARCHAR2(20)

6  );

table  created

2，创建控制文件：

[oracle@hu225 backup]$ vi liuwenhe.ctl

load data

infile '/backup/liuwenhe.txt'

badfile '/backup/liu.bad'      ###插不进去的数据会进入bad文件里。如果都插进去了，就不会生成bad文件，

into table liuwenhe.Industry

fields terminated by ','

TRAILING NULLCOLS       ##最好有这个参数，保证有空的也能插入进去，指记录中没有内容(空格、空白、或null)的列被当作null 列

(INDUSTRYID,INDUSTRYNAME,LANGTYPE)

3,执行命令：

[oracle@hu225 backup]$ sqlldr liuwenhe/liuwenhe control=/backup/liuwenhe.ctl      log=/backup/liuwenhe.log

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 19 14:34:52 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 16

4，看执行的log文件。

[oracle@hu225 backup]$ cat liuwenhe.log

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 19 14:34:52 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   /backup/liuwenhe.ctl

Data File:      /backup/liuwenhe.txt

Bad File:     /backup/liu.bad

Discard File:  none specified

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array:     64 rows, maximum of 256000 bytes

Continuation:    none specified

Path used:      Conventional

Table LIUWENHE.INDUSTRY, loaded from every logical record.

Insert option in effect for this table: INSERT

TRAILING NULLCOLS option in effect

Column Name                  Position   Len  Term Encl Datatype

------------------------------ ---------- ----- ---- ---- ---------------------

INDUSTRYID                          FIRST     *   ,       CHARACTER

INDUSTRYNAME                         NEXT     *   ,       CHARACTER

LANGTYPE                             NEXT     *   ,       CHARACTER

Table LIUWENHE.INDUSTRY:

16 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array:                  49536 bytes(64 rows)

Read   buffer bytes: 1048576

Total logical records skipped:          0

Total logical records read:            16

Total logical records rejected:         0

Total logical records discarded:        0

Run began on Sun Jul 19 14:34:52 2015

Run ended on Sun Jul 19 14:34:52 2015

Elapsed time was:     00:00:00.06

CPU time was:         00:00:00.02

5.检查结果：

SQL> select count(*) from liuwenhe.Industry;

COUNT(*)

----------

16

迁移成功！！！

小节：[oracle@hu225backup]$ sqlldr liuwenhe/liuwenhe control=/backup/liuwenhe.ctl      log=/backup/liuwenhe.log 这个步骤中如果丢掉了log=/backup/liuwenhe.log 那么他会在你执行命令的当前目录下，这里就是/backup下生成名字和相应的控制文件对应的.log文件，如果你的控制文件是pp.ctl  那么自动生成pp.log 文件。badfile '/backup/liu.bad'  这个参数，插不进去的数据会进入bad文件里。如果都插进去了，就不会生成bad文件，因为数据的问题，导致进不去，那么会生成相应的.bad文件。默认64行数据就commit；如果你要想跳过某些列，可以用参数filler ,例如：你想加载的数据格式：1，liuwenhe,man,25

2,dashuai,man,28

3，helei,man,21

但是你不想要第三列，你可以这样写控制文件：

load data

infile '/backup/liuwenhe.txt'

badfile '/backup/liu.bad'

into table liuwenhe.Industry

fields terminated by ','

TRAILING NULLCOLS

(INDUSTRYID,INDUSTRYNAME,dummy1 filler ,LANGTYPE)     这样允许你映射一个输入记录中的一列，但不把他放在数据库中。也就是说虽然数据库中只有三个字段，但是真正的数据有四个字段，你可以通过dummy1 filler ， dummy2 filler.。。。这样有选择性的跳过一些不想要的列值。

insert：数据加载方式(默认)

加载方式有如下四种：

append：原先的表有数据就加在后面

insert：(默认值)装载空表,如果原先的表有数据SQLLOADER会停止

replace：原先的表有数据原先的数据会全部删除

truncate：指定的内容和REPLACE的相同会用TRUNCATE语句删除现存数据