Oracle sqlldr

當有大量資料要匯入資料庫時, 也可以選擇使用sqlldr工具來完成, 好處如下,

• 如果是降級匯入資料(11g to 9i), 就沒辦法使用exp/imp完成
• 使用insert語法, 資料量大時執行要很久

以前我都是很認命的自己寫 control file, 後來發現Oracle SQL Developer很人性化的可以產生control file和data file, 方法如下,

1. 登入Oracle SQL Developer後, 找到要處理的Table

2. 點右鍵選擇[匯出]功能

3. 設定相關欄位

• 格式: 選擇loader
• 分隔符號: 選擇資料要分隔的符號, 常用 pipe
• 行結束字元: 每筆資料分隔(換行)的符號
• 另存新檔: 使用loader時要選擇個別檔案, 因為會產生control file(ctl)和data file (ldr)

 

4. 下一步後, 如果需要限制資料條件, 則在對應的Table右方輸入Where條件

5. 下一步後, 按下完成就會開始產生檔案

6. Control File (*.ctl)

OPTIONS (ERRORS=50)
LOAD DATA
INFILE 'D:\ABC.ldr' "str '\r\n'"
APPEND
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE "MYDB"."ABC"
FIELDS TERMINATED BY'|'
OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS ( 
"ABC01" ,
"ABC02" ,
"ABC03" ,
"ABC04" ,
"ABC05" ,
"ABC06" ,
"ABC07" ,
"ABC08" ,
"ABC09" ,
"ABC10" ,
"ABC11" ,
"ABC12" ,
"ABC13" ,
"ABC14" ,
"ABC15" ,
"ABC16" ,
"ABC17" ,
"ABC18" ,
"ABC19" ,
"ABCDATE" DATE "DD-MON-RR" ,
"ABC20" )

7. Data File (*.ldr)

 "AAA"|"A01"||||"001"|||"1"|"S"|"Y"|"Y"|"N"|0|0|"Y"|"512"||"fff"|26-6月 -09||
 "BBB"|"B01"|||||||"1"|"S"|"Y"|"N"|"N"|0|0|"Y"|"589"||"44"|02-8月 -06||
 "CCC"|"C01"||||"002"|||"1"|"S"|"Y"|"Y"|"N"|0|0|"Y"|"ffg"||"alex"|01-11月-13||
 "DDD"|"D01"||||"052"|||"1"|"S"|"Y"|"Y"|"N"|0|0|"Y"|"sdf"||"123"|24-6月 -10||

8. 執行下列指令就可以匯入資料了

sqlldr userid/password@dbtnsname control=d:\aaa.ctl

** 指定control檔案路徑時記得要給完整的路徑
** control file中指定的data file路徑是依據匯出時選擇的路徑產生的, 如果檔案產生後有移動位置, 記得要改, 否則會找不到
** contorl file中會依據匯出的schema指預設匯入相同的schema, 如果要匯入不同schema記得要修改