Sqlload 总结


Sqlload 总结 Created by wangzz 标例: load data infile * ---指定加载文件 *表示数据就在控制文件后面 into table bonus ---指定表名 fields terminated by "," ---指定区域分隔符就是逗号 (ename,job,sal) ---指定表的列名 begindata ---仅当infile 指定*时有效 smith,cleak,3904 allen,salesman,2891 ward,salesman,3128 ........ 把上术文件保存成a.ctl文件,用如下命令就可以导入 sqlldr userid/pass control=a.ctl 一,要加载的文件不是以逗号作分隔 a,修改原始数据,将分隔符替换成逗号 b,修改控制文件,将fields terminated by 的值修改成实际的分隔符 二,要加载的数据中包含分隔符怎么办,如下表kkk.dat smith,cleak,3904 allen,"salesman,"ak"",2891 ward,"salesman,M",3128 这个时候就要修改控制文件 load data infile kkk.dat ---指定加载文件 *表示数据就在控制文件后面 into table bonus ---指定表名 fields terminated by "," optinonally enclosed by ' " ' ---指定区域分隔符就是逗号 (ename,job,sal) ---指定表的列名 optinonally enclosed by ' " ' 默认就是双引号,如果是其他的,把双引号更改就可以了 三,数据文件没有分隔符怎么办,是定长字符串kkk,dat smith cleak 3904 allen salesman 2891 ward salesman 3128 修改控制文件 load data infile kkk.dat truncate table bonus ( ename position(1:5), position(1:5)指的是从第一个字符载止到第五个字符作为ename的值, 绝对偏移量 job position(7:15), sal position(17:20) ) position(*+2:15) ,相对偏移量, 表示从上一个位置结束后偏移二个开始取字符,载止到实际第15个字符 position(*)char(9) 相对偏移量+类型和长度的优势在于,你只需要为第一列指定开始位置,其他的只需要指定列 长度就可以了 四,数据文件中的列比要导入的表的列少,且空列又必须赋值 如bonus中多一列comm,并赋初始值0,则可以这么写 load data infile kkk.dat truncate table bonus ( ename position(1:5), job position(7:15), sal position(17:20), comm '0' ) 如果要输入特殊值,则可以用函数来解决 load data infile kkk.dat truncate table bonus ( ename position(1:5), job position(7:15), sal position(17:20), comm "substr(:sal,1,1)" 取sal值的第一列,并赋值给comm列 ) 当然也可以用pl/sql编写自定义的函数来赋值 五,数据文件中的列比要导入的表中列多怎么办 a,将数据文件中多的列删除 b,采用过滤,或在控制文件中不录这列数据 load data infile kkk.dat truncate table bonus ( ename position(1:5), job position(7:15), sal position(17:20), tcol filler position(22:30) , --tcol假设这列不录入,就过滤掉,或这行根本不出现在控 制文件中 ) 如果此时数据文件是以分隔符出现的则这样写 load data infile kkk.dat truncate table bonus fields terminated by "," (ename , job ,sal, tcol filler) 六,多个数据文件导同一张表,条件就是这些数据文件的格式要相同 load data infile kkk.dat infile kkk2.dat infile kkk3.dat truncate table bonus fields terminated by "," (ename , job ,sal ) 七,同一个数据文件要导入不同的表 bon smith cleak 3904 bon allen saler 2891 mgr king tech 2543 mgr smm admd 3032 要把这里面的数据导到b和m表 load data infile kkk.dat discardfile ldr_case9.dsc truncate into table b when tab='bon' 如果此处判断关键字有多个,只能用and,不能用 or (tab filler position(1:3), ename position(5:9), job position(*+1:18), sal position(*+1) ) into table m when tab='mgr' (tab filler position(1:3), ename position(5:9), job position(*+1:18), sal position(*+1) ) 八,数据文件前n行不导入 sqlddr scott/scott control=ldr_case1.ctl skip=3 意思前三行不导入,从第四行工始 sqlddr scott/scott control=ldr_case1.ctl skip=3 load=6 前三行不导入,导入接下来的6行 九,要加载的数据中有换行符 手工指定的换行符,数据文件的换行符并不是标准的换行标志,而是用户自定义的一个标识字符或多个字符组 成, 10, smith,sales amnager,this is amith,\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. 控制文件的写法 load data infile ldr_case11_1.dat truncate into table manager fields terminated by "," ( mgrno,mname,job, remark "replace(:remark,'\\n',chr(10))" ) 如果数据文件是定长字符呢. smith sales manager this is smith he is a sales manager allenw tech manager this is allen w he is a tech manager. blake hr manager this is blake he is a hr manager. load data infile ldr_case11_2.dat "fix 68" 就是加载文件之前,先通过fix值属性指定每行的长度,这里每行 68个字符包括换行符在内, 到了指定长度就换行,不管中间有没有换行符,因此仅能用于定长 字符串的数据文件, 因为只有字符串定长,你才知道应该在infile处指定什么值 truncate into table manager ( ename position(1:8), job position(10:16), zhiwei position(*+1:22), remark position(*+1:65) ) windows中换行实际上由二个字符组成,回车加换行 chr(13)+chr(10), linux/unix下只需一个字符chr(10)即可 char_string:普通字符,即标准的可见字符, \n,表示换行, \t 表示行制表符, \f 表示换页 \v 表示列制表符 \r 表示回车 windows下用\r\n linux/unix下用\n就可以了 行尾部换行标识例: 数据文件 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. | 控制文件 load data infile ldr_case1_4.dat "str ' | \r\n" truncate into table manager fields terminated by ',' (mgrno,maname,job,remarek) 十,要导入大字段(lob类型) 1,数据保存在数据文件中 load data infile ldr_case12_1.dat "str '|\r\n'" truncate into table manager fields terminated by "," optionally enclosed by '"', (mgrno,mname,job,remark char(10000)) 假定remark列有大量文本,就可以这么定义 2,数据文件保存在独立的文件中 SQL> create table lobtbl( 2 fileowner varchar2(30), 3 filename varchar2(200), 4 filesize number, 5 filedata clob, 6 create_date date); 数据文件如下 ldr_case12_2.dat 2009-03-17 09:43 154 junsansi f:\oracle\script\ldr_case11_1.ctl 2009_03_17 09:44 189 junsansi f:\oracle\script\ldr_case11_1.dat 2009_03_17 09:45 2,639 junsansi f:\oracle\script\ldr_case11_4.log 控制文件如下 load date infile ldr__case12_2.dat truncate into table lobtbl (create_date position(1:17) date 'yyyy-mm-dd hh24:mi', filesize position(*+1:25) "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) 十一,某些字段有空值: load data infile ldr_case13.dat truncate into table bonus fields terminated by "," trailing nullcols (ename,job,sal) 十二,大量数据的导入 sqlldr scott/scott control=ldr_object.ctl errors=10 rows=640 表示10行出错就跳出,每次加载640行.默认是64行 如果640行太大,日志信息里面有提示,这个时候就要修改bindsize参数,默认就是256k,我们将其修改为10m( 1024X1024X10=10485760)同时这一次将行提高到5000行. 这样会更快, sqlldr scott/scott@riso_192.168.1.250 control=ldr_object.ctl errors=10 rows=5000 bindsize=10485760 但还可以更快. sqlldr scott/scott control=ldr_object.ctl direct=true; 但还可以更快 streamsize:直接路径加载默认读取全部记录, 因此不需要设置rows参数,读取到的数据处理后存入缓存区,即 streamsize参数,该参数为256k,这里加大到10mb date_cache: 该参数指定一个转换后日期格式的缓存区,以条为单位,默认值1000条,即保存1000条转换后的日 期格式, 由于我们要导入的数据中有日期格式,因此加大该参数到5000,以降低日期转换操作带来的开销. sqlldr scott/tiger control=ldr_object.ctl direct=true streamsize=10485760 date_cache=5000
还剩4页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 5 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

nonothing

贡献于2013-08-21

下载需要 5 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf