SqlLoader实用教程(基础入门).doc

SqlLoader实用教程本教程只捡最有用的部分讲解，通过运行一个实例，来教大家如何使用sqlloader命令。如果想掌握更多的技巧，可以参考一些网上的教程或者和本教程配套打包的其他书籍。SQL*Loader是Oracle数据库导入外部数据的一个工具，一般用来导入文本文件，也可以导入二进制的文件，比如图片等。常用的就是导入文本就行了。首先需要明确的，那就是SQLLoader依赖于Oracle客户端，如果一个应用服务器上没有安装Oracle客户端（10G以后版本只需要安装有服务器端或者客户端即可），那么是无法应用SQLLoader特性的。Sqlloader的命令格式是SQLLDR keyword=value ,keyword=value,.可选的参数userid - ORACLE username/passwordcontrol - Control file namelog - Log file namebad - Bad file namedata - Data file namediscard - Discard file namediscardmax - Number of discards to allow (Default all)skip - Number of logical records to skip (Default 0)load - Number of logical records to load (Default all)errors - Number of errors to allow (Default 50)rows - Number of rows in conventional path bind array or between direct path data saves (Default: Conventional path 64, Direct path all)bindsize - Size of conventional path bind array in bytes (Default 256000)silent - Suppress messages during run (header,feedback,errors,discards,partitions)direct - use direct path (Default FALSE)parfile - parameter file: name of file that contains parameter specificationsparallel - do parallel load (Default FALSE)file - File to allocate extents from skip_unusable_indexes - disallow/allow unusable indexes or index partitions (Default FALSE)skip_index_maintenance - do not maintain indexes, mark affected indexes as unusable (Default FALSE) readsize - Size of Read buffer (Default 1048576)external_table - use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED)columnarrayrows - Number of rows for direct path column array (Default 5000)streamsize - Size of direct path stream buffer in bytes (Default 256000)multithreading - use multithreading in direct path resumable - enable or disable resumable for current session (Default FALSE)resumable_name - text string to help identify resumable statementresumable_timeout - wait time (in seconds) for RESUMABLE (Default 7200)date_cache - size (in entries) of date conversion cache (Default 1000)安装完oracle的客户端后，在控制台打sqlldr，就可以出现sqlldr命令的详细信息。这里我们只捡有用的说，我们用的格式是：sqlldr userid=SCOTT/TIGERGDBLY_liyue control=D:/jqykt/ctl.CTL log=D:/jqykt/log.LOG bad=D:/jqykt/bad.BAD direct=true readsize=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执行完毕后的错误数据文件位置，如果有错误数据的话。如果不指定，会生成一个默认的文件，扩展名也是BADdirect=true 直接装载，属于优化部分，用户提高速度。readsize=4194304设置sqlloader 的缓冲，单位是字节，也就是4M啦，经过n多测试，4M的效果是最好的。前面描述了那么多，接下来我们用一个实例来实践一下。建表相关的sql是：- Create tablecreate table F2008050102NHINFO_T加_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_YKTID VARCHAR2(30), SYS_ID NUMBER(10), SYS_XIANG VARCHAR2(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);控制文件很重要，它的内容是：load data infile D:/jqykt/F2008320722NHINFO_Import.1.txt这一行指定了数据文件的位置，我们默认把它放在D:jqykt下append导入的方式，选择追加(append)，也有truncate方式(把之前表内的数据都删掉) into table F2008050102NHINFO_T要导到那个表中去fields terminated by x09数据文件中的字段是以什么分隔符分隔的，x09表示是用ascII码是09的字符分隔也就是tab（制表符）TRAILING NULLCOLS允许字段为空值，这个几乎是必须的(SYS_ZX字段名称 CHAR, SYS_ZDM CHAR字段类型，除了日期型，其余用CHAR型即可，日期型用DATE yyyy-mm-dd HH24:MI:SS。当然你的数据文件中的日期型数据也得用此格式, SYS_MC CHAR, SYS_XZQH CHAR, SYS_FZ CHAR, SYS_LSH CHAR, SYS_SFZ CHAR, HKBH CHAR, XB CHAR, CSRQ CHAR, KYYH CHAR,SYS_YKTID CHAR,SYS_ID CHAR,SYS_XIANG CHAR,SYS_CUN CHAR,YZBM CHAR,JTDZ CHAR,JTRK CHAR,CNWN CHAR,CNWG CHAR,YLHN CHAR,JSMJ CHAR,LXDH CHAR,CBMJ CHAR)我们把它保存成一个文本文件，然后把名字改成ctl.CTL，放在D:jqykt下，就可以了。然后就是数据文件了，类似这样的文件，数据之间用tab分隔0320722011601001周凤树32072201160100132072219580808001x32072201011090000511753190116牛山镇牛山村一组5300320722011601002周凤怀32072201160100232072219540328001332072201011090000512063200116牛山镇牛山村一组530一切就绪，执行命令很快，报告255633条记录装载完毕。我们看到目录下生成了一个log文件打开日志记录文件看看SQL*Loader: Release 9.2.0.1.0 - Production on 星期一 6月 16 23:08:24 2008Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Control File: D:/jqykt/ctl.CTLData File: D:/jqykt/F2008320722NHINFO_Import.1.txt Bad File: D:/jqykt/bad.BAD Discard File: none specified (Allow all discards)Number to load: ALLNumber to skip: 0Errors allowed: 50最多允许50行错误，再有错误就不导了Continuation: none specifiedPath used: DirectTable F2008050102NHINFO_T, loaded from every logical record.Insert option in effect for this table: APPENDTRAILING NULLCOLS option in effect Column Name Position Len Term Encl Datatype- - - - - -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 Table F2008050102NHINFO_T: 255633 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null.这部分信息很重要，一共成功的导入了多少条记录，有多少条因为错误没有导入等等。如果你的数据和ctl文件都配合的很好，错误数据应该为0Bind array size not used in direct path.Column array rows : 5000Stream buffer bytes: 256000Read buffer bytes: 4194304Total logical records skipped: 0Total logical records read: 255633Total logical records rejected: 0Total logical records discarded: 0Total stream buffers loaded by SQL*Loader main thread: 59Total stream buffers loaded by SQL*Loader load thread: 98Run began on 星期一 6月 16 23:08:24 2008Run ended on 星期一 6月 16 23:08:27 2008Elapsed time was: 00:00:02.70CPU time was: 00:00:00.89大致的经