2.1 基本知識

的 SQL* LOADER 可以將外部格式化的文字資料載入到資料庫表中。通常與 SPOOL匯出文字資料方法配合使用。

1.命令格式

SQLLDR keyword=value [，keyword=value，……]

例：$ sqlldr user/pwd control=emp.ctl data=emp.dat bad=emp.bad log=emp.log

2.控制檔案

SQL*LOADER 根據控制檔案可以找到需要載入的資料。並且分析和解釋這些資料。

控制檔案由三個部分組成，具體引數參考幫助文件：1. 全域性選件，行，跳過的記錄數等；2. INFILE 子句指定的輸入資料；3. 資料特性說明。

comment： ——註釋

例：

load data infile *

append ——除了 append外，還有 insert、replace、truncate等方式

into table emp fields terminated b y ‘|’

（

no float external， name char（20），

age integer external，

duty char（1），salary float external，

upd_ts date（14） ‘YYYYMMDDHH24MISS’）

begindata

100000000003|Mulder|000020|1|000000005000|20020101000000

100000000004|Scully|000025|2|000000008000|20020101235959

控制檔案中infile選項跟sqlldr 命令列中data 選項含義相同，如使用infile *則表明資料在本控制檔案以 begin data 開頭的區域內。一些選項：FIELDS TERMINATED BY WHITESPACE FIELDS TERMINATED BY x'09' FILLER_1 FILLER， // 指定某一列將不會被裝載

DEPTNO position（1：2）， DNAME position（*：16）， // 指定列的位置SEQNO RECNUM //載入每行的行號

SKIP n // 指定匯入時可以跳過多少行資料

3.資料檔案

按控制檔案資料格式定義的資料行集，

例：

100000000001|Tom|000020|1|000000005000|20020101000000

100000000002|Jerry|000025|2|000000008000|20020101235959

固定格式、可變格式、流記錄格式：

固定格式：

當資料固定的格式（長度一樣）時且是在檔案中得到時，要用 INFILE "fix n"

load data

infile 'example.dat' "fix 11"

into table example

fields terminated b y '，' optionally enclosed by '"'

（col1 char（5）， col2 char（7）） example.dat：

001， cd， 0002，fghi，

00003，lmn，

1， "pqrs"，

0005，uvwx，

可變格式：

當資料是可變格式（長度不一樣）時且是在檔案中得到時，要用 INFILE "var n".如：

load data

infile 'example.dat' "var 3"

into table example

fields terminated b y '，' optionally enclosed by '"'

（col1 char（5）， col2 char（7）） example.dat：

009hello，cd，010world，im，

012my，name is，

流記錄格式： // Stream-recored format：load data infile 'xx.dat' "str '|\n'"

into table xx field terminated b y '，' optionally enclosed by '"'

（col1 char（5）， col2 char（7））

example.dat：

hello， ccd，|

world， bb，|

4. 壞檔案

bad=emp.bad壞檔案包含那些被 SQL*Loader拒絕的記錄。被拒絕的記錄可能是不符合要求的記錄。

5. 日誌檔案及日誌資訊

log=emp.log當 SQL*Loader 開始執行後，它就自動建立日誌檔案。日誌檔案包含有載入的總結，載入中的錯誤資訊等。

2.2 高階選項

1. Conventional Path Load與Direct Path Load

Conventional-path Load：透過常規通道方式上載。

特點：commit， always gen redo logs， enforce all constraints， fire insert triggers， can load into cluster， other user can make change

rows：每次提交的記錄數

bindsize：每次提交記錄的緩衝區

readsize：與 bindsize 成對使用，其中較小者會自動調整到較大者

sqlldr 先計算單條記錄長度，乘以 rows，如小於 bindsize，不會試圖擴張，rows以填充 bindsize；如超出，則以 bindsize 為準。命令為：

$ sqlldr dbuser/oracle control=emp.ctl log=emp.log rows=10000 bindsize=8192000

Direct-Path Load：

透過直通方式上載，可以跳過資料庫的相關邏輯，不進行 SQL解析，而直接將數據匯入到資料檔案中。

特點：save， conditionly gen redo logs， enforce PK UK NN， not fire triggers， can not load into cluster， other user can not make change命令為：

$ sqlldr dbuser/oracle control=emp.ctl log=emp.log direct=true

2. SPOOL匯出文字資料方法

匯入的資料檔案可以用 SPOOL匯出文字資料方法生成。

SQL*PLUS環境設定

SET NEWPAGE NONE HEADING OFF SPACE 0

PAGESIZE 0 SET TRIMOUT ON TRIMSPOOL ON LINESIZE 2500

注：LINESIZE 要稍微設定大些，免得資料被截斷，它應和相應的 TRIMSPOOL結合使用防止匯出的文字有太多的尾部空格。

但是如果 LINESIZE 設定太大，會大大降低匯出的速度，另外在 WINDOWS下導出最好不要用 PLSQL匯出，速度比較慢，直接用 COMMEND 下的 SQLPLUS命令最小化視窗執行。對於欄位內包含很多回車換行符的應該給與過濾，形成比較規矩的文字檔案。

通常情況下，我們使用 SPOOL方法，將資料庫中的表匯出為文字檔案，如下述：

set trimspool on

set linesize 120 pagesize 2000 newpage 1 heading off term off spool 路徑+檔名

select col1||'，'||col2||'，'||col3||'，'||col4||'……' from tablename；

spool off

2.3 指令碼

1. 將表中資料記錄匯出為欄位值用分隔符'|'分開的。dat檔案

#！/bin/ksh

##################################################################

## 名稱： unloadtable

## 功能：本 shell 用於將表中資料記錄匯出

## 匯出為欄位值用分隔符'|'分開的。dat檔案

## 編者：

## 日期： 2006.03.18

##################################################################

if [ $# -ne 3 ]

then echo "usage：unloadtable tablename username password."

exit 0

fi

##準備工作

echo "set heading off " >/tmp/$1.col

echo "set pagesize 0" >>/tmp/$1.col

echo "set linesize 800 " >>/tmp/$1.col

echo "set feedback off " >>/tmp/$1.col

echo "set tab off " >>/tmp/$1.col

echo "select column_name||'，' from user_tab_columns where lower（table_name）='$1' order by

column_id； " >> /tmp/$1.col

##產生 select 語句

echo "set heading off " >/tmp/$1.sel

echo "set pagesize 0" >>/tmp/$1.sel

echo "set linesize 800 " >>/tmp/$1.sel

echo "set feedback off " >>/tmp/$1.sel

echo "set tab off " >>/tmp/$1.sel

echo "select " >>/tmp/$1.sel

echo `sqlplus -s $2/$3

>>/tmp/$1.sel

##生成 dat檔案

#echo "from $1；\n/" >>/tmp/$1.sel 由於 / 導致多執行一次 select

echo "from $1；\n" >>/tmp/$1.sel

sqlplus -s $2/$3 < /tmp/$1.sel >$1_tmp.dat

#awk '{if（FNR！=1） print $0}' $1_tmp.dat >$1.dat FNR 選項使得第一條記錄選不出

awk '{print $0}' $1_tmp.dat >$1.dat

rm -f $1_tmp.dat

2. 將資料匯入到相應表中

#！/bin/ksh

##################################################################

## 名稱：loadtable

## 功能：本 shell 用於將已經準備好的。dat資料檔案匯入相應的表中

## .dat 檔案各個欄位值用分隔符'|'分開。

## 編者：

## 日期： 2006.03.18

##################################################################

if [ $# -ne 3 ]

then

echo "usage：loadtable tablename username password." exit 0 fi

##準備工作

echo "set heading off " >/tmp/$1.colsql

echo "set pagesize 0" >>/tmp/$1.colsql

echo "set linesize 800 " >>/tmp/$1.colsql

echo "set feedback off " >>/tmp/$1.colsql

echo "set tab off " >>/tmp/$1.colsql

echo "select column_name||'，' from user_tab_columns where lower（table_name）='$1' order by

column_id； " >> /tmp/$1.colsql

##產生 ctl檔案

echo "load data" >/tmp/$1.ctl

echo "infile *" >>/tmp/$1.ctl

echo "into table $1" >>/tmp/$1.ctl

echo "fields terminated by '|'" >>/tmp/$1.ctl

echo `sqlplus -s $2/$3 < /tmp/$1.colsql` |sed "s/，$/）/g" |sed "s/^/（/g" >>/tmp/$1.ctl

##開始匯入資料

echo "truncate table $1；" >/tmp/$1.sql

sqlplus $2/$3 < /tmp/$1.sql

sqlldr $2/$3 data=$1.dat control=/tmp/$1.ctl log=/tmp/$1.log

簡述資料庫匯入Oracle SQL*Loader指南:

SQL*Loader是資料庫匯入外部資料的一個工具。它和DB2的Load工具相似，但有更多的選擇，它支援變化的載入模式，可選的載入及多表載入。

如何使用 SQL*Loader 工具

我們可以用的sqlldr工具來匯入資料。例如:

sqlldr scott/tiger control=loader.ctl

控制檔案(loader.ctl) 將載入一個外部資料檔案（含分隔符)loader.ctl如下:

load data 
infile 'c:\data\mydata.csv' 
into table emp 
fields terminated by "," optionally enclosed by '"' 
      ( empno, empname, sal, deptno )

mydata.csv 如下:

10001,"Scott Tiger", 1000, 40 
      10002,"Frank Naude", 500, 20

下面是一個指定記錄長度的示例控制檔案。"*" 代表資料檔案與此檔案同名，即在後面使用BEGINDATA段來標識資料。

load data 
infile * 
replace 
into table departments 
( dept position (02:05) char(4), 
deptname position (08:27) char(20) 
) 
begindata 
COSC COMPUTER SCIENCE 
ENGL ENGLISH LITERATURE 
MATH MATHEMATICS 
      POLY POLITICAL SCIENCE

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等。

載入可變長度或指定長度的記錄：

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 
      oneEgby having a '#' character in column 1

SQL*Loader 資料的提交

一般情況下是在匯入資料檔案資料後提交的。也可以透過指定 ROWS= 引數來指定每次提交記錄數。

提高 SQL*Loader　的效能：

(1) 一個簡單而容易忽略的問題是，沒有對匯入的表使用任何索引和/或約束(主鍵)。如果這樣做，甚至在使用ROWS=引數時，會很明顯降低資料庫匯入效能。

(2) 可以新增 DIRECT=TRUE來提高匯入資料的效能。當然，在很多情況下，不能使用此引數。

(3) 透過指定　UNRECOVERABLE選項，可以關閉資料庫的日誌。這個選項只能和 direct 一起使用。

(4) 可以同時執行多個匯入任務。

常規匯入與direct匯入方式的區別：

常規匯入可以透過使用 INSERT語句來匯入資料。Direct匯入可以跳過資料庫的相關邏輯(DIRECT=TRUE)，而直接將資料匯入到資料檔案中。

Oracle資料庫備份與恢復之二：SQL*Loader

相關文章