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@hu225 backup]$ 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語句刪除現存資料

sqlldr 完成mysql到oracle的資料遷移

相關文章