使用sqlldr匯入文字資料到oracle

鬆門一枝花發表於2016-05-25
SQLOracle

1.sqlldr介紹

用法: SQLLDR keyword=value [,keyword=value,...]
 
有效的關鍵字:
 
    userid -- ORACLE 使用者名稱/口令       
   control -- 控制檔名               
       log -- 日誌檔名                   
       bad -- 錯誤檔名                  
      data -- 資料檔名                 
   discard -- 廢棄檔名
discardmax -- 允許廢棄的檔案的數目         (全部預設)
      skip -- 要跳過的邏輯記錄的數目  (預設 0)
      load -- 要載入的邏輯記錄的數目  (全部預設)
    errors -- 允許的錯誤的數目         (預設 50)
      rows -- 常規路徑繫結陣列中或直接路徑儲存資料間的行數
               (預設: 常規路徑 64, 所有直接路徑)
  bindsize -- 常規路徑繫結陣列的大小 (以位元組計)  (預設 256000)
    silent -- 執行過程中隱藏訊息 (標題,反饋,錯誤,廢棄,分割槽)
    direct -- 使用直接路徑                     (預設 FALSE)
   parfile -- 引數檔案: 包含引數說明的檔案的名稱
  parallel -- 執行並行載入                    (預設 FALSE)
      file -- 要從以下物件中分配區的檔案    
skip_unusable_indexes -- 不允許/允許使用無用的索引或索引分割槽  (預設 FALSE)
skip_index_maintenance -- 沒有維護索引, 將受到影響的索引標記為無用  (預設 FALSE)
commit_discontinued -- 提交載入中斷時已載入的行  (預設 FALSE)
  readsize -- 讀取緩衝區的大小               (預設 1048576)
external_table -- 使用外部表進行載入; NOT_USED, GENERATE_ONLY, EXECUTE  (預設 NOT_USED)
columnarrayrows -- 直接路徑列陣列的行數  (預設 5000)
streamsize -- 直接路徑流緩衝區的大小 (以位元組計)  (預設 256000)
multithreading -- 在直接路徑中使用多執行緒
 resumable -- 啟用或禁用當前的可恢復會話  (預設 FALSE)
resumable_name -- 有助於標識可恢復語句的文字字串
resumable_timeout -- RESUMABLE 的等待時間 (以秒計)  (預設 7200)
date_cache -- 日期轉換快取記憶體的大小 (以條目計)  (預設 1000)
no_index_errors -- 出現任何索引錯誤時中止載入  (預設 FALSE)
 
PLEASE NOTE: 命令列引數可以由位置或關鍵字指定
。前者的例子是 'sqlldr
scott/tiger foo'; 後一種情況的一個示例是 'sqlldr control=foo
userid=scott/tiger'。位置指定引數的時間必須早於
但不可遲於由關鍵字指定的引數。例如,
允許'sqlldr scott/tiger control=foo logfile=log', 但是
不允許'sqlldr scott/tiger control=foo log', 即使
引數'log' 的位置正確。

2.實戰

先有需求：

將000000_0.txt檔案匯入oracle資料庫

000000_0.txt大小：123MB，幾十萬行

000000_0.txt內容格式：

20160514|未知|未知|未知|3G|057431041116|55349|41116|28173301261774|460007863660008|1.224609375|13.0|\N|\N
20160514|HTC|手機|D728w|3G|057431001157|55097|1157|35153907023514|460006620667069|0.513671875|10.0|\N|\N
20160514|HTC|手機|D728w|3G|057431043861|55084|43861|35153907033487|460027685036740|2937.208984375|8052.0|\N|\N
20160514|HTC|手機|D728w|3G|057431003011|55084|3011|35153907033487|460027685036740|1.755859375|56.0|\N|\N


匯入的資料庫表結構：


create table TEST201605
(
  SHI_JIAN,
    ZDCS,
    ZDFL,
    ZDXH,
    XQWLLX,
    XQWYBH,
    LAC,
    CI,
    IMEI,
    IMSI,
    THREEG_LL,
    THREEG_SZ,
    FOURG_LL
)



2.1編寫控制檔案 load.ctl：

load data
CHARACTERSET UTF8  //檢視下檔案的編碼，設為同一個編碼
infile "E:\datafile\000000_0.txt"   //只能一個個匯入，可以把多個檔案合併到一個裡，然後匯入
append
into table TEST201605
fields terminated by '|'
trailing nullcols
(
    --"時間",
    SHI_JIAN,
    ZDCS,
    ZDFL,
    ZDXH,
    XQWLLX,
    XQWYBH,
    LAC,
    CI,
    IMEI,
    IMSI,
    THREEG_LL,
    THREEG_SZ,
    FOURG_LL
)


2.2 windows下

dos命令窗，cd 到load.ctl所在目錄

使用命令：

sqlldr daily/mdasil@daily-74 control=load.ctl log=log.log bad=bad.log errors=5000 rows=1000 bindsize=10485760


linux：


load data
CHARACTERSET UTF8
infile "/datafile-import/000021_0.txt"
append
into table jingfen_201605_1415
fields terminated by '|'
trailing nullcols
(
    SHI_JIAN,
    ZDCS,
    ZDFL,
    ZDXH,
    XQWLLX,
    XQWYBH,
    LAC,
    CI,
    IMEI,
    IMSI,
    THREEG_LL,
    THREEG_SZ,
    FOURG_LL
)


oracle使用者下執行：


sqlldr daily/mdasil control=/datafile-import/ctl/44.ctl log=/datafile-import/log/log20160526.log  bad=/datafile-import/log/bad20160526.log


rows與bindsize相關，rows預設64行，如果不修改bindsize，只修改rows無效。

bindsize --（ 每次提交記錄的緩衝區的大小，位元組為單位，預設256000)


dos視窗會列印：

達到提交點 - 邏輯記錄計數 441884
達到提交點 - 邏輯記錄計數 441954
達到提交點 - 邏輯記錄計數 442024
達到提交點 - 邏輯記錄計數 442094


sqllder 匯入的時候，不支援中文列名，因為檔案裡的都是string，匯入的表欄位也要 字串 型別 。

（可以控制型別，但是如果資料內容格式不對，匯入出錯就比較麻煩，還是以string文字匯入後，再修改表結構）


相關文章