zz Sqlldr的使用

http://blog.oracle.com.cn/index.php/266780/viewspace-26695

Sqlldr也就是SQL*LOADER，它是oracle的高速批量数据加载工具，可以将外部文件的数据导入到oracle的数据库中。可以用于从多种平面文件格式向oracle数据库中加载数据。

它有两种操作模式：传统路径（conventional path）：利用sql插入为我们加载的数据。直接路径（direct path）：不使用sql，而是直接格式化数据库块。利用直接路径加载，能从一个平面文件读取数据，并将其直接写至格式化的数据库块，而绕过整个sql引擎和undo生成，同时还可能避开redo生成，要在一个没有任何数据的数据库中充分加载数据，这是最好的方法。

SQLLDR包括五个文件:控制文件（*.ctl）、数据文件（*.dat）、日志文件(*.log)、错误文件(*.bad)、废弃文件(*.dsc)。

   其中我们最常用的为前四中：导入方式为：

Sqlldr userid=user/password@SID control＝path\xx.ctl log=path\xx.log bad=path\ xx.bad

注意SID就是数据库名，path就是路径名，不要加’’，一般不用加数据文件，因为在control就包含了数据或者数据路径，log是需要的，它可以告诉你导入的详细信息，而bad则存放者错误的文件，discard（废弃文件）则存放者不满足导入条件的数据。

控制文件它的作用是告诉oracle如何的读取和加载信息，并提供数据的路径，加载的方式以及加载的规则的。具体格式如下“

Load data

Infile‘数据路径’

Into table table_name

Truncate

Fields terminated by‘’

（column_name1，column_name2，······）

具体含义：1、Load data的意义就是说载入数据2、Infile‘数据路径’就是指明了载入数据的位置，其中数据默认的扩展名是.dat，我们也可以更改比如.txt、.csv（逗号分割值形式）都可以，还有这里不仅可以导入数据文件，也可以导入错误文件或者废弃文件，格式如：badfile‘path\xx.bad’或者discardfile‘path\xx.dsc’3、Into table table_name把数据导入到的什么表，这里可以插入多个表，用when condition条件分割就可以了4、Truncate它的含义是当sqlldr执行这个控制文件是，表在开始加载前就给截断了。用Truncate是不能回退的一定要谨慎，除此之外，还可以使用append（用于在表中增加行）、insert（用于在空表中增加行，如果不为空，加载就会错误）、replace（用于清空表，然后在增加新行，但是用户必须有该表的delete权限），前提注意，执行加载的用户必须具有表的insert权限，如果没有该参数，系统默认的为insert。5、Fields terminated by‘’数据的分割符，首先先要明白sqlldr加载数据我们常用的有两种装载定长数据和装载变长数据。例子如下：

装载定长数据

load data
infile ‘xx.txt’

into table table_name append

(column_name1 position(01:10) character,

column_name2 position(11:12) character,

column_name3 position(13:14) character,

column_name4 position(15:16) character,)

它是装载了未知固定好的数据。

装载定长数据

load data
infile ‘x.dat’

into table table_name append

(column_name1 char terminated by ‘ ‘,

column_name2 char terminated by ‘ ‘,

column_name3 char enclosed by ‘ ‘,

column_name4 char terminated by whitespace)

或者

load data
infile ‘x.dat’

into table table_name append

Fields terminated by“”

(column_name1,column_name2，····)

这里注意一下Fields terminated by‘’，里面的‘’表示分割符号，比如说‘/’、‘；’,也可以FIELDS TERMINATED BY X‘09’（制表符）, FIELDS TERMINATED whitespace（空格）等等，还可以在后面加上OPTIONALLY ENCLOSED BY“”，它的意思实说每个选定的字段是用“”表示的。LINES TERMINATED‘\t‘ 表示每行记录之间用什么分隔默认的为\n（可以不加的）6、（column_name1，column_name2，······）就是把数据加入到表中的哪些字段。7、还可以加一个选项trailing nullcols指没有的数据用NULL填充。

我们在看一个有选择条件的加载多个表方式

Load data

infile 'x.txt'

replace into table table1

when column_name = 'condition1'

(column_name1，column_name2，····)

when column_name =‘condition2’

into table table2

(column_name1，column_name2，····)

导入EXCEL，可以把EXCEL文件另存为CSV（逗号分隔）(*.csv),控制文件就改为用逗号分隔

LOAD DATA

INFILE 'x.csv'

APPEND INTO TABLE table

FIELDS TERMINATED BY ","

(column_name1, column_name2，····)

使用filler跳过在导入数据文本中不想进行导入的列。在控制文件中还可以使用concatenate语句将多个物理行合成一个逻辑行插入到表中，在文件中加注释是用――后根语句来注释的。理想的情况下我们加载数据都希望不是完全的成功就是完全的失败这样便于第二次加载，但是实际上往往是部分成功和部分失败，一次要利用日志文件判断出失败的地方，日志文件会记录提交点和错误产生点。所有被拒绝的记录保存在坏文件和废弃文件中，因此我们必须活用sqlldr的选现来实现数据的加载。

如果加载的数据过多，我们可以作一个.bat的批处理文件把所有的sqlldr加载语句放入其中，最后直接执行批处理文件就可以。

最后一个就是并行并发操作：比如

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），最好抑制日志的产生：

SQL>ALTER TABLE RESULTXT nologging;

这样不产生REDO　LOG，可以提高效率。然后在CONTROL文件中load data上面加一行：unrecoverable（不可恢复）此选项必须要与DIRECT共同应用。

因为sqlldr在加载数据时会产生大量的insert语句，因此使用direct path先格式化数据快，在把数据块快数插入表中。提高性能。使用了direct path就可以使用unrecoverable关键字提高数据加载性能。可以不必生成重做日志项。同时可以使用parallel direct path加载选项将数据加载工作分为多个进程。因此并行的direct path加载操作比单个的direct path加载效率快很多。

Sqlldr选项参考工作图中的sqlldr.jpg

另外还要注意一个使TRAILING NULLCOLS，因为在引导数据中，比如说你向往5列中引入数据，但数据每行只有4个，而第5列之前前四行之和，但是在处理中sqlldr会告诉你，没有等处理完所有的列，记录中就没有数据了。因此我们就必须使用TRAILING NULLCOLS，这样一来，如果数据记录中不存在某列的数据，sqlldr就会先为该列绑定一个null值，因此我们执行第5列之前，实际上它的值就使null，而不会报错了。（注意，在加载数据时可以使用sql连接运算符）

加载有内嵌换行符的数据，一种方法可以用非换行符的其他字符来表示换行符比如说在文本中应该出现换行符的位置上放上一个\n，并在加载时使用一个SQL函数用以chr（10）来替换该文本。二种是使用FIX属性，但是这种方法，输入数据必须出现在定长记录中，每个记录与输入数据集中所有其他记录的长度都相同，既有相同的字节数。使用FIX属性，必须使用一个INFILE子句，因为FIX是INFILE一个选项，而且，数据必须在外部存储，而并非存储在控制文件本身，如：INFILE TEST.DAT “FIX 80”指定一个输入数据文件，这个文件每个记录都有80个字节，包括尾部的换行符。在这种情况下，输入文件中提供给sqlldr的记录设置就以非\n结束的。第三种情况，可以使用VAR属性，这种格式，每个记录必须以某个固定的字节数开始，这标识这个记录的总长度。可以加载包含内嵌换行符的变长记录，但是每个记录的开始处必须有一个记录长度的字段。如：infile test.dak“var 3”这里指出了每个输入记录的前3个字节是该输入记录的长度。还有一种情况是使用STR属性，这算是最灵活的一种，可以指定一个新的行结束字符（或字符序列）。这样就能创建一个输入数据文件，其中每一行的最后有某个特殊字符，换行符不再有特殊含义。STR属性是以16进制指定的，因此要得到所需的具体16进制串，最容易的办法是使用SQL和UTL_RAW来生成16进制串,可以直接导入在unix平台下的数据。总结一下：要注意在windows和unix两个平台的不同，结束符是不同的unxi上是\n，而windows上是\r\n，\r是记录的一部分，控制文件必须适应这一点，比如，如果取一个.dat文件只包含\n当传输到windows上是，要将各个\n转换为\r\n。那么原来unix种的控制文件就不能记载数据了。