Oracle SQL Loader

 

一：SQL Loader 的特點
oracle自己帶了很多的工具可以用來進行資料的遷移、備份和恢複等工作。但是每個工具都有自己的特點。
比如說exp和imp可以對資料庫中的資料進行匯出和匯出的工作，是一種很好的Database Backup和恢複的工具，因此主要用在資料庫的熱備份和恢複方面。有著速度快，使用簡單，快捷的優點；同時也有一些缺點，比如在不同版本資料庫之間的匯出、匯入的過程之中，總會出現這樣或者那樣的問題，這個也許是oracle公司自己產品的相容性的問題吧。
sql loader 工具卻沒有這方面的問題，它可以把一些以文字格式設定存放的資料順利的匯入到oracle資料庫中，是一種在不同資料庫之間進行資料移轉的非常方便而且通用的工具。缺點就速度比較慢，另外對blob等類型的資料就有點麻煩了。

二. sqlldr 協助：
C:/Documents and Settings/David>sqlldr
SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 7月 2 08:54:06 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
用法: 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  (預設 NO
T_USED)
columnarrayrows -- 直接路徑列數組的行數  (預設 5000)
streamsize -- 直接路徑流緩衝區的大小 (以位元組計)  (預設 256000)
multithreading -- 在直接路徑中使用多線程
resumable -- 啟用或禁用當前的可恢複會話  (預設 FALSE)
resumable_name -- 有助於標識可恢複語句的文本字串
resumable_timeout -- RESUMABLE 的等待時間 (以秒計)  (預設 7200)
date_cache -- 日期轉換快取的大小 (以條目計)  (預設 1000)
PLEASE NOTE: 命令列參數可以由位置或關鍵字指定
。前者的例子是 'sqlload
scott/tiger foo'; 後一種情況的一個樣本是 'sqlldr control=foo
userid=scott/tiger'.位置指定參數的時間必須早於
但不可遲於由關鍵字指定的參數。例如,
允許 'sqlldr scott/tiger control=foo logfile=log', 但是
不允許 'sqlldr scott/tiger control=foo log', 即使
參數 'log' 的位置正確。

三， SQL LOADER 執行個體

建立表：test_load，
sql>create table test_load(id number(10),uname varchar2(20), phone varchar2(20));
控制檔案：d:/loader.ctl
load data
infile 'd:/datafile.txt'
append into table test_load
fields terminated by "," optionally enclosed by '"'
( id, uname, phone )

資料檔案：D:/datafile.txt
001,"david",0551-1234567
002,"dave",0551-7654311
sqlloader命令：
SQL>sqlldr 'sys/admin AS SYSDBA' control=D:/loader.ctl log=d:/a.log
SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 7月 2 09:40:42 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
達到提交點 - 邏輯記錄計數 1
達到提交點 - 邏輯記錄計數 2
SQL> select * from test2;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
     10002 Frank             500         20
     10001 Scott            1000         40

四， 控制檔案詳解
   載入可變長度或指定長度的記錄，"*" 代表資料檔案與此檔案同名，即在後面使用BEGINDATA段來標識資料。
　　如：
　　LOAD DATA
　　INFILE *
　　INTO TABLE load_delimited_data
　　FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
　　TRAILING NULLCOLS
　　( data1,
　　data2
　　)
　　BEGINDATA
　　11111,AAAAAAAAAA
　　22222,"A,B,C,D,"
　　下面是匯入固定位置（固定長度）資料樣本：
　　LOAD DATA
　　INFILE *
　　INTO TABLE load_positional_data
　　( data1 POSITION(1:5),
　　data2 POSITION(6:15)
　　)
　　BEGINDATA
　　11111AAAAAAAAAA
　　22222BBBBBBBBBB
　　跳過資料行：
　　可以用 "SKIP n" 關鍵字來指定匯入時可以跳過多少行資料。如：
　　LOAD DATA
　　INFILE *
　　INTO TABLE load_positional_data
　　SKIP 5
　　( data1 POSITION(1:5),
　　data2 POSITION(6:15)
　　)
　　BEGINDATA
　　11111AAAAAAAAAA
　　22222BBBBBBBBBB
　　匯入資料時修改資料：
　　在匯入資料到資料庫時，可以修改資料。注意，這僅適合於常規匯入，並不適合 direct匯入方式.如：
　　LOAD DATA
　　INFILE *
　　INTO TABLE modified_data
　　( rec_no "my_db_sequence.nextval",
　　region CONSTANT '31',
　　time_loaded "to_char(SYSDATE, 'HH24:MI')",
　　data1 POSITION(1:5) ":data1/100",
　　data2 POSITION(6:15) "upper(:data2)",
　　data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
　　)
　　BEGINDATA
　　11111AAAAAAAAAA991201
　　22222BBBBBBBBBB990112
　　LOAD DATA
　　INFILE 'mail_orders.txt'
　　BADFILE 'bad_orders.txt'
　　APPEND
　　INTO TABLE mailing_list
　　FIELDS TERMINATED BY ","
　　( addr,
　　city,
　　state,
　　zipcode,
　　mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
　　mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
　　mailing_state
　　)
　　將資料匯入多個表：
　　如:
　　LOAD DATA
　　INFILE *
　　REPLACE
　　INTO TABLE emp
　　WHEN empno != ' '
　　( empno POSITION(1:4) INTEGER EXTERNAL,
　　ename POSITION(6:15) CHAR,
　　deptno POSITION(17:18) CHAR,
　　mgr POSITION(20:23) INTEGER EXTERNAL
　　)
　　INTO TABLE proj
　　WHEN projno != ' '
　　( projno POSITION(25:27) INTEGER EXTERNAL,
　　empno POSITION(1:4) INTEGER EXTERNAL
　　)
　　匯入選定的記錄：
　　如下例： (01) 代表第一個字元, (30:37) 代表30到37之間的字元:
　　LOAD DATA
　　INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
　　APPEND
　　INTO TABLE my_selective_table
　　WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
　　(
　　region CONSTANT '31',
　　service_key POSITION(01:11) INTEGER EXTERNAL,
　　call_b_no POSITION(12:29) CHAR
　　)
　　匯入時跳過某些欄位：
　　可用 POSTION(x:y) 來分隔資料. 在Oracle8i中可以通過指定 FILLER 欄位實現。FILLER 欄位用來跳過、忽略匯入資料檔案中的欄位.如：
　　LOAD DATA
　　TRUNCATE INTO TABLE T1
　　FIELDS TERMINATED BY ','
　　( field1,
　　field2 FILLER,
　　field3
　　)
　　匯入多行記錄：
　　可以使用下面兩個選項之一來實現將多行資料匯入為一個記錄:
　　CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
　　CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.

五， 補充知識
　　Unloader這樣的工具，Oracle 沒有提供將資料匯出到一個檔案的工具。但是，我們可以用SQL*Plus的select 及 format 資料來輸出到一個檔案：
　　set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
　　spool oradata.txt
　　select col1 || ',' || col2 || ',' || col3
　　from tab1
　　where col2 = 'XYZ';
　　spool off
　　另外，也可以使用使用 UTL_FILE PL/SQL 包處理:
　　rem Remember to update initSID.ora, utl_file_dir='c:/oradata' parameter
　　declare
　　fp utl_file.file_type;
　　begin
　　fp := utl_file.fopen('c:/oradata','tab1.txt','w');
　　utl_file.putf(fp, '%s, %s/n', 'TextField', 55);
　　utl_file.fclose(fp);
　　end;
　　
　　當然你也可以使用第三方工具，如SQLWays ,TOAD for Quest等。

　　SQL*Loader 資料的提交：
　　一般情況下是在匯入資料檔案資料後提交的。
　　也可以通過指定 ROWS= 參數來指定每次提交記錄數。
　　提高 SQL*Loader 的效能：
　　1) 一個簡單而容易忽略的問題是，沒有對匯入的表使用任何索引和/或約束(主鍵)。如果這樣做，甚至在使用ROWS=參數時，會很明顯降低資料庫匯入效能。
　　2) 可以添加 DIRECT=TRUE來提高匯入資料的效能。當然，在很多情況下，不能使用此參數。
　　3) 通過指定 UNRECOVERABLE選項，可以關閉資料庫的日誌。這個選項只能和 direct 一起使用。
　　4) 可以同時運行多個匯入任務.
　　常規匯入與direct匯入方式的區別：
　　常規匯入可以通過使用 INSERT語句來匯入資料。Direct匯入可以跳過資料庫的相關邏輯(DIRECT=TRUE)，而直接將資料匯入到資料檔案中。