SQL*Loader总结sqlldr

阅读量：2042 次

发布时间：2019-04-28

本文共 22137 字，大约阅读时间需要 73 分钟。

由记录,所有代码均经过测试.只是部分代码的效果在不同平台上会出现差异!有问题请:1#w1520.com[#替换成@],或者:

SQL*Loader总结

本处使用的配置:

Red hat Server 6.4 _ X64 [英文版]

Oracle 11G R2 _X64[英文版]

认识SQL*Loader

SQL*Loader是一个命令行下的操作工具,对应的操作系统命令是SQLLDR,注意Linux/UNIX环境中对于命令的大小写敏感,执行的命令应为小写:sqlldr.

示例: 为Oracle添加几笔数据(Oracle数据库必须已经正常启动): 在当前目录新建一个文件:ldr_case1.ctl :

注意:文件名和文件类型任意.

保存如下内容:

LOAD DATA	INFILE *	INTO TABLE BONUS	FIELDS	TERMINATED BY ","	(ENAME,JOB,SAL)	BEGINDATA	SMITH,CLEAK,3904	ALLEN,SALESMAN,2891	WARD,SALESMAN,3128	KING,PRESIDENT,2523---------//code end.

然后执行如下命令:

sqlldr scott/TIGER CONTROL=ldr_case1.ctl //提交记录到数据库

然后马上连接到数据库看一下:

SQLPLUS scott/TIGERSELECT * FROM BONUS;//...输出上述数据.

SQL*Loader体系结构

控制文件

LOAD DATA	-----------我是分割符--------------	INFILE *	INTO TABLE BONUS	FIELDS	TERMINATED BY ","	(ENAME,JOB,SAL)	BEGINDATA	-----------我是分割符--------------	SMITH,CLEAK,3904	ALLEN,SALESMAN,2891	WARD,SALESMAN,3128	KING,PRESIDENT,2523

第一部分:控制文件一般以此开头(控制文件的语法非常复杂,一般是以此开头,不是说只能以此开头,比如LOAD DATA前可指定UNRECOVERABLE或RECOVERABLE来控制此次加载的数据是否可恢复,或者指定CONTINUE_LOAD,表示继续加载.更多控制文件语法的大全可以参考官方文档.这里只对常用功能进行介绍.)

中间部分:这里是真正的控制部分,下面分解开来,逐条介绍:

>:INFILE:表示数据文件位置,如果值为*,表示数据就在控制文件中,本例中没有单独的数据文件,对于大多数加载而言,都会将数据文件与控制文件分离.	>:INTO TABLE tbl_name: tbl_name 即数据要加载到的目标表,该表再你执行SQLLDR命令之前必须已经创建.	   >> INTO 前还有一些很有意思的参数需要说明:		>>>:INSERT :向表中插入数据,表必须为空,如果表非空的话,执行SQLLDR命令时会报错,默认就是INSERT参数.		>>>:APPEND :向表中追加数据,不管表中是否有数据.		>>>:REPLACE :替换表中数据,相当于先DELETE表中全部数据,然后再INSERT.	>:FIELDS TERMINATED BY "," : 设置数据部分字符串的分隔值,这里设置为逗号(,)分隔,当然也可以换成其他任意可见字符,只要确定那是数据行中的分割符就行.	>:(ENAME,JOB,SAL) :要插入的表的列名,这里需要注意的是列名要与表中列名完全相同,列的顺序可以与表中列顺序不同,但是必须与数据部分的列一一对应.	>:BEGINDATA :表示以下为待加载数据,仅当INFILE指定为*时有效.

数据部分:演示时为了简化步骤,将数据部分与控制部分都放在控制文件中,通常这部分是独立存在于一个文本文件中.如果是独立的数据文件,只需要将控制文件中INFILE参数后面的*改为数据文件的文件名即可.

日志文件

在默认情况下,SQLLDR命令再执行过程中,会自动产生一个与控制文件同名的日志文件,文件扩展名为.log,日志文件中记录了加载过程中的各项统计信息,如一些初始化参数,读取的记录数,成功加载的记录数,加载用时等.

//此产生的日志文件存放于:ldr_case1.log

错误文件

SQLLDR命令在执行过程中,还会产生一个同名的错误文件,文件扩展名为.bad(如果DBA不显式指定的话.)错误文件中数据的格式与数据文件完全相同.

废弃文件

除了日志文件和错误文件,执行SQLLDR命令时还有可能生成一个同名的废弃文件,文件扩展名为.dsc(在默认情况下不会有,必须在执行SQLLDR命令时显式指定废弃文件,并确实存在不符合导入逻辑的记录)

//~SQL*Loader 体系结构图 //~ 存放于本目录下的images文件夹.[名为:5_2sqlldr.png]

一千零一十一个怎么办

给我的是个Excel文件怎么办

注意:如果是Excel文件,则可以选择用csv格式 [以逗号分割]保存文件,然后用上面的方式导入. .xls文件单个Sheet最大行数不超过65536行.

要加载的文件不是以逗号分隔怎么办

有两种方式可以参考:

修改控制文件,将分隔符替换为逗号.

修改控制文件,将FIELDS TERMINATED BY 的值修改为实际的分割符.

要加载的数据中包含分隔符怎么办

提供的数据格式如下:

SMITH,CLEAK,3904ALLEN,"SALER,M",2891WARD,"SALER,""S""",3128KING,PRESIDENT,2523--ldr_case2.ctl

修改控制文件:

LOAD DATAINFILE ldr_case2.ctlTRUNCATE INTO TABLE BONUSFIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'(ENAME,JOB,SAL)BEGINDATA

数据文件没有分隔符怎么办

例如:提供数据文件中的数据都是以下的格式:

SMITH CLEAK 3904ALLEN SALESMAN 2891WARD SALESMAN 3128KING PRESIDENT 2523

示例文件保存为数据文件:oracledata3.data

按照专业的叫法,这是定长字符串,不要紧的.SQLLDR中处理定长字符串也轻而易举.

针对此例,我们将控制文件修改如下:

LOAD DATAINFILE oracledata3.dataTRUNCATE INTO TABLE BONUS(ENAME position(1:5);JOB position(7:15);SAL Position(17:20))BEGINDATA

控制文件保存为:ldr_case3.ctl

position关键字用来指定列的开始和结束位置,如JOB position(7:15)是指从第7个字符开始截止到第15个字符作为ENAME列的列值.position的写法也很灵活,要实现上述功能还可以换成下列几种形式:

position(*+2:15):直接指定数值的方式叫做绝对偏移量,如果使用*号,专业名词叫相对偏移量,表示上一个字段从哪里结束,这次就从哪里开始.相对偏移量也可以再做运算,比如(*+2:15)就表示从上次结束的位置+2的地方开始.

position(*)char(9):这种相对偏移量+类型和长度的优势在于,你只需要为第一列指定位置开始,其他列只需要指定列的长度就可以了,实际使用中比较省事.

数据文件中的列比要导入的表中的列少怎么办

提出这样的问题,说明你没有认真看我们前面的实例.重新看一下表结构:

Name					   Null?    Type ----------------------------------------- -------- -- ENAME						    VARCHAR2(10) JOB						    VARCHAR2(9) SAL						    NUMBER 						    NUMBER

之前的诸多演示,正是在数据文件中列比表要少的环境下创建的.这说明列少不怕,关键是看控制文件中的配置.考虑下面一种情况,如果缺少的列必须赋值又怎么办?(修改一下控制文件即可):

LOAD DATAINFILE oracledata3.dataTRUNCATE INTO TABLE BONUS(ENAME position(1:5),JOB position(7:15),SAL position(17:20), "0")BEGINDATA

示例代码保存为控制文件 ldr_case3.ctl

输出数据库数据.

可以再玩点更有难度的,根据SAL的值设置列的值,修改控制文件如下:

LOAD DATAINFILE oracledata4.dataTRUNCATE INTO TABLE BONUS(ENAME position(1:5),JOB position(7:15),SAL position(17:20), "substr(:SAL,1,1)")BEGINDATA

插入数据.

输出数据库数据

ENAME	   JOB		    SAL       ---------- --------- ---------- ----------SMITH	   CLEAKMAN	    904 	 9ALLEN	   SALESMAN	    891 	 8WARDA	   SALESMAN	    128 	 1KINGA	   PRESIDENT	   2523 	 2

这里列的值根据SAL列值而定,我们通过一个SQL中的函数substr取SAL值的第一列,赋予列,当然这只是一个示例.DBA可以根据实际需求进行适当的修改,通过SQL中的函数可以实现很多很有意思的转换,

也许能够为你省下很大力气.而且如果现有函数无法实现,甚至可以通过PL/SQL编写自定义函数,然后在SQLLDR的控制文件中调用,调用方式与系统自带函数方式完全相同.这样就可以根据需求对要加载的列做更灵活的处理.

数据文件中的列比要导入的表中的列多怎么办

如果数据文件中的列比要导入的表中的列少,处理的时候可能麻烦些,多了反倒更简单.针对不同情况,一般有以下两种处理方式:

1.修改数据文件,将多余的数据删除,不过以这种方式处理,小数据量时还算可行,一旦数据文件较大,几百兆甚至上千兆,修改数据文件耗时耗力.这时我们就需要Plan B.

2.比如,数据文件如下:

-------------------------------------示例数据文件:

SMITH	7369 CLERK	800 20ALLEN	7499 SALESMAN	1600 30WARD	7521 SALESMAN	1250 30SMITH	7369 CLERK	800 20ALLEN	7499 SALESMAN	1600 30WARD	7521 SALESMAN	1250 30SMITH	7369 CLERK	800 20ALLEN	7499 SALESMAN	1600 30WARD	7521 SALESMAN	1250 30SMITH	7369 CLERK	800 20ALLEN	7499 SALESMAN	1600 30WARD	7521 SALESMAN	1250 30//----------------------------------end DATA.

示例文件保存为:oracledata4.data

我们希望导入第1,3,4列而跳过2,5两列(注意这里指的不是字符列),创建控制文件:

LOAD DATAINFILE oracledata4.dataTRUNCATE INTO TABLE BONUS(ENAME position(1:6),TCOL FILLER position(8:11),JOB position(13:21),SAL position(23:26))BEGINDATA

SQLLDR的控制文件中对列定义时支持FILLER关键字,可以用来指定过滤列,在上述控制文件中,我们就使用该关键字来过滤列,相当于第8到第11列之间的数据不导入.

事实上由于此处为定长字串,我们再控制文件中指定的position参数,已经限定了读取的内容,你甚至可以删除控制文件中TCOL FILLER position(8:11)那行.

结果符合要求,不过如果数据文件中字符串不是定长格式,而是通过跟分隔符来处理的,那控制文件中就需要注意,如数据文件如下:

SMITH,7369,CLERK,800,20ALLEN,7499,SALESMAN,1600,30WARD,7521,SALESMAN,1250,30SMITH,7369,CLERK,800,20ALLEN,7499,SALESMAN,1600,30WARD,7521,SALESMAN,1250,30SMITH,7369,CLERK,800,20ALLEN,7499,SALESMAN,1600,30WARD,7521,SALESMAN,1250,30SMITH,7369,CLERK,800,20ALLEN,7499,SALESMAN,1600,30WARD,7521,SALESMAN,1250,30

-->数据文件保存为:oracledata5.data

创建控制文件,此时控制文件必须指定FILLER,不然列值就有可能不对应,例如:创建控制文件如下:

LOAD DATAINFILE oracledata5.dataTRUNCATE INTO TABLE BONUSFIELDS TERMINATED BY ","(ENAME,TCOL FILLER,JOB,SAL)BEGINDATA

-->控制文件保存为:ldr_case7.ctl

-->执行SQLLDR

输出数据库数据

//------------------------------SQL> SELECT * FROM BONUS;ENAME	   JOB		    SAL       ---------- --------- ---------- ----------SMITH	   CLERK	    800ALLEN	   SALESMAN	   1600WARD	   SALESMAN	   1250SMITH	   CLERK	    800ALLEN	   SALESMAN	   1600WARD	   SALESMAN	   1250SMITH	   CLERK	    800ALLEN	   SALESMAN	   1600WARD	   SALESMAN	   1250SMITH	   CLERK	    800ALLEN	   SALESMAN	   1600ENAME	   JOB		    SAL       ---------- --------- ---------- ----------WARD	   SALESMAN	   125012 rows selected.

提供了多个数据文件,要导入同一张表怎么办

通常对于逻辑比较复杂的系统可能存在这种情况,因为导出的数据来源于多个系统,因此可能提供给DBA的也是多个数据文件.没有关系,先不要急着抱怨,像SQL*Loader这么有历史底蕴的工具,

什么风雨没见过,这种小case.人家早就已经预见到了,并不需要你执行多次加载,只需要在控制文件中做适当配置即可.不过有一点非常重要,提供的数据文件中的数据存放格式必须完全相同.

下面简单演示,之前老是用BONUS,这里换个表把,新建一个MANAGER表:

CREATE TABLE MANAGER(			MGRNO NUMBER,			MNAME VARCHAR2(30),			JOB VARCHAR2(30),			REMARK VARCHAR2(4000)			);

有多个数据文件,分别如下[已经在表中存在的数据]:

示例文件保存为数据文件oracledata6.data

10,SMITH,SALES,MANAGER       		      11,ALLEN.W,TECH,MANAGER       		      16,BLAKE,HR,MANAGER

示例代码保存为数据文件oracledata6_1.data

12,WARD,SERVICE,MANAGER		13,TURNER,SELLS,DIRECTOR		15,JAMES,HR,DIRECTOR

示例代码保存为数据文件oracledata6_2.data

17,MILLER,PRESIDENT

创建控制文件,只需要指定多个INFILE参数即可,如下所示:

LOAD DATA        INFILE oracledata6.data        INFILE oracledata6_1.data        INFILE oracledata6_2.data        TRUNCATE INTO TABLE MANAGER        FIELDS TERMINATED BY ","        (MGRNO,MNAME,JOB)        BEGINDATA

执行命令sqlldr.

输出数据库数据

同一个数据文件,要导入不同表怎么办

控制文件提供了多种逻辑判断方式,只要能把逻辑清晰地描述出来,SQL*Loader就能按照指定的逻辑加载.

举个例子,待导入的数据文件如下：

BON SMITH  CLEAK  3904        BON ALLEN  SALER,M  2891        BON WARD  SALER,“S”  3128        BON KING  PRESIDENT  2523        MGR  10  SMITH  SALES  MANAGER        MGR  11  ALLEN.W  TECH  MANAGER        MGR  16  BLAKE  HR  MANAGER        TMP SMITH 7369   CLERK     800   20         TMP ALLEN 7499   SALESMAN  1520  30        TMP WARD  7521   SALESMAN  1250  30        TMP JONES 7566   MANAGER   2975  20

-->示例代码保存数据文件为:oracledata7.data

需求是将MGR开头的记录导入MANAGER表,以BON开头的记录导入BONUS表,其他记录存入废弃文件中:

LOAD DATA        INFILE oracledata7.data        DISCARDFILE oracledata7.dsc        TRUNCATE INTO TABLE BONUS    WHEN TAB='BON'    (TAB FILLER POSITION(1:3),    ENAME POSITION(5,8),    JOB POSITION(*+1:18),    SAL POSITION(*+1)    ) INTO TABLE MANAGER    WHEN TAB = 'MGR'    (TAB FILLER POSITION(1:3),    MGRNO POSITION(4:5),    MNAME POSITION(7:13),    JOB POSITION(*+1))BEGINDATA

->示例代码保存为:ldr_case10.ctl

虽然这个控制文件看起来比之前的都要复杂,但只有一个新语法,即WHEN关键字,我们这里通过WHEN来实现判断,很容易理解.同时,指定了DISCARDFILE参数,

以生成不满足加载条件的废弃文件,如果你有心,不妨等执行完SQLLDR命令后查看ldr_case10.dsc文件和ldr_case10.log文件.

注意:控制文件中WHEN逻辑判断不支持OR关键字,因此如果你的判断条件有多个,则只能通过AND连接,而不能直接使用OR.

数据文件前N行不想导入怎么办

假如某天你接到一项数据加载需求,用户提供了一份100万行的数据文件,告诉你导后50万行,恭喜,你接到了一个正常的需求.

实现的方式较多,比如修改数据文件,只保留后50万行(Windows 下借助Editplus这类文本工具可以轻松实现,Linux/UNIX下通过TAIL等命令也可以轻易实现).

如果你人很懒,不想修改文件,那正合SQLLDR胃口,人家早早的就提供好了SKIP参数专用于满足此类需求.

例如:提供的数据文件如下:

SQL> SELECT ENAME,MGR,JOB,SAL FROM EMP;//^=10ENAME		  MGR JOB	       SAL---------- ---------- --------- ----------1:6 7-11 15:23 26:33 [22-31值太大,超过10个字节!!报错!!]123456789^123456789^123456789^123456789^12SMITH67902     CLERK	 800ALLEN67698     SALESMAN	 1600WARD66698      SALESMAN	 1250JONES67839     MANAGER	 2975MARTIN7698     SALESMAN	 1250BLAKE67839     MANAGER	 2850CLARK67839     MANAGER	 2450SCOTT67566     ANALYST	 3000KING66PRESIDENT 5000TURNER7698     SALESMAN	 1500ADAMS67788     CLERK	 1100

示例代码保存为数据文件oracledata8.data

//前三行不导入,从第四行开始导入.LOAD DATAINFILE oracledata8.dataTRUNCATE INTO TABLE BONUS(ENAME position(1:6),TCOL FILLER position(18:21),JOB  position(23:31),SAL position(39:42))BEGINDATA

查看导入结果:

SQL> SELECT * FROM BONUS;ENAME	   JOB		    SAL       ---------- --------- ---------- ----------SMITH	   LERK 	    800ALLEN	   ALESMAN	   1600WARD	   LESMAN	   1250JONES	   ANAGER	   2975MARTIN	   SALESMAN	   1250BLAKE	   ANAGER	   2850CLARK	   ANAGER	   2450SCOTT	   NALYST	   3000KING	   ESIDENT	   5000TURNER	   SALESMAN	   1500ADAMS	   LERK 	   1100

如果用户要求只加载第X～XX行的记录,SQLLDR还有一个参数叫LOAD,配置LOAD参数即可轻松实现.

示例:依旧使用上述数据,需求更改为,只导入第4～9行的数据:

执行命令:SQLLDR SCOTT/TIGER CONTROL=ldr_case.ctl SKIP=4 LOAD=6

要加载的数据中有换行符怎么办

由于标准换行符也是SQLLDR识别数据行结束的标注符,因此要将含换行符的数据加载到表中稍复杂一点点,而且需要根据实际情况来处理,不同情况的处理方式也不一样,但基本思路是相同的,就是要同SQLLDR指明什么时候才需要进行换行操作.

手工指定的换行符

在手工指定换行符的情况下,数据文件中的换行符并不是标准的换行标志,而是用户自定义的一个标识字符(或多个字符组成),这种情况的处理比较简单,如数据文件如下:

10,SMITH,SALES MANAGER,This is SMITH.  \nHe is a Sales Manager.11,ALLEN.W,TECH MANAGER,This is ALLEN.W. \nHe is a tech Manager.16,BLAKE,HR MANAGER,This is BLAKE. \nHe is a Hr Manager.

示例代码保存为数据文件 oracledata10.data

我们可以通过控制文件,在数据加载前处理remark列的数据,将用户指定的"\n"字符替换为chr(10),即标准换行符,创建控制文件如下:

LOAD DATAINFILE oracledata10.dataTRUNCATE INTO TABLE MANAGERFIELDS TERMINATED BY ","( MGRNO, MNAME, JOB, REMARK "replace(:remark, '\\n',chr(10))")BEGINDATA

示例代码保存为控制文件:ldr_case12.ctl

这里需要注意的是,替换时必须指定"\\n"而不只是"\n",因为"\n"会被SQLLDR识别成换行符并转换成换行标志,这样可能导致数据加载出错.而"\"是默认转义符,指定该转义符后SQLLDR就会将"\n"识别成普通字符了.

指定FIX属性处理换行符(定长数据文件专用)

数据文件如下:

10,SMITH,SALES MANAGER,This is SMITH.He is a Sales Manager.11,ALLEN.W,TECH MANAGER,This is ALLEN.W.He is a tech Manager.16,BLAKE,HR MANAGER,This is BLAKE.He is a Hr Manager.

示例代码保存为数据文件: oracledata11.data

创建控制文件如下:

LOAD DATAINFILE oracledata11.data "fix 68"TRUNCATE INTO TABLE MANAGER(MGRNO position(1:2),MNAME position(*+1:10),JOB position(*+1:24),REMARK position(*+1:65))BEGINDATA

示例代码保存为控制文件 ldr_case13.ctl

指定VAR属性处理换行符(行头部标识换行)

前面提到的INFILE 关键字还支持VAR属性,语法格式为INFILE filename "var n",n的值不能超过40,否则会报错,如果不指定n,则默认值为5.

本小节就演示通过这种方式处理换行符.总的来说,这确实是相当有才的一种方式,首先通过VAR属性在每行开头指定一个固定长度的字符串,该字符串指明该行的长度,通过这种方式支持变长字符串.

下面演示一下,数据文件如下:

06310,SMITH,SALES MANAGER,This is SMITH.He is a Sales Manager.06511,ALLEN.W,TECH MANAGER,This is ALLEN.W.He is a tech Manager.05516,BLAKE,HR MANAGER,This is BLAKE.He is a Hr Manager.

示例代码保存为数据文件 oracledata12.data

创建控制文件如下:

LOAD DATAINFILE oracledata12.data "var 3"TRUNCATE INTO TABLE MANAGERFIELDS TERMINATED BY ","(MGRNO,MNAME,JOB,REMARK)BEGINDATA

示例代码保存为控制文件: ldr_case14.ctl

注意:这两种方式其实并不方便,因为每个操作系统的换行标识符也不同,比如,在Windows环境下换行标志由"回车(13)+换行CHR(10)"两个字节组成,而Linux/UNIX环境则是"换行CHR(10)"一个字节,也就是说指定了长度也不方便转换.[下面介绍的方法更易于操作,也更可行一些]

指定STR属性处理换行符(行尾部标识换行)

这种方式也需要先对数据文件做处理,在记录换行除打上一个标记,比如“|”(当然你也可以定义为其他字符,但注意不要与要导入的数据有冲突),这样SQLLDR见到该字符就知道换行的时候到了.

由于单个字符出现在导入数据中的机率较高,因此简易换行标志尽可能由多个字符组成,通常习惯于定义“字符+换行符”作为新的换行标记,这里我们也采用这种方式.

例如:数据文件如下:

10,SMITH,SALES MANAGER,This is SMITH.He is a Sales Manager.|11,ALLEN.W,TECH MANAGER,This is ALLEN.W.He is a Tech Manager.|16,BLAKE,HR MANAGER,This is BLAKE.He is a Hr Manager.|

示例代码保存为数据文件: oracledata13.data

创建控制文件如下:

LOAD DATAINFILE oracledata13.data "str '|'"TRUNCATE INTO TABLE MANAGERFIELDS TERMINATED BY ","(MGRNO,MNAME,JOB,REMARK)BEGINDATA

控制文件保存为: ldr_case15.ctl

STR属性中支持两种字符指定方式:

'char_string':普通字符,即标准的可见字符,不过也有写不可见字符可以通过下列反斜杠标识的方式在字符串模式中指定:      \n:表示换行.      \t:表示行制表符(tab)      \f:表示换页      \w:表示列制表符      \r:表示回车        说到这里,又不得不再次提及Windows 和 Linux/UNIX 对换行符识别的差别,Linux/UNIX下指定"\n"即可,Windows下需要指定"\r\n"才表示一个完整的换行符.        X'hex_string':二进制字符,对于一些不可见字符,如像回车换行这类字符,可以将其转换成十六进制,然后再通过str X'hex_str'方式指定.

比如上述控制文件中的功能如果用二进制字符标识,形式如下：

iINFILE oracledata14.data "str X'7C0D0A'"

要查看指定字符的十六进制编码,可以通过UTL_RAW.CAST_TO_RAW生成,例如:

SQL> SELECT UTL_RAW.CAST_TO_RAW('|'||chr(13)||chr(10)) from dual;        UTL_RAW.CAST_TO_RAW('|'||CHR(13)||CHR(10))-------------------------------------------------------------------------------      7C0D0A

要导入大字段(LOB类型)怎么办

LOB作为打字段数据类型,是Oracle新增的数据类型,用来替代long和long raw类型,一般SQLLDR操作中不会涉及大字段类型的操作.

1.数据保存在数据文件中.

这种方式可以按照5.3.10节中提到的方式处理,举个例子:

还是以MANAGER表为例,修改表中的REMARK列为LOB类型,在SQL*Plus命令环境中如下:

SQL> ALTER TABLE MANAGER DROP COLUMN REMARK;       Table altered.       SQL> ALTER TABLE MANAGER ADD REMARK CLOB;      Table altered.

数据文件如下:

10,SMITH,SALES MANAGER,This is SMITH.He is a Sales Manager.|11,ALLEN.W,TECH MANAGER,This is ALLEN.W.He is a Tech Manager.|16,BLAKE,HR MANAGER,"This is BLAKE. He is a Hr Manager. The jobs responsibilities are in the following:1.	Ensure the effective local implementation of corporate level HR initiatives and new programs.2.	Take initiatives in defining HR strategy on attracting, hiring, integrating, developing, managing3.	Oversee standard recruiting and procedures to ensure the company¡¯s staffing requirements are met in a timely manne4.	Provide employees with fair and appropriate compensation and benefit, to ensure market competitiveness.5.	Develop, implement and oversee the training and development programs to upgrade the skills of the  future challenges."|

示例代码保存为数据文件: oracledatat14.data

创建控制文件如下:

LOAD DATA       INFILE oracledata14.data "str '|\n'"       TRUNCATE INTO TABLE MANAGER       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'       (MGRNO,MNAME,JOB,REMARK char(100000))       BEGINDATA

控制文件保存为: ldr_case16.ctl

注意这里REMARK显式指定char(100000),因为Oracle默认所有输入字段都是char(255),如不显式指定类型和长度,一旦加载列的实际长度超出255,则数据加载就会报错:Field in data file exceeds maximum length.

执行SQLLDR加载数据.

数据成功加载!这种方式最关键的地方是必须保证REMARK列定义的长度大于数据文件中文本块的大小.

2. 数据保存在独立的文件中

这种数据相对于第一种更加常见,相应处理也更简单一些(跳过了换行符的处理),SQLLDR中提供了LOBFILE关键字,直接支持加载文件到LOB类型中.

首先再SQL*Plus命令行环境中连接数据库,创建一个新表:

SQL> CREATE TABLE LOBTBL (    FILEOWNER VARCHAR2(30),    FILENAME VARCHAR2(200),    FILESIZE NUMBER,    FILEDATA CLOB,    CREATE_DATE DATE    );

表中共有5列,分别表示文件属主,文件名,文件大小,文件内容和文件创建时间.

创建数据文件,内容如下:

2009-03-17  09:43     154 JUNSANSI /home/oracle/ldr_case1.ctl2009-03-17  09:44     189 JUNSANSI /home/oracle/ldr_case1.log2009-03-17  09:44   2,369 JUNSANSI /home/oracle/ldr_case2.ctl2009-03-16  16:50     173 JUNSANSI /home/oracle/ldr_case2.log2009-03-16  16:49     204 JUNSANSI /home/oracle/ldr_case3.ctl2009-03-16  16:50   1,498 JUNSANSI /home/oracle/ldr_case3.log2009-03-16  17:41     145 JUNSANSI /home/oracle/ldr_case4.ctl2009-03-16  17:44     130 JUNSANSI /home/oracle/ldr_case4.log2009-03-16  17:44   1,743 JUNSANSI /home/oracle/ldr_case5.ctl2009-03-17  11:01     132 JUNSANSI /home/oracle/ldr_case5.log2009-03-17  11:02     188 JUNSANSI /home/oracle/ldr_case6.ctl2009-03-17  11:02   1,730 JUNSANSI /home/oracle/ldr_case6.log

示例代码保存为数据文件: oracledata15.data

创建控制文件如下:

LOAD DATAINFILE oracledata15.dataTRUNCATE INTO TABLE LOBTBL (CREATE_DATE position(1:17) date 'yyyy-mm-dd hh24:mi',FILESIZE position(*+1:24) "to_number(:FILESIZE,'99,999,999')",FILEOWNER position(*+1:34),FILENAME position(*+1) char(200) "substr(:FILENAME,instr(:FILENAME,'\\',-1)+1)",FILEDATA LOBFILE(FILENAME) TERMINATED BY EOF )BEGINDATA

示例文件保存为控制文件:ldr_case17.ctl

这个控制文件是之前介绍示例应用的集大成者,又有定长处理,又有函数转换,唯一陌生的就是最后一行:LOBFILE就是前面提到的(FILENAME) TERMINATED BY EOF 这LOBFILE关键字,只需要指定FILENAME列,其他都是固定格式,调用时直接按此指定即可.

某些字段无值导致加载报错怎么办

比如你某天拿到了一个这样的一个数据文件：

SMITH,CLEAK,3904ALLEN,SALESMAN,WARD,SALESMAN,3128KING,PRESIDENT,2523

示例代码『未实际保存』.[ora.data]

首先按照前面的例子去处理这些数据:

LOAD DATAINFILE ora.dataTRUNCATE INTO TABLE BONUSFIELDS TERMINATED BY ","(ENAME,JOB,SAL)BEGINDATA

控制文件『未实际保存』.[ora.ctl]

执行后报错,错误如下:

Record 2 : Rejected - Error on table BONUS, column SQL.Column not found before end of logical record (use TRAILING NULLCOLS)

就本例的错误信息来说,SQLLDR提示已经非常清楚:直到行结束也没发现适当的列值.

这是本例中数据文件的第二行没有提供适当的值(这一点都不稀奇,不管数据量庞大与否,DBA绝对不能期望数据文件完全满足要求,因此在编写控制文件时,也要考虑到对意外情况的处理.)

针对这一错误,SQLLDR甚至连解决方案也一并提供:使用TRAILING NULLCOLS.TRAILING NULLCOLS的作用是当某行没有对应的列值时,SQLLDR就会自动将其赋为NULL,而不是报错.

SMITH,CLEAK,3904ALLEN,SALESMAN,WARD,SALESMAN,3128KING,PRESIDENT,2523

数据文件保存: oracledata16.data

控制文件如下:

LOAD DATAINFILE oracledata16.dataTRUNCATE INTO TABLE BONUSFIELDS TERMINATED BY "," TRAILING NULLCOLS//TRAILING NULLCOLS的作用是当某行没有对应的列值时,SQLLDR就会自动将其赋为NULL,而不是报错.(ENAME,JOB,SAL)BEGINDATA

通过这个示例,我们能够得到三点体会:

执行完操作后一定要验证,就本例来说,从SQLLDR命令的执行看起来一切征程,如果不是到SQL*Plus环境中查看导入的数据,恐怕都不知道有记录未被成功导入.

一定要注意看日志,SQLLDR虽然算不上智能,但是也不傻,有时候造成错误的原因只是它不知到怎么办好,不过日志文件中一定会留下处理痕迹,不管SQLLDR命令执行是否成功,日志文件总是能告诉我们其执行的更多细节.

以上全部都是.

100万条记录的数据加载

生成百万级的数据文件

连接到本地数据库(自己安装的):

找一个合适大小的表:

SELECT COUNT(0) FROM DBA_OBJECTS;COUNT(0)----------     65389

然后找一个有20行记录的表,对其进行笛卡尔运算,即可生成130万条记录了.

为了更好地体现通用性,我们在输出时对created日期做一下格式转换:

select a.owner||',"'||a.object_name||'",'||a.object_id||','||        to_char(a.created,'yyyy-mm-dd hh24:mi:ss')||','||a.status        from dba_objects a,(select rownum rn from dual content by rownum<=20) b ;

示例代码保存为SQL文件:getobject.sql

set echo offset term offset line 1000 pages 0set feedback offset heading offspool [当前路径]/ldr_object.csv@[当前路径]/getobject.sqlspool offset heading onset feedback onset term onset echo on

示例代码保存为:SQL文件:call.sql

然后在SQL*Plus命令行环境中,执行下列命令即可：

SQL> @[当前路径]call.sql

然后就耐心等待把,132万的记录量,输出也是需要一定时间的.

查看:

exitdu -m ldr_object.csv        //输出的数据以M为单位.

初始化环境

创建表:

CREATE TABLE OBJECTS(    OWNER VARCHAR2(30),    OBJECT_NAME VARCHAR2(50),    OBJECT_ID NUMBER,    STATUS VARCHAR2(2000),    CREATED DATE);

创建Index.

create index idx_obj_owner_name on objects(owner,object_name);

执行导入

创建控制文件如下:

LOAD DATAINFILE ldr_object.csvINSERT INTO TABLE OBJECTSFIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'(owner,object_name ,object_id,created date 'yyyy-mm-dd hh24:mi:ss',status char(2000))BEGINDATA

控制文件保存为:ldr_object.ctl

这里注意,对于CREATED列，我们指定了日期格式，并进行了转换,这个格式一定要与数据文件中日期格式相符,不然日期格式转换时会报错并导致数据加载失败.

按照默认参数执行SQLLDR，看看需要多长时间，同时指定ERRORS参数值为10,明确指定出现10次错误即中止加载:

sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl ERRORS=10

5.4.4 能不能再快一点呢

SQLLDR常规路径导入时默认一次加载64行,现在要加载的总行数已经达到百万级,十位数显然太小,我们首先尝试修改该值,先直接在后面加个0好了,看看能对效率起到多大的提升:

执行如下命令:sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl ERRORS=10 ROWS=640

日志节选信息:

value used for ROWS parameter changed from 640 to 84Table OBJECTS:  1307820 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.Space allocated for bind array:        254856 bytes(84 rows)Read   buffer bytes: 1048576Total logical records skipped: 0Total logical records read:       1307820Total logical records rejected:0Total logical records discarded:        0Run began on Wed Jun 05 14:18:43 2013Run ended on Wed Jun 05 14:28:56 2013Elapsed time was:     00:10:12.39CPU time was:00:00:16.40

注意节选信息的第一行，该信息是提示由于640行所占用的空间已经超过了参数BINDSIZE的默认值，因此自动修改到最大可承受的84行，这说明BINDSIZE参数默认值偏小.速度只比刚才小了2秒(变化几乎可以忽略)

再进一步调整BINDSIZE参数值,默认为256k,我们将其修改为10M(1024kb * 1024 * 10 = 10485760),同时将一次加载的行数提高到5000.

sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl ERRORS=10 ROWS=5000 BINDSIZE=10485760

时间变得更长,正在研究:

大约时间是:13:48

能不能再快一点呢

所有参数默认，只打开直接路径加载:

sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl DIRECT=TRUE

靠,这个速度才是王道啊!

Table OBJECTS:  1307820 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.  Date conversion cache disabled due to overflow (default size: 1000)Bind array size not used in direct path.Column array  rows :    5000Stream buffer bytes:  256000Read   buffer bytes: 1048576Total logical records skipped: 0Total logical records read:       1307820Total logical records rejected:0Total logical records discarded:        0Total stream buffers loaded by SQL*Loader main thread:     1250Total stream buffers loaded by SQL*Loader load thread:     4996Run began on Wed Jun 05 14:55:12 2013Run ended on Wed Jun 05 14:56:28 2013Elapsed time was:     00:01:15.74CPU time was:00:00:11.80

有没有可能更快呢

这究竟是希望还是欲望，已经说不清楚了，反正没个尽头。

直接导入路径导入可用的参数也有不少，不过我们这里总数据量不大，因此实际能够起到效率提升的不多，我准备主要从以下两个参数入手：

STREAMSIZE ：直接路径加载默认读取全部记录，因此不需要设置ROWS参数,读取到的数据处理后存入流缓存区,即STREAMSIZE参数，该参数默认值为256kb,这里加大到10MB.

DATE_CACHE : 该参数指定一个转换后日期格式的缓存区，以条为单位，默认值1000条，即保存1000条转换后的日期格式，由于我们要导入的数据中有日期列，因此加载该参数值到5000，以降低日期转换带来的开销.

修改参数后执行命令最终形式如下所示:

sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl DIRECT=TRUE STREAMSIZE=10485760 DATE_CACHE=5000

可能已经达到性能瓶颈:

Date cache:   Max Size:      5000   Entries :      1207   Hits    :   1306613   Misses  :0Bind array size not used in direct path.Column array  rows :    5000Stream buffer bytes:16777216Read   buffer bytes: 1048576Total logical records skipped: 0Total logical records read:       1307820Total logical records rejected:0Total logical records discarded:        0Total stream buffers loaded by SQL*Loader main thread:     1250Total stream buffers loaded by SQL*Loader load thread:        0Run began on Wed Jun 05 15:15:39 2013Run ended on Wed Jun 05 15:17:07 2013Elapsed time was:     00:01:28.14CPU time was:00:00:09.05