酱油是oracle菜鸟，贴出来一些学习的笔记，希望大神指点指点sqlldr导数据的时候，如果数据文件有一堆空格，可以这么整几个sqlplus的设置set termout off; 是否在屏幕上显示输出内容，off屏幕不显示查询语句，主要与spool结合使用set feedback off; 关闭本次sql命令处理的记录条数，默认为on即去掉最后的已经选择的行数set echo off; 关闭脚本中正在执行的SQL语句的显示set heading off; 关闭标题的输出，设置为off就去掉了select结果的字段名只显示数据set trimout on; 去除标准输出每行后面多余的空格set trimspool on; 将每行后面多余的空格去掉【linesize-实际字符数=多余空格】各个参数属性load datainfile '/home/oracle/sql_loader/test.txt' 待加载的数据文件badfile '/home/oracle/sql_loader/test_bad.txt' 格式不匹配写入坏文件discardfile'/home/oracle/sql_loader/test_discard.txt' 条件不匹配写入丢弃文件append into table test_loader 追加的方式插入数据fields terminated by "," 字段与字段之间的分隔符trailing nullcols 这句的意思是将没有对应值的列都置为null (owner,object_name,object_id,object_type) 数据插入的对应字段load datainfile '/home/oracle/emp.txt'badfile '/home/oracle/bad.txt'discardfile '/home/oracle/dis.txt'appendinto table t1fields terminated by ","trailing nullcols(empno "trim(:empno)",ename"trim(:ename)")（1）直接加载比传统加载效率要高（2）不扫描原来的空数据块（3）不需要sql解析，减少系统的负载（4）不经过SGA，而传统加载时经过SGA;（5）不走DBWR进程，走自己的专属进程，所以速度快直接加载限制：（1）不能加载簇表（2）锁定整个表，在表上有活动事务的时候不能加载直接加载特点：（1）直接加载是在所有数据块后面加载新数据块，修改高水位线，不扫描原来的空数据块。

（2）直接加载只产生一点点的管理redo，因为要修改数据字典（也可以讲不产生redo）。

（3）回滚，如果加载失败把新分配数据块抹掉就行了。

（4）无需SGA，无需SQL解析，无需DBWR进程SCOTT@POD>select owner||','||object_name||','||object_type from dba_objects where rownum<50001; 把object里的东西导进表里load datainfile '/home/oracle/sqlldr/test.txt'badfile '/home/oracle/sqlldr/bad.log'appendinto table testfields terminated by ','optionally enclosed by '"'trailing nullcols(owner "trim(wner)",object_name"trim(bject_name)",object_type"trim(bject_name)")默认加载[oracle@POD sqlldr]$ sqlldr scott/tiger control='/home/oracle/sqlldr/load.ctl'导入的时候触发commit point...Commit point reached - logical record count 48864Commit point reached - logical record count 48928Commit point reached - logical record count 48992Commit point reached - logical record count 49056Commit point reached - logical record count 49120Commit point reached - logical record count 49184Commit point reached - logical record count 49248Commit point reached - logical record count 49312Commit point reached - logical record count 49376Commit point reached - logical record count 49440Commit point reached - logical record count 49504Commit point reached - logical record count 49568Commit point reached - logical record count 49632Commit point reached - logical record count 49696Commit point reached - logical record count 49760Commit point reached - logical record count 49824Commit point reached - logical record count 49888Commit point reached - logical record count 49952Commit point reached - logical record count 50001...看下日志里的信息SQL*Loader: Release 11.2.0.1.0 - Production on Tue Mar 18 17:21:20 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Control File: /home/oracle/sqlldr/load.ctlData File: /home/oracle/sqlldr/test.txtBad File: /home/oracle/sqlldr/bad.logDiscard File: none specified(Allow all discards)Number to load: ALLNumber to skip: 0Errors allowed: 50Bind array: 64 rows, maximum of 256000 bytesContinuation: none specifiedPath used: ConventionalTable TEST, loaded from every logical record.Insert option in effect for this table: APPENDTRAILING NULLCOLS option in effectColumn Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- OWNER FIRST * , O(") CHARACTER SQL string for column : "trim(wner)"OBJECT_NAME NEXT * , O(") CHARACTER SQL string for column : "trim(bject_name)"OBJECT_TYPE NEXT * , O(") CHARACTER SQL string for column : "trim(bject_name)"Record 50001: Discarded - all columns null.Table TEST:50000 Rows successfully loaded.0 Rows not loaded due to data errors.0 Rows not loaded because all WHEN clauses were failed.1 Row not loaded because all fields were null.Space allocated for bind array: 49536 bytes(64 rows)Read buffer bytes: 1048576Total logical records skipped: 0Total logical records read: 50001Total logical records rejected: 0Total logical records discarded: 1Run began on Tue Mar 18 17:21:20 2014Run ended on Tue Mar 18 17:21:23 2014Elapsed time was: 00:00:02.79CPU time was: 00:00:00.17再用直接加在[oracle@POD sqlldr]$ sqlldr scott/tiger control='/home/oracle/sqlldr/load.ctl' direct=true SQL*Loader: Release 11.2.0.1.0 - Production on Tue Mar 18 17:28:32 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Control File: /home/oracle/sqlldr/load.ctlData File: /home/oracle/sqlldr/test.txtBad File: /home/oracle/sqlldr/bad.logDiscard File: none specified(Allow all discards)Number to load: ALLNumber to skip: 0Errors allowed: 50Continuation: none specifiedPath used: DirectTable TEST, loaded from every logical record.Insert option in effect for this table: APPENDTRAILING NULLCOLS option in effectColumn Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- OWNER FIRST * , O(") CHARACTER SQL string for column : "trim(wner)"OBJECT_NAME NEXT * , O(") CHARACTER SQL string for column : "trim(bject_name)"OBJECT_TYPE NEXT * , O(") CHARACTER SQL string for column : "trim(bject_name)"Record 50001: Discarded - all columns null.Table TEST:50000 Rows successfully loaded.0 Rows not loaded due to data errors.0 Rows not loaded because all WHEN clauses were failed.1 Row not loaded because all fields were null.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: 50001Total logical records rejected: 0Total logical records discarded: 1Total stream buffers loaded by SQL*Loader main thread: 19 Total stream buffers loaded by SQL*Loader load thread: 8Run began on Tue Mar 18 17:28:32 2014Run ended on Tue Mar 18 17:28:32 2014Elapsed time was: 00:00:00.49CPU time was: 00:00:00.17时间省了不少在配置文件中可以加上控制每多少行提交一次，最多多少错行后取消执行OPTIONS(ERRORS=500,ROWS=100000)小结：经过比对direct比conventional要提高了20倍效率，区别如下Direct 特点（1）数据绕过SGA直接写入磁盘的数据文件（2）数据直接写入高水位线HWM之后的新块，不会扫描HWM之前的空闲块（3）commit之后移动HWM他人才能看到（4）不对已用空间进行扫描（5）使用direct几乎不产生redo log，不是完全不产生(安全性差)，但会产生undo数据（6）适用OLAP在线分析场景，增删改不频繁的场景Conventional传统加载特点（1）数据先加载-> SGA -> 磁盘的数据文件（2）会扫描高水位线HWM之前的数据块，如果有空闲块(碎片经常DML导致)就会利用，如果没有再插入新块（3）高水位线HWM之前的数据块是放在SGA区的（4）会产生redo log和undo数据（5）安全性高，可恢复数据（6）传统加载与SQL语句insert插入没区别。