http://blog.chinaunix.net/uid-23284114-id-3259752.html

日常中，大量有格式的資料存放在平面檔案中，如csv、txt中。透過sql*loader可以很方便匯入到oracle資料庫中。

先看一個例子，把平面檔案test_action.txt中的資料匯入到test_action表中。

test_action.txt

--------

"2012-06-25 00:03:01","5D8969C289594C4FE76188066C4D72C4","official","newwap","boutique_product","0","140","x39x0xxx89x3x","S60V52","45"

--------

test_action表結構

控制檔案

test_action.ctl

----

load data

CHARACTERSET UTF8

infile '/home/oracle/test_action.txt'

append into table scott.test_p2sp

fields terminated by ","

optionally enclosed by '"'

(LOG_TIME date "yyyy-mm-dd hh24:mi:ss",MD5ID,CHANNEL,SUB_CHANNEL,ACTION_TYPE,STATUS,VER,IMEI,MBL,PRODUCT_ID )

----

匯入語句

$ sqllder user/password errors=1000 control='/home/oracle/test_action.ctl'

一、sql*loader的組成

1.命令

sqlldr user/password errors=n control='/xx/xx/xx/xx.ctl' log='/xx/xx.log' bad='/xx/xx.bad'

--errors：允許失敗的最大行數，預設50。超過errors sqlldr自動停止。

--control：控制檔案

--log：日誌，不指定也可，預設在執行sqlldr命令所在的目錄。

--bad：記錄匯入失敗的資料，預設在執行sqlldr命令所在的目錄。

2.平面檔案

平面檔案中存放有規律的資料，比如以","逗號作為列之間的分割，各列以雙引號""包圍。

如下：

"2012-06-25 00:03:01","5D8969C289594C4FE76188066C4D72C4","official","newwap","boutique_product","0","140","x39x0xxx89x3x","S60V52","45"

3.控制檔案.ctl

也就是上面命令中的control=。控制檔案時平面檔案與資料庫表之間的橋樑。控制檔案指定平面檔案各列對應表中的哪些列。

如:

load data

CHARACTERSET UTF8

infile '/home/oracle/test_action.txt'

append into table scott.test_p2sp

fields terminated by ","

optionally enclosed by '"'

(LOG_TIME date "yyyy-mm-dd hh24:mi:ss",MD5ID,CHANNEL,SUB_CHANNEL,ACTION_TYPE,STATUS,VER,IMEI,MBL,PRODUCT_ID )

二、常用引數

sqlldr help=y可以檢視所有引數

DIRECT:直接路徑載入，它直接將資料寫入到database block中，大大加速了匯入速度。預設值是false。使用direct=true，表中的觸發器失效。

PARALLEL:並行載入。預設值是false。

rows:多少行提交一次

bindsize: 每次提交記錄(依據rows引數)的緩衝區的大小，預設255420 bytes，一般設定為20971520(20M)，目前還沒測出最大值。與rows配合使用。在conventional path load（一般方式)下有效。bindsize制約rows，如果rows提交的資料，

即行*行長>bindsize，那麼還是以bindsize為準。所以增大rows，一定要考慮bindsize是否夠用。

readsize:讀取緩衝區的大小 (預設 1048576),一般設定成20971520(20M)。readsize負責讀，而bindsize負責提交。如果readsize小於bindsize，那麼readsize會自動增加。

三、加速匯入

1.表改為nologging

執行sqlldr命令之前，最好把相關表的logging模式改為nologging，這樣會減少redo日誌產生，同時也就加速了匯入的速度。導完後再改為logging模式。

alter table table_name nologging;

alter table table_name logging;

2.PARALLEL

在命令中加入parallel=true

並行技術

3.DIRECT

使用direct path load(直接路徑載入)，觸發器會失效。

有效的約束有：

NOT NULL

UNIQUE

PRIMARY KEY (unique-constraints on not-null columns)

失效的約束：

CHECK constraints

Referential constraints (FOREIGN KEY)

當然，也可以parallel=true+direct=true

4.rows

rows=n，匯入n行提交一次。

5.bindsize:

和rows結合使用，決定一次提交的行數。

6.readsize

四、控制檔案引數

load data

CHARACTERSET UTF8

infile '/home/oracle/test_action.txt'

append into table scott.test_p2sp

fields terminated by ","

optionally enclosed by '"'

(LOG_TIME date "yyyy-mm-dd hh24:mi:ss",MD5ID,CHANNEL,SUB_CHANNEL,ACTION_TYPE,STATUS,VER,IMEI,MBL,PRODUCT_ID )

以上面為例：

1.CHARACTERSET :字符集,如UTF8

infile 平面檔案所在位置，這裡可以寫多個infile，也就是多個平面檔案匯入到一張表裡

infile '/home/oracle/1.csv'

infile '/home/oracle/2.csv'

2.xx into table:append into --追加

truncate into --把表的資料都刪除，然後匯入

insert into --空表可以採用insert

3.fields terminated by "," :以逗號作為分隔

4.optionally enclosed by '"':列被雙引號包圍

5.函式

（1）日期轉換：平面檔案中

"2012-06-25 00:03:01",

那麼ctl中，要根據平面檔案日期格式在ctl中標明

LOG_TIME date "yyyy-mm-dd hh24:mi:ss",

（2）upper，轉成大寫

格式:col1 "upper(:col1)"

如：ACTION_TYPE "upper(:action_type)"

如果表中某列的型別為varchar2(300),那麼在ctl中就要寫成char(300)

SQLLDR預設輸入流為資料型別為CHAR 長度為 255 所以當 begindata section裡面的輸入流長於255 的時候會報錯的

五、sqllder的兩種方式

Direct path load和conventional path load。

從這裡可以明顯看出Direct path load 與（常用方式）的區別

1.Conventional Path Load

使用sql insert方式插入資料。

特點：

（1）適用表中含有索引

（2）適用簇表：load data to a clustered table。direct path load不支援load data to a clustered table簇表。

（3）滿足所有約束constraint，如索引、not null等。

（5）你想知道哪些資料被rejected，資訊記錄在sqlldr的log和bad檔案中。

（6）觸發表中觸發器

（7）在插入時，別的使用者可以操作該表

2.Direct Path Load

直接把資料寫到database block中，效率比Conventional Path Load高得多。Direct Path Load不經過oracle的sql這一層，也沒有parseing、executing這些過程，對於oracle資料庫的整個負載都是一個減輕。插入資料時，只是在表的HWM之上直接重新分配新的資料塊，而不是在freelist中查詢可以insert 的塊。

注意：direct path load方式也會產生redo，因為插入資料時會分配新extend，oracle必須記錄這些redo資訊。

特點：

（1）direct path load不是滿足所有的constraint，只滿足primary key、unique、not null

（2）不能插入cluster table

（3）direct path load，在插入時，別的使用者不能操作該表（不能進行DML操作，不能建立、重建索引，甚至查詢）

（4）不會觸發觸發器

（5）適用於將大量資料匯入到空表或者非空表，這個表最好不要含有觸發器、參考約束。

Conventional Path Load（傳統匯入）最佳化實驗：

1.rows=10000,10000行提交一次

$ sqlldr scott/tiger errors=1000000 control='/home/oracle/test_action.ctl' rows=10000

##Space allocated for bind array: 255420 bytes(99 rows)

##Elapsed time was: 00:03:05.90

#這裡rows=10000,而實際是99 rows提交一次，原因在於bindsize

2.rows=10000,10000行提交一次，加大bindsize

$ sqlldr scott/tiger errors=1000000 control='/home/oracle/test_action.ctl' rows=10000 bindsize=20971520

##Space allocated for bind array: 20970240 bytes(8128 rows) //依然bindsize

##Elapsed time was: // 00:00:52.51 效率大大提高

#提示specified value for readsize(1048576) less than bindsize(20971520)

解決辦法：可以忽略，或者設定readsize和bindsize等值（意義不大，當readsize

3.rows=10000,10000行提交一次，繼續加大bindsize

$ sqlldr scott/tiger errors=1000000 control='/home/oracle/test_action.ctl' rows=10000 bindsize=25800000

##Space allocated for bind array: 25800000 bytes(10000 rows)//終於可以一次提交10000行資料了

##Elapsed time was: 00:00:50.37 // 增大bindsize後，效果有所提高

4.row=10000,10000行提交一次,新增readsize

$ sqlldr scott/tiger errors=1000000 control='/home/oracle/p2sp_action.ctl' rows=10000 readsize=25800000 bindsize=25800000

##Space allocated for bind array: 25800000 bytes(10000 rows)

##Elapsed time was: 00:00:49.49 //新增readsize引數後，效率有所提高

5.rows=10000,10000行提交一次，新增parallel=true

$ sqlldr scott/tiger errors=1000000 control='/home/oracle/test_action.ctl' rows=10000 readsize=25800000 bindsize=25800000 parallel=true

##Space allocated for bind array: 25800000 bytes(10000 rows)

##Elapsed time was: 00:00:48.86 //效率又提高了

總結：rows+bindsize+readsize是傳統匯入最優方案。

Direct Path load實驗：

$ sqlldr scott/tiger@67 errors=1000000 control='/home/oracle/test_action.ctl' direct=true

##Elapsed time was: 00:00:06.54//速度驚人啊！

遠端sqlldr匯入資料

在A伺服器上有csv檔案，要匯入到B庫中

用sqlldr+tnsnames.ora就可以實現

1.首先要在A伺服器安裝oracle client，client版本最好和DB版本相同

如果A伺服器沒有oracle DB，那麼安裝oracle client來獲得sqlldr命令，安裝的時候選擇自定義安裝，選擇oracle database utilties和oracle net兩項即可。

2.A庫配置tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/product/11.1.0/client/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

B =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.67)(PORT = 1521))

)

(CONNECT_DATA =

(SID = hou)

)

3.A伺服器上執行sqlldr命令，csv資料和ctl控制檔案都在A伺服器

$ sqlldr scott/tiger@67 errors=10 control='/home/oracle/test.ctl'

SQL*Loader 常用知識

相關文章