Oracle SQL Loader的详细语法.doc

Oracle SQL Loader的详细语法Oracle SQL Loader的详细语法 SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据仓库选择使用的加载方法，因为它提供了最快速的途径（DIRECT，PARALLEL）。现在，我们抛开其理论不谈，用实例来使您快速掌握SQL*LOADER的使用方法。 首先，我们认识一下SQL*LOADER。 在NT下，SQL*LOADER的命令为SQLLDR，在UNIX下一般为sqlldr/sqlload。 如执行：d:oraclesqlldr SQL*Loader: Release .0 - Production on星期二 1月 8 11:06:42 2002 (c) Copyright 1999 Oracle Corporation. All rights reserved.用法: SQLLOAD关键字 =值 ,keyword=value,.有效的关键字: userid - ORACLE username/password control - Control file name log - Log file name bad - Bad file name data - Data file name discard - Discard file name discardmax - Number of discards to allow (全部默认) skip - Number of logical records to skip (默认0) load - Number of logical records to load (全部默认) errors - Number of errors to allow (默认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(默认65536) silent - Suppress messages during run (header,feedback,errors,discards,partitions) direct - use direct path (默认FALSE) parfile - parameter file: name of file that contains parameter specifications parallel - do parallel load (默认FALSE) file - File to allocate extents from skip_unusable_indexes - disallow/allow unusable indexes or index partitions(默认FALSE) skip_index_maintenance - do not maintain indexes, mark affected indexes as unusable(默认FALSE) commit_discontinued - commit loaded rows when load is discontinued(默认FALSE) readsize - Size of Read buffer (默认1048576) PLEASE NOTE:命令行参数可以由位置或关键字指定。前者的例子是 sqlloadscott/tiger foo;后者的例子是 sqlload control=foo userid=scott/tiger.位置指定参数的时间必须早于但不可迟于由关键字指定的参数。例如, SQLLOAD SCott/tiger control=foo logfile=log,但不允许 sqlload scott/tiger control=foo log,即使允许参数 log的位置正确。 d:oracle我们可以从中看到一些基本的帮助信息，这里，我用到的是中文的WIN2000ADVSERVER。我们知道，SQL*LOADER只能导入纯文本，所以我们现在开始以实例来讲解其用法。一、已存在数据源result.csv，欲倒入ORACLE中FANCY用户下。 result.csv内容： 1,默认 Web站点,54:80:,RUNNING 2,other,54:80:,STOPPED 3,third,54:81:,RUNNING从中，我们看出4列，分别以逗号分隔，为变长字符串。二、制定控制文件result.ctl result.ctl内容：load data infile result.csv into table resultxt (resultid char terminated by , website char terminated by , ipport char terminated by , status char terminated by whitespace)说明： infile指数据源文件这里我们省略了默认的discardfile result.dsc badfile result.bad into table resultxt默认是INSERT，也可以into table resultxt APPEND为追加方式，或REPLACE terminated by ,指用逗号分隔 terminated by whitespace结尾以空白分隔三、此时我们执行加载：D:sqlldr userid=fancy/testpass control=result.ctl log=resulthis.out SQL*Loader: Release .0 - Production on星期二 1月 8 10:25:42 2002 (c) Copyright 1999 Oracle Corporation. All rights reserved. SQL*Loader-941:在描述表RESULTXT时出现错误 ORA-04043:对象 RESULTXT不存在提示出错，因为数据库没有对应的表。四、在数据库建立表 create table resultxt (resultid varchar2(500), website varchar2(500), ipport varchar2(500), status varchar2(500) /五、重新执行加载D:sqlldr userid=fancy/k1i7l6l8 control=result.ctl log=resulthis.out SQL*Loader: Release .0 - Production on星期二 1月 8 10:31:57 2002 (c) Copyright 1999 Oracle Corporation. All rights reserved.达到提交点，逻辑记录计数2达到提交点，逻辑记录计数3已经成功！我们可以通过日志文件来分析其过程：resulthis.out内容如下： SQL*Loader: Release .0 - Production on星期二 1月 8 10:31:57 2002 (c) Copyright 1999 Oracle Corporation. All rights reserved.控制文件: result.ctl数据文件: result.csv错误文件: result.bad废弃文件:未作指定 : (可废弃所有记录)装载数: ALL跳过数: 0允许的错误: 50绑定数组: 64行，最大 65536字节继续:未作指定所用路径:常规表RESULTXT已载入从每个逻辑记录插入选项对此表INSERT生效列名位置长度中止包装数据类型 - - - - - - RESULTID FIRST * , CHARACTER WEBSITE NEXT * , CHARACTER IPPORT NEXT * , CHARACTER STATUS NEXT * WHT CHARACTER表RESULTXT: 3行载入成功由于数据错误, 0行没有载入。由于所有 WHEN子句失败, 0行没有载入。由于所有字段都为空的, 0行没有载入。为结合数组分配的空间: 65016字节（63行）除绑定数组外的内存空间分配: 0字节跳过的逻辑记录总数: 0读取的逻辑记录总数: 3拒绝的逻辑记录总数: 0废弃的逻辑记录总数: 0从星期二 1月 08 10:31:57 2002开始运行在星期二 1月 08 10:32:00 2002处运行结束经过时间为: 00: 00: 02.70 CPU时间为: 00: 00: 00.10(可六、并发操作 sqlldr userid=/ control=result1.ctl direct=true parallel=true sqlldr userid=/ control=result2.ctl direct=true parallel=true sqlldr userid=/ control=result2.ctl direct=true parallel=true当加载大量数据时（大约超过10GB），最好抑制日志的产生：SQLALTER TABLE RESULTXT nologging;这样不产生REDOLOG，可以提高效率。然后在CONTROL文件中load data上面加一行：unrecoverable此选项必须要与DIRECT共同应用。在并发操作时，ORACLE声称可以达到每小时处理100GB数据的能力！其实，估计能到110G就算不错了，开始可用结构相同的文件，但只有少量数据，成功后开始加载大量数据，这样可以避免时间的浪费。有关SQLLDR的问题控制文件：input.ctl，内容如下：load data -1、控制文件标识 infile test.txt -2、要输入的数据文件名为test.txt append into table test-3、向表test中追加记录 fields terminated by X09-4、字段终止于X09，是一个制表符（TAB） (id,username,password,sj) -定义列对应顺序其中append为数据装载方式，还有其他选项： a、insert，为缺省方式，在数据装载开始时要求表为空 b、append，在表中追加新记录 c、replace，删除旧记录，替换成新装载的记录 d、truncate，同上=PS:下面是我用C#写的调用SQL_LOADER的方法Private Function Exec_SqlLoader(ByVal s_aFilePath As String, ByVal o_afileName As Hashtable, ByRef s_aErrMsg As String) As Boolean FilePath = s_aFilePath.Trim() Dim ctlPath As String = FilePath + control s_aErrMsg = Try For i As Integer = 1 To 4 Dim strmReader As New StreamReader(ctlPath + 001 + i.ToString() + .ctl) Dim ctlContent As String = strmReader.ReadToEnd() ctlContent = ctlContent.Replace(dataFile, o_afileName.Item(i.ToString) strmReader.Close() strmReader = Nothing Dim strCtlName As String = ctlPath + 001 + i.ToString() + new.ctl Dim strLogName As String = ctlPath + 001 + i.ToString() + new.log Dim strBadName As String = ctlPath + 001 + i.ToString() + new.bad Dim strDscName As String = ctlPath + 001 + i.ToString() + new.dsc Dim strmWriter As StreamWriter = File.CreateText(strCtlName) strmWriter.Write(ctlContent) strmWriter.Close() strmWriter = Nothing If (File.Exists(strBadName) Then File.Delete(strBadName) End If Dim connSetting As String() = thisVersion.MSD_ConnectionStr.Split(;) Dim serviceName As String = CType(connSetting.GetValue(0), String) serviceName = serviceName.Substring(serviceName.IndexOf(=) + 1) Dim userId As String = CType(connSetting.GetValue(1), String) userId = userId.Substring(userId.IndexOf(=) + 1) Dim password As S