SqlLoader实用教程基础入门.docx
《SqlLoader实用教程基础入门.docx》由会员分享,可在线阅读,更多相关《SqlLoader实用教程基础入门.docx(9页珍藏版)》请在冰豆网上搜索。
SqlLoader实用教程基础入门
SqlLoader实用教程
本教程只捡最有用的部分讲解,通过运行一个实例,来教大家如何使用sqlloader命令。
如果想掌握更多的技巧,可以参考一些网上的教程或者和本教程配套打包的其他书籍。
SQL*Loader是Oracle数据库导入外部数据的一个工具,一般用来导入文本文件,也可以导入二进制的文件,比如图片等。
常用的就是导入文本就行了。
首先需要明确的,那就是SQLLoader依赖于Oracle客户端,如果一个应用服务器上没有安装Oracle客户端(10G以后版本只需要安装有服务器端或者客户端即可),那么是无法应用SQLLoader特性的。
Sqlloader的命令格式是
SQLLDRkeyword=value[,keyword=value,...]
可选的参数
userid--ORACLEusername/password
control--Controlfilename
log--Logfilename
bad--Badfilename
data--Datafilename
discard--Discardfilename
discardmax--Numberofdiscardstoallow (Defaultall)
skip--Numberoflogicalrecordstoskip (Default0)
load--Numberoflogicalrecordstoload (Defaultall)
errors--Numberoferrorstoallow (Default50)
rows--Numberofrowsinconventionalpathbindarrayorbetweendirectpathdatasaves
(Default:
Conventionalpath64,Directpathall)
bindsize--Sizeofconventionalpathbindarrayinbytes (Default256000)
silent--Suppressmessagesduringrun(header,feedback,errors,discards,partitions)
direct--usedirectpath (DefaultFALSE)
parfile--parameterfile:
nameoffilethatcontainsparameterspecifications
parallel--doparallelload (DefaultFALSE)
file--Filetoallocateextentsfromskip_unusable_indexes--disallow/allowunusableindexesorindexpartitions (DefaultFALSE)
skip_index_maintenance--donotmaintainindexes,markaffectedindexesasunus
able (DefaultFALSE)
readsize--SizeofReadbuffer (Default1048576)
external_table--useexternaltableforload;NOT_USED,GENERATE_ONLY,EXECUTE
(DefaultNOT_USED)
columnarrayrows--Numberofrowsfordirectpathcolumnarray (Default5000)
streamsize--Sizeofdirectpathstreambufferinbytes (Default256000)
multithreading--usemultithreadingindirectpath
resumable--enableordisableresumableforcurrentsession (DefaultFALSE)
resumable_name--textstringtohelpidentifyresumablestatement
resumable_timeout--waittime(inseconds)forRESUMABLE (Default7200)
date_cache--size(inentries)ofdateconversioncache (Default1000)
安装完oracle的客户端后,在控制台打sqlldr,就可以出现sqlldr命令的详细信息。
这里我们只捡有用的说,我们用的格式是:
sqlldruserid=SCOTT/TIGER@GDBLY_liyuecontrol=D:
/jqykt/ctl.CTLlog=D:
/jqykt/log.LOGbad=D:
/jqykt/bad.BADdirect=truereadsize=4194304
解释:
userid=SCOTT/TIGER@GDBLY_liyue
userid=用户名/密码@服务名
加入这个参数,以后就可以向不同的数据库中装载数据,如果@后面的参数不写,表明只向本机的oracle导入数据。
注意@后面用服务名,这个在oracle的EMC中设置。
如图所示
control=D:
/jqykt/ctl.CTL
告诉sqlloader,控制文件的位置。
控制文件(*.ctl)包含要导入的数据在哪里,要导到哪个表里去,数据的分隔符是什么,字段与数据文件中数据的对应关系等等,这些控制信息当然要放在一个ctl(control)的文件里面。
log=D:
/jqykt/log.LOG
指定sqlloader执行完毕后的日志文件位置
bad=D:
/jqykt/bad.BAD
指定sqlloader执行完毕后的错误数据文件位置,如果有错误数据的话。
如果不指定,会生成一个默认的文件,扩展名也是BAD
direct=true
直接装载,属于优化部分,用户提高速度。
readsize=4194304
设置sqlloader的缓冲,单位是字节,也就是4M啦,经过n多测试,4M的效果是最好的。
前面描述了那么多,接下来我们用一个实例来实践一下。
建表相关的sql是:
--Createtable
createtableF2008050102NHINFO_T
(
SYS_ZX NUMBER
(1),
SYS_ZDM VARCHAR2(15),
SYS_MC VARCHAR2(50),
SYS_XZQH VARCHAR2(10),
SYS_FZ VARCHAR2
(2),
SYS_LSH VARCHAR2(3),
SYS_SFZ VARCHAR2(18),
HKBH VARCHAR2(10),
XB VARCHAR2(10),
CSRQ NUMBER(30),
KYYH VARCHAR2
(2),
SYS_YKTIDVARCHAR2(30),
SYS_ID NUMBER(10),
SYS_XIANGVARCHAR2
(2),
SYS_CUN VARCHAR2
(2),
YZBM VARCHAR2(10),
JTDZ VARCHAR2(50),
JTRK NUMBER(14),
CNWN NUMBER(14),
CNWG NUMBER(14),
YLHN NUMBER(12),
JSMJ NUMBER(18),
LXDH VARCHAR2(14),
CBMJ NUMBER(18)
);
控制文件很重要,它的内容是:
loaddata
infile'D:
/jqykt/F2008320722NHINFO_Import.1.txt'
appendintotableF2008050102NHINFO_T
fieldsterminatedbyx'09'
TRAILINGNULLCOLS
(
SYS_ZXCHAR,
SYS_ZDMCHAR,
SYS_MCCHAR,
SYS_XZQHCHAR,
SYS_FZCHAR,
SYS_LSHCHAR,
SYS_SFZCHAR,
HKBHCHAR,
XBCHAR,
CSRQCHAR,
KYYHCHAR,
SYS_YKTIDCHAR,
SYS_IDCHAR,
SYS_XIANGCHAR,
SYS_CUNCHAR,
YZBMCHAR,
JTDZCHAR,
JTRKCHAR,
CNWNCHAR,
CNWGCHAR,
YLHNCHAR,
JSMJCHAR,
LXDHCHAR,
CBMJCHAR
)
我们把它保存成一个文本文件,然后把名字改成ctl.CTL,放在D:
\jqykt下,就可以了。
然后就是数据文件了,类似这样的文件,数据之间用tab分隔
0 周凤树 3207220116 01 001 320722************ 1175 319 01 16 牛山镇牛山村一组 5 3 0
0 周凤怀 3207220116 01 002 320722************ 1206 320 01 16 牛山镇牛山村一组 5 3 0
>>>>>>>>>>
一切就绪,执行命令
很快,报告255633条记录装载完毕。
我们看到目录下生成了一个log文件
打开日志记录文件看看
SQL*Loader:
Release9.2.0.1.0-Productionon星期一6月1623:
08:
242008
Copyright(c)1982,2002,OracleCorporation. Allrightsreserved.
ControlFile:
D:
/jqykt/ctl.CTL
DataFile:
D:
/jqykt/F2008320722NHINFO_Import.1.txt
BadFile:
D:
/jqykt/bad.BAD
DiscardFile:
nonespecified
(Allowalldiscards)
Numbertoload:
ALL
Numbertoskip:
0
Errorsallowed:
50
Continuation:
nonespecified
Pathused:
Direct
TableF2008050102NHINFO_T,loadedfromeverylogicalrecord.
Insertoptionineffectforthistable:
APPEND
TRAILINGNULLCOLSoptionineffect
ColumnName Position Len TermEnclDatatype
--------------------------------------------------------------------------
SYS_ZX FIRST * WHT CHARACTER
SYS_ZDM NEXT * WHT CHARACTER
SYS_MC NEXT * WHT CHARACTER
SYS_XZQH NEXT * WHT CHARACTER
SYS_FZ NEXT * WHT CHARACTER
SYS_LSH NEXT * WHT CHARACTER
SYS_SFZ NEXT * WHT CHARACTER
HKBH NEXT * WHT CHARACTER
XB NEXT * WHT CHARACTER
CSRQ NEXT * WHT CHARACTER
KYYH NEXT * WHT CHARACTER
SYS_YKTID NEXT * WHT CHARACTER
SYS_ID NEXT * WHT CHARACTER
SYS_XIANG NEXT * WHT CHARACTER
SYS_CUN NEXT * WHT CHARACTER
YZBM NEXT * WHT CHARACTER
JTDZ NEXT * WHT CHARACTER
JTRK NEXT * WHT CHARACTER
CNWN NEXT * WHT CHARACTER
CNWG NEXT * WHT CHARACTER
YLHN NEXT * WHT CHARACTER
JSMJ NEXT * WHT CHARACTER
LXDH NEXT * WHT CHARACTER
CBMJ NEXT * WHT CHARACTER
TableF2008050102NHINFO_T:
255633Rowssuccessfullyloaded.
0Rowsnotloadedduetodataerrors.
0RowsnotloadedbecauseallWHENclauseswerefailed.
0Rowsnotloadedbecauseallfieldswerenull.
Bindarraysizenotusedindirectpath.
Columnarray rows:
5000
Streambufferbytes:
256000
Read bufferbytes:
4194304
Totallogicalrecordsskipped:
0
Totallogicalrecordsread:
255633
Totallogicalrecordsrejected:
0
Totallogicalrecordsdiscarded:
0
TotalstreambuffersloadedbySQL*Loadermainthread:
59
TotalstreambuffersloadedbySQL*Loaderloadthread:
98
Runbeganon星期一6月 1623:
08:
242008
Runendedon星期一6月 1623:
08:
272008
Elapsedtimewas:
00:
00:
02.70
CPUtimewas:
00:
00:
00.89
大致的经过就是如此,不过光有这些还不够,我们需要注意的是:
一个数据文件不要很大,分成几个较小的文件来导速度更快一些。
只要在控制文件中多指定几个数据文件即可,比如
infile'D:
/jqykt/F2008320722NHINFO_Import.1.txt'
infile'D:
/jqykt/F2008320722NHINFO_Import.2.txt'
…
SQL*Loader数据的提交:
一般情况下是在导入数据文件数据后提交的。
也可以通过指定ROWS=参数来指定每次提交记录数。
也就是说,我在命令中加入rows=50000,就是每装载50000行,进行一次提交,这时50000条数据已经被写到数据库里面了。
提高SQL*Loader 的性能:
1)一个简单而容易忽略的问题是,没有对导入的表使用任何索引和/或约束(主键)。
如果这样做,甚至在使用ROWS=参数时,会很明显降低数据库导入性能。
2)可以添加DIRECT=TRUE来提高导入数据的性能。
当然,在很多情况下,不能使用此参数。
3)通过指定 UNRECOVERABLE选项,可以关闭数据库的日志。
这个选项只能和direct一起使用。
4)可以同时运行多个导入任务.
5)尽量将数据文件分隔成若干小文件,每个文件里面不要太多数据,有几十万条足以。
常规导入与direct导入方式的区别:
常规导入可以通过使用INSERT语句来导入数据。
Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE),而直接将数据导入到数据文件中。
如何导入定长数据
下面是导入固定位置(固定长度)数据示例: