快捷導(dǎo)航

Oracle的SQLLDR用法簡介

更新時間：2023年07月03日 10:01:01 作者：OO好久不見OO

這篇文章主要介紹了Oracle的SQLLDR用法簡介,SQL*Loader是oracle的高速批量數(shù)據(jù)加載工具,這是一個非常有用的工具，可用于從多種平面文件格式向Oracle數(shù)據(jù)庫中加載數(shù)據(jù),SQLLDR可以在極短的時間內(nèi)加載數(shù)量龐大的數(shù)據(jù),需要的朋友可以參考下

SQLLDR導(dǎo)入

1.簡介

SQL*LOADER是ORACLE的數(shù)據(jù)加載工具，通常用來將操作系統(tǒng)文件（數(shù)據(jù)）遷移到ORACLE數(shù)據(jù)庫中。SQL*LOADER是大型數(shù)據(jù)倉庫選擇使用的加載方法，因為它提供了最快速的途徑（DIRECT，PARALLEL）。

2.語法和參數(shù)

語法： SQLLDR keyword=value [,keyword=value,...];

Sqlldr 參數(shù)一覽

Keyword	默認(rèn)值	描述
userid		ORACLE 用戶名/口令
control		控制文件名
log		日志文件名
bad		錯誤文件名
data		數(shù)據(jù)文件名
discard		廢棄文件名
discardmax	全部	允許廢棄的文件的數(shù)目
skip	0	要跳過的邏輯記錄的數(shù)目
load	全部	要加載的邏輯記錄的數(shù)目
errors		允許的錯誤的數(shù)目
rows	常規(guī)：64 默認(rèn)路徑：全部	常規(guī)路徑綁定數(shù)組中或直接路徑保存數(shù)據(jù)間的行數(shù)
bindsize	256000	常規(guī)路徑綁定數(shù)組的大小
silent		運行過程中隱藏消息
direct	FALSE	使用直接路徑
parfile		參數(shù)文件: 包含參數(shù)說明的文件的名稱
parallel	FALSE	執(zhí)行并行加載
file		執(zhí)行文件
skip_unusable_indexes	FALSE	不允許/允許使用無用的索引或索引分區(qū)
skip_index_maintenance	FALSE	沒有維護索引, 將受到影響的索引標(biāo)記為無用
commit_discontinued	FALSE	提交加載中斷時已加載的行
readsize	1048576	讀取緩沖區(qū)的大小
external_table	NOT_USED	使用外部表進行加載; NOT_USED, GENERATE_ONLY, EXECUTE
columnarrayrows	5000	直接路徑列數(shù)組的行數(shù)
streamsize	256000	直接路徑流緩沖區(qū)的大小 (以字節(jié)計)
multithreading		在直接路徑中使用多線程
resumable	FALSE	啟用或禁用當(dāng)前的可恢復(fù)會話
resumable_name		有助于標(biāo)識可恢復(fù)語句的文本字符串
resumable_timeout	7200	RESUMABLE 的等待時間 (以秒計)
date_cache	1000	日期轉(zhuǎn)換高速緩存的大小 (以條目計)

3. 范例

利用PLSQL生成測試數(shù)據(jù)cux_sqlldr_test.txt

BEGIN
? FOR iIN1..100
??? LOOP
????? IFMOD(i,2)=1THEN
??????? dbms_output.put_line('"'||i||'","column1_'||i||'",'||'"column2_'||i||'",'||'"column3_'||i||'",'||'"show_column_'||i||'",'||'"hide_column_'||i||'","2017-01-01"');?
????? ELSE
??????? dbms_output.put_line('"'||i||'","column1_'||i||'", ,'||'"column3_'||i||'",'||'"show_column_'||i||'",'||'"hide_column_'||i||'"');?????????
????? ENDIF;
??? ENDLOOP;
END;

建表

CREATETABLE cux.cux_sqlldr_test
(line_num?NUMBER,
?seq_num NUMBER,
?column1 VARCHAR2(30),
?column2 VARCHAR2(30)NOTNULL,
?column3 VARCHAR2(30)DEFAULT'column2',
?show_column VARCHAR2(30),
?hide_column VARCHAR2(30),
?creation_date DATE
);
CREATEORREPLACE?SYNONYM apps.cux_sqlldr_test?FOR cux.cux_sqlldr_test;
CREATESEQUENCE cux.cux_sqlldr_test_sSTARTWITH10001;
CREATEORREPLACESYNONYM apps.cux_sqlldr_test_s FOR cux.cux_sqlldr_test_s;

4.Sqlldr 有兩種使用方式

（1）在控制文件中包涵數(shù)據(jù).

創(chuàng)建一個文件命名為cux_sqlldr_test.ctl，在服務(wù)器下創(chuàng)建目錄Sqlldr,在sqlldr下創(chuàng)建log和bad文件夾，

上傳cux_sqlldr_test.ctl至服務(wù)器，如下圖所示：

cux_sqlldr_test.ctl內(nèi)容如下:

OPTIONS (skip=3,rows=128)
load data?????
CHARACTERSET ZHS16GBK??
infile? *??????
badfile? "/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/bad/cux_sqlldr_test.bad"
discardfile? "/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/bad/cux_sqlldr_test.disc"?
TRUNCATE into table cux_sqlldr_test?
WHEN column1 != "column1_1"
Fields terminated by ","
Optionally enclosed by '"'
TRAILING NULLCOLS?
(
line_num? RECNUM ,
seq_num? "cux_sqlldr_test_s.nextval" ,
column1 ,
column2 ,
column3 NULLIF (column3="column3_4"),
show_column "UPPER(:show_column)" ,
hide_column? FILLER ,?
creation_date? DATE? 'YYYY-MM-DD' "CASE WHEN :creation_date is null THEN TO_CHAR(sysdate,'YYYY-MM-DD')? ELSE :creation_date END"
)
BEGINDATA
"1","column1_1","column2_1","column3_1","show_column_1","hide_column_1","2017-01-01"
"2","column1_2", ,"column3_2","show_column_2","hide_column_2"
"3","column1_3","column2_3","column3_3","show_column_3","hide_column_3","2017-01-01"
"4","column1_4", ,"column3_4","show_column_4","hide_column_4"
"5","column1_5","column2_5","column3_5","show_column_5","hide_column_5","2017-01-01"
"6","column1_6", ,"column3_6","show_column_6","hide_column_6"
"7","column1_7","column2_7","column3_7","show_column_7","hide_column_7","2017-01-01"
"8","column1_8", ,"column3_8","show_column_8","hide_column_8"
"9","column1_9","column2_9","column3_9","show_column_9","hide_column_9","2017-01-01"
"10","column1_10", ,"column3_10","show_column_10","hide_column_10"

運行命令

sqlldr userid=apps/appsjiahuicontrol=/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/file/cux_sqlldr_test.ctl  log=$CUX_TOP/sqlldr/file/cux_sqlldr_test.log

查看結(jié)果

查看表

由上圖可以看出，運行命令后，在file文件夾下生成了cux_sqlldr_test.log，在bad下生成了cux_sqlldr_test.bad，在表中生成了3條記錄，下面分析一下cux_sqlldr_test.ctl的內(nèi)容和結(jié)果

代碼	說明
OPTIONS (skip=3,rows=128)	sqlldr 的內(nèi)容可以寫在cotrol文件load_data的前面，此處跳過前3行,每次提交128行
load data	加載數(shù)據(jù)
CHARACTERSET ZHS16GBK	字符集編碼（如果出現(xiàn)亂碼要考慮一下）
infile *	加載的文件，* 表示本文件
badfile	錯誤的數(shù)據(jù)所放的文件（校驗錯誤）
discardfile	丟棄的數(shù)據(jù)放的路徑（記錄的格式錯誤或過濾行）
TRUNCATE into table cux_sqlldr_test	先TRUNCATE  cux_sqlldr_test再將記錄插入表
WHEN column1 != "column1_1"	過濾行，對于值為column1_1的行過濾
Fields terminated by ","	多個字段間用“,”隔開
Optionally enclosed by '"'	單個字段用“"”，“"”開始結(jié)束
TRAILING NULLCOLS	對于值為空的字段允許為空
(line_num RECNUM	序號，自動生成，并不取自數(shù)據(jù)
seq_num "cux_sqlldr_test_s.nextval"	取每條記錄的第一個字段，此處應(yīng)為1..10，但是這里賦值序列。

表2

代碼	說明
column1	column1
column2	column2，表定義為非空字段，雖然上面允許為空，但是如果該值為空，不能插入表種
column3 NULLIF (column3="column3_4")	column3="column3_4"時候默認(rèn)為空
show_column "UPPER(:show_column)"	大寫列（調(diào)用UPPER大寫函數(shù)）
hide_column FILLER	FILLER 隱藏列
creation_date DATE 'YYYY-MM-DD'"CASE WHEN :creation_date is null THENTO_CHAR(sysdate,'YYYY-MM-DD') ELSE :creation_date END"	日期類型，格式為YYYY-MM-DD，為空的時候取系統(tǒng)日期
BEGINDATA	數(shù)據(jù)開始
*******	數(shù)據(jù)內(nèi)容，默認(rèn)每行一條記錄

從日志可以看出7條數(shù)據(jù)中，4條記錄無法沒導(dǎo)入的原因。

查看cux_sqlldr_test.bad，其中記錄4條錯誤的數(shù)據(jù)。

（2）在控制文件中不包涵數(shù)據(jù).

上傳cux_sqlldr_test.txt,cux_sqlldr_test.ctl至服務(wù)器，cux_sqlldr_test.txt由上面PLSQL腳本生成，cux_sqlldr_test.ctl如下

OPTIONS (skip=3,rows=128)
load data????&nbsp;
CHARACTERSET ZHS16GBK??
infile? "/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/file/cux_sqlldr_test.txt"?????
badfile? "/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/bad/cux_sqlldr_test.bad"
discardfile "/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/bad/cux_sqlldr_test.disc"?
TRUNCATE into table cux_sqlldr_test?
WHEN column1 != "column1_4"
Fields terminated by ","
Optionally enclosed by '"'
TRAILING NULLCOLS?
(
line_num? RECNUM ,
seq_num? "cux_sqlldr_test_s.nextval" ,
column1 ,
column2 "nvl(:column2,'***')",
column3 NULLIF (column3="column3_4"),
show_column "UPPER(:show_column)" ,
hide_column? FILLER ,?
creation_date? DATE? 'YYYY-MM-DD' "CASE WHEN :creation_date is null THEN TO_CHAR(sysdate,'YYYY-MM-DD')? ELSE :creation_date END"
)

運行命令

sqlldr userid=apps/appsjiahuicontrol=/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/file/cux_sqlldr_test.ctl log=$CUX_TOP/sqlldr/file/cux_sqlldr_test.log