【移動資料】SQL*Loader

不一樣的天空w發表於2016-10-18
SQL

SQL*Loader
SQL*LOADER是ORACLE的資料載入工具，通常用來將作業系統檔案遷移到ORACLE資料庫中，其載入的檔案為文字檔案，可以檢視！！！
1. 常用引數

引數名稱

含義

userid

ORACLE username/password

control

控制檔案

log

記錄的日誌檔案

bad

壞資料檔案

data

資料檔案

discard

丟棄的資料檔案

discardmax

允許丟棄資料的最大值(全部預設)

skip

Number of logical records to skip(預設 0)

load

Number of logical records to load(全部預設)

errors

允許的錯誤記錄數(預設 50)

rows

Number of rows in conventional path bind array or between
direct path data saves（每次提交的記錄數，預設: 常規路徑 64, 所有直接路徑）

bindsize

Size of conventional path bind array in bytes(預設 256000)每次提交記錄的緩衝區的大小(位元組為單位，預設 256000)

silent

禁止輸出資訊 (header,feedback,errors,discards,partitions)

direct

使用直通路徑方式匯入(預設 FALSE)

parfile

parameter file: name of file that contains parameter

specifications

parallel

並行匯入 (預設 FALSE)



 

注 在作業系統中直接輸入 sqlldr 即可檢視相關引數的幫助資訊 [oracle@ocmu ~]$ sqlldr


2. 控制檔案基本格式
load data
infile 't_01.dat' -- 要匯入的資料檔名稱
--infile 't_02.dat' -- 如果是多個資料檔案，那麼可以在此處寫入多個
--infile * -- 要匯入的內容就在 CONTROL 檔案裡, BEGINDATA 後面就是匯入的內容(與
上面格式不能同時使用)
insert： 資料載入方式（預設）
載入方式有如下四種：
append：原先的表有資料就加在後面
insert：（預設值）裝載空表,如果原先的表有資料 SQLLOADER 會停止
replace：原先的表有資料原先的資料會全部刪除
truncate：指定的內容和 REPLACE 的相同會用 TRUNCATE 語句刪除現存資料
badfile 'bf_name.bad'：指定出現錯誤的記錄存放的位置及名稱。如果此引數沒有指定，那麼預設會
在控制檔案同目錄下生成一個與存放資料的檔案同名的且字尾為 bad 的檔案。
fields terminated by ',' optionally enclosed by '"'
轉載的資料格式為，以','分隔的資料，且以'"'來標識一個欄位的起始。主要是因為，在平文字檔案中，
有可能出現帶逗號的欄位，那樣， sqlloader 會誤以為，那個逗號為分隔符，導致 load 的資料是錯誤的。
注：此引數可以宣告也可以不宣告，如果沒宣告，那麼需要在定義欄位的地方宣告用什麼來區分。
trailing nullcols：允許出現空值，當平文字檔案中，沒有對應表中欄位的值，那麼以 null 來代替。
如果不加此引數，那麼，對應不上的記錄將無法寫入表，會出現在 bad 檔案中。
(col_name1,col_name2,col_name3) ：宣告所有欄位的名稱。
如果沒有宣告 FIELDS TERMINATED BY ','，那麼也可以在欄位處進行宣告，如下：
(
col_name1 [INTERGER EXTERNAL] TERMINATED BY ',' ,
col_name2 [DATE "DD-MON-YYY"] TERMINATED BY ',' ,
col_name3 [CHAR] TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
)
如果沒有宣告 FIELDS TERMINATED BY ','，並且文字檔案中沒有任何的分隔標識，那麼也可以用指
定位置的方式來裝載資料，如下：
(
col_name1 POSITION(1:2),
col_name2 POSITION(3:9),
col_name3 POSITION(*:15) CHAR(8),//char(8)指定欄位型別及長度， *:15，表示，從上一個欄位結束的位置開始， 15 結束
col_name4 POSITION(16:30) "TRIM(:col_name4)", // 去掉本欄位擷取的字元兩邊的空格)
begindata：與 infile * 遙相呼應，即要匯入的資料就在控制檔案中，且在 begindata 的下面。

 


3. 測試一： infile *
要求：
- 指定 bad 檔案
- 裝載之前將目標表 delete 清空
- 匯入的資料在控制檔案中
1) 在 scott 使用者下建立測試表

SCOTT@ORA11GR2>create table sl_base(id number(5),fname varchar2(10),lname varchar2(10));

 

Table created.

 

2) 建立控制檔案

[oracle@wang ~]$ vi base.ctl

load data

infile *

badfile 'base.bad'

into table sl_base

replace

fields terminated by ','

(id,fname,lname)

begindata

1,zhangfei,zhangyide

2,guanyu,guanyunchang

3,liubei,liuxuande

~

"base.ctl" [New] 11L, 179C written           

[oracle@wang ~]$

 

3) 透過 sqlldr 將控制檔案的資料匯入 scott 使用者下的測試表

[oracle@wang ~]$ sqlldr scott/tiger control=base.ctl

 

SQL*Loader: Release 11.2.0.4.0 - Production on Fri Sep 23 21:32:08 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Commit point reached - logical record count 3

[oracle@wang ~]$

 

4) 檢視所生成的相關日誌  

[oracle@wang ~]$ ls

base.bad  base.ctl  base.log

[oracle@wang ~]$

[oracle@wang ~]$ ll

total 12

-rw-r--r-- 1 oracle oinstall   22 Sep 23 21:32 base.bad

-rw-r--r-- 1 oracle oinstall  179 Sep 23 21:31 base.ctl

-rw-r--r-- 1 oracle oinstall 1698 Sep 23 21:32 base.log

[oracle@wang ~]$

 

5) 檢視生成的 bad 檔案

[oracle@wang ~]$ cat base.bad

2,guanyu,guanyunchang

[oracle@wang ~]$

 

6) 透過 sqlplus 檢視匯入的資料

SCOTT@ORA11GR2>select * from sl_base;

 

        ID FNAME      LNAME

---------- ---------- ----------

         1 zhangfei   zhangyide

         3 liubei     liuxuande

 

7) 小結
本測試是將資料寫入控制檔案中，未指定 bad 檔案，由於控制檔案中的資料長度大於測試表的欄位長度，導致匯入失敗，預設的情況下自動生成 bad 檔案，檔案的存放目錄為執行 sqlldr 的目錄，另由於未指定日誌檔案目錄，所以，也自動生成到 sqlldr 目錄下， bad 檔案和 log 檔案的預設名稱與控制檔案的名稱相同。

 

4. 測試二： null 值
要求：
- 建立存放資料的檔案
- 使用預設的 bad 檔案生成方式
- 使用 truncate 選項方式
1) 清理環境測試一生成的檔案

[oracle@wang ~]$ rm base*

[oracle@wang ~]$

[oracle@wang ~]$ ls

[oracle@wang ~]$

 

2) 建立資料檔案

[oracle@wang ~]$ vi base_data.dat

1,zhangfei,zhangyide

2,guanyu,guanyunchang

3,liubei,liuxuande

4,kongming

~~

"base_data.dat" [New] 4L, 73C written        

[oracle@wang ~]$ 

 

3) 建立控制檔案

[oracle@wang ~]$ vi base.ctl

load data

infile 'base_data.dat'

into table sl_base

truncate

fields terminated by ','

(id,fname,lname)

~

"base.ctl" [New] 6L, 103C written             

[oracle@wang ~]$

 

4) 檢視建立的檔案

[oracle@wang ~]$ ll

total 8

-rw-r--r-- 1 oracle oinstall 103 Sep 23 21:45 base.ctl

-rw-r--r-- 1 oracle oinstall  73 Sep 23 21:42 base_data.dat

[oracle@wang ~]$

 

5) 透過 sqlldr 匯入資料

[oracle@wang ~]$ sqlldr scott/tiger control=base.ctl

 

SQL*Loader: Release 11.2.0.4.0 - Production on Fri Sep 23 21:47:12 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Commit point reached - logical record count 4

[oracle@wang ~]$

 

6) 檢視生成的相關檔案

[oracle@wang ~]$ ll

total 16

-rw-r--r-- 1 oracle oinstall  103 Sep 23 21:45 base.ctl

-rw-r--r-- 1 oracle oinstall   33 Sep 23 21:47 base_data.bad

-rw-r--r-- 1 oracle oinstall   73 Sep 23 21:42 base_data.dat

-rw-r--r-- 1 oracle oinstall 1839 Sep 23 21:47 base.log

[oracle@wang ~]$

 

7) 檢視 bad 檔案內容

[oracle@wang ~]$ cat base_data.bad

4,kongming

2,guanyu,guanyunchang

[oracle@wang ~]$

注：bad 檔案中有兩條記錄， id 為 2 的記錄未匯入的原因是 lname 欄位太長，id 為 4 的記錄未導
入原因是 lname 欄位出現了空值

8) 透過 sqlplus 檢視匯入資訊

SCOTT@ORA11GR2>select * from sl_base;

 

        ID FNAME      LNAME

---------- ---------- ----------

         1 zhangfei   zhangyide

         3 liubei     liuxuande

 

9) 解決 null 值問題，編輯控制檔案

[oracle@wang ~]$ ls

base.ctl  base_data.bad  base_data.dat  base.log

[oracle@wang ~]$

[oracle@wang ~]$ vi base.ctl

load data

infile 'base_data.dat'

into table sl_base

truncate

fields terminated by ','

trailing nullcols

(id,fname,lname)

~

"base.ctl" 7L, 121C written

[oracle@wang ~]$

 

10) 再次執行 sqlldr

[oracle@wang ~]$ sqlldr scott/tiger control=base.ctl

 

SQL*Loader: Release 11.2.0.4.0 - Production on Fri Sep 23 21:54:20 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Commit point reached - logical record count 4

[oracle@wang ~]$

 

11) 檢視生成檔案

[oracle@wang ~]$ ll

total 16

-rw-r--r-- 1 oracle oinstall  121 Sep 23 21:53 base.ctl

-rw-r--r-- 1 oracle oinstall   22 Sep 23 21:54 base_data.bad

-rw-r--r-- 1 oracle oinstall   73 Sep 23 21:42 base_data.dat

-rw-r--r-- 1 oracle oinstall 1744 Sep 23 21:54 base.log

[oracle@wang ~]$

12) 檢視 bad 檔案，此時空值錯誤的記錄已經不存在了

[oracle@wang ~]$ cat  base_data.bad

2,guanyu,guanyunchang

[oracle@wang ~]$

 

13) 驗證空值

SCOTT@ORA11GR2>select * from sl_base;

 

        ID FNAME      LNAME

---------- ---------- ----------

         1 zhangfei   zhangyide

         3 liubei     liuxuande

         4 kongming

 

14) 小結：
如果資料檔案中的資料存在 null 值，那麼一定記得在控制檔案中加入 trailing nullcols 語句，
除非你不想匯入 null 值。

 

5. 測試三：字串中包含逗號
要求：
- 資料檔案中的資料存在逗號
- 在控制檔案中定義欄位時指定分隔符
1) 刪除上次實驗相關檔案

[oracle@wang ~]$ ls

base.ctl  base_data.bad  base_data.dat  base.log

[oracle@wang ~]$

[oracle@wang ~]$

[oracle@wang ~]$ rm *

[oracle@wang ~]$ ls

[oracle@wang ~]$

 

2) 建立控制檔案

[oracle@wang ~]$ vi base.ctl

load data

infile 'base_data.dat'

into table sl_base

truncate

trailing nullcols

(id terminated by ',',

fname terminated by ',',

lname terminated by ','optionally enclosed by '"'

)

~

"base.ctl" [New] 9L, 179C written            

[oracle@wang ~]$

 

3) 建立資料檔案

[oracle@wang ~]$ vi base_data.dat

1,zhangfei,"zhang,yide"

2,guanyu,"guan,yunchang"

3,liubei,"liu,xuande"

4,kongming

~

"base_data.dat" [New] 4L, 82C written        

[oracle@wang ~]$

 

4) 執行 sqlldr

[oracle@wang ~]$ sqlldr scott/tiger control=base.ctl

 

SQL*Loader: Release 11.2.0.4.0 - Production on Fri Sep 23 22:08:15 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Commit point reached - logical record count 4

[oracle@wang ~]$

 

5) 檢視執行後當前目錄生成的檔案

[oracle@wang ~]$ ls

base.ctl  base_data.bad  base_data.dat  base.log

[oracle@wang ~]$

 

6) 檢視 bad 檔案，還是那條過長的記錄沒有匯入

[oracle@wang ~]$ cat base_data.bad

2,guanyu,"guan,yunchang"

[oracle@wang ~]$

 

7) 驗證

SCOTT@ORA11GR2>select * from sl_base;

 

        ID FNAME      LNAME

---------- ---------- ----------

         1 zhangfei   zhang,yide

         3 liubei     liu,xuande

         4 kongming

 

6. 測試四：資料檔案中無分隔符
1) 刪除上次實驗相關檔案

[oracle@wang ~]$ ls

base.ctl  base_data.bad  base_data.dat  base.log

[oracle@wang ~]$

[oracle@wang ~]$ rm *

[oracle@wang ~]$

[oracle@wang ~]$ ls

[oracle@wang ~]$

 

2) 建立資料檔案

[oracle@wang ~]$ vi base_data.dat

1zhangfeizhangyide

2guanyu  guanyunchang

3liubei  liuxuande

4kongming

~

"base_data.dat" [New] 4L, 70C written        

[oracle@wang ~]$

[oracle@wang ~]$ ls

base_data.dat

[oracle@wang ~]$

 

3) 建立控制檔案

[oracle@wang ~]$ vi base.ctl

load data

infile 'base_data.dat'

into table sl_base

truncate

trailing nullcols

(id position(1:1),

fname position(2:9),

lname position(10:22))

~

"base.ctl" [New] 8L, 142C written            

[oracle@wang ~]$

4) 執行 sqlldr

[oracle@wang ~]$ sqlldr scott/tiger control=base.ctl

 

SQL*Loader: Release 11.2.0.4.0 - Production on Fri Sep 23 22:18:00 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Commit point reached - logical record count 4

[oracle@wang ~]$

 

5) 檢視生成的檔案

[oracle@wang ~]$ ls

base.ctl  base_data.bad  base_data.dat  base.log

[oracle@wang ~]$

檢視bad日誌：

[oracle@wang ~]$ cat base_data.bad

2guanyu  guanyunchang

[oracle@wang ~]$

 

6) 驗證

SCOTT@ORA11GR2>select * from sl_base;

 

        ID FNAME      LNAME

---------- ---------- ----------

         1 zhangfei   zhangyide

         3 liubei     liuxuande

         4 kongming

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2126617/，如需轉載，請註明出處，否則將追究法律責任。

相關文章