sql*loader

简介

SQL*Loader（SQLLDR）是Oracle的高速批量数据加载工具。这是一个非常有用的工具，可用于多种平面文件格式向Oralce数据库中加载数据。SQLLDR可以在极短的时间内加载数量庞大的数据。它有两种操作模式。
传统路径：（conventional path）：SQLLDR会利用SQL插入为我们加载数据。
直接路径(direct path)：采用这种模式，SQLLDR不使用SQL；而是直接格式化数据库块。
利用直接路径加载，你能从一个平面文件读数据，并将其直接写至格式化的数据库块，而绕过整个SQL引擎和undo生成，同时还可能避开redo生成。要在一个没有任何数据的数据库中充分加载数据，最快的方法就是采用并行直接路径加载。
要使用SQLLDR，需要有一个控制文件（control file）。 控制文件中包含描述输入数据的信息（如输入数据的布局、数据类型等），另外还包含有关目标表的信息。控制文件甚至还可以包含要加载的数据。
注：简介部分来着百度，如有侵权请联系删除

简单样例一

建表：
create table WCG_LDR_TEST
(
v_id NUMBER(18),
v_name VARCHAR2(4000),
v_text VARCHAR2(4000),
v_date DATE,
v_test VARCHAR2(64)
);
建立控制文件wcg_ldr_test.ctl：
load data
INFILE *
insert
CONCATENATE 4
into table wcg_ldr_test
(
v_id position (1:2) integer external,
v_name position (3:6) char “upper(:v_name)”,
v_text position (7:10) char
)
BEGINDATA
123456789ABCDEFG
34sdkajsyuebcjuhgduabcj

执行：sqlldr userid=wcg/123456 control=wcg_ldr_test.ctl
#数据库去查询表wcg_ldr_test就已经有数据装载进入

语法说明：

load data
INFILE *
/*
1> INFILE *:代表导入的数据在当前控制文件中，BEGINDATA关键字换行后面的就是导入的数据。换句话说如果导入的数据在控制文件中，关键字BEGINDATA是必须的
2> 如果想把导入的数据放到单独的文件，则把INFILE 换成 INFILE ‘文件路径/impdata.dat’,impdata为自定义的文件名，dat为特定的文件类型

*/

insert
/*
1> insert为关键字，要装载的表必须是空的
2> replace为关键字，先删除表在装载。在使用replace开始作业时看到进度一直不动是因为正在删除装载表的数据
3> append为关键字，在原来的基础上添加
*/
CONCATENATE 4
/*
1> CONCATENATE 4 :CONCATENATE能把文件中的行合并，4代表四个物理行合并成一个逻辑行。这个关键字不是必加的，不加时默认一行是一个记录。
加的话必须在insert、replace、append后面
2> CONTINUEIF this (8:8) = ‘8’ #代表每个物理行的第八个字节如果等于8时，就把下一行和当前行合并成一个物理行，
如果下一行的第八位依然是8，则会是3个物理行合并成一个逻辑行.合并后用于比较的字符不会体现在合并的行中，例如本例中第八个字符8会被移除
3> CONTINUEIF last =‘j’ #代表每个物理行的最后一个字符是j时就会把下一个物理行和当前行合并成一个逻辑行，字符j依然会体现，不会像CONTINUEIF this中被移除
4> CONCATENATE 和 CONTINUEIF都不是必须参数，建议不要使用，他们会影响sqlloader的执行速度，因为当前这两个参数会导致sqlloader进行额外的扫描
*/
into table wcg_ldr_test
/*
如果此处加入以下可选参数说明：
#trailing nullcols:数据文件中没有，但是控制文件中有的列，这些列通过该语句置null
*/
(
v_id position (1:2) integer external,
v_name position (3:6) char “upper(:v_name)”,
v_text position (7:10) char
/*
1> 这部分是数据文件和数据库字段的映射，以上例子中v_id、v_name、v_text都是数据库的表字段
2> position 是关键字
3> 类似于(1:2)是说取逻辑行的第一到第二个字符
4> integer external、char是取值的数据类型，和数据库的对应字段的数据类型关联，
支持的数据类型有：INTEGER(n) --二进制整数，其中n可以是1 2 4或8、SMALLINT、CHAR、INTEGER EXTERNAL、FLOAT EXTERNAL 、DECIMAL EXTERNAL
5> position 是指定数据的位置，当然也可以使用分隔符TERMINATED BY ','说明以逗号为分隔，使用的映射例子如：
v_id terminated by ‘,’,
v_name terminated by ‘,’,
v_text terminated by ‘,’
然也可以使用全局设置，例如下insert 表名后面使用FIELDS TERMINATED BY ‘,’
注：TERMINATED BY WHITESPACE 说明以空格为分隔符
6> 还有一个可选参数optionally enclosed by '"'它数据文件的对于字段是由双引号括括起来的，数据导入时不用导入双引号
（因为Oracle的数据导出字符串类型默认都加了双引号），例如一行数据12,“34” ，如果你不加optionally enclosed by ‘"’ 那么导入数据库存储就是"34"，
两边的双引号也被当成了内容。可以和terminated by在同一个地方使用，例子如下：
v_id terminated by ‘,’,
v_name terminated by ‘,’ optionally enclosed by ‘"’,
v_text terminated by ‘,’
7> 现实使用时能使用position确定字段位置时尽量使用。因为TERMINATED BY 会要寻找分隔符而进行多余的扫描，效率也会有响应降低
8> 在数据装载时也可以使用函数，例如上面的upper(:v_name)
9> 映射字段如果不想从数据文件中获取，ldr还支持自动生成：
常量值例子：v_textconstant “wcg” #那么生成的每一行v_text字段值都是wcg
表达式值：由SQL操作符或者plsql函数生成，例如：v_text expression “TO_CHAR(sysdate,‘yyyymmddhh24miss’)” #expression时关键字
系统日期例子：v_date sysdate
序列值例子：v_id sequence(max,1)
行数号记录值例子：v_id recnum #recnum为关键字
获取数据库用户：v_name “USER” #把数据库用户填充到v_name字段，例如我的数据库执行用户是wcg,填充到v_name就是wcg 注:USER必须使用双引号括起来，不区分大小写
)*/
BEGINDATA
123456789ABCDEFG
34sdkajsyuebcjuhgduabcj

关键字说明(这些关键参数主要按分类来说的话主要在参数文件中)：
#userid：户名跟着要操作的数据库用户名和密码
#control：用户指定执行时使用的控制文件
#data：如果数据文件是单独的文件（不是直接在控制文件跟在BEGINDATA），data用于指定读取的数据文件。使用例子：data=‘wcg_ldr_test.dat’(数据文件后缀名统一为dat)
#bindsize：为指定一次处理的数组大小。一般要处理的数据都很大，那么ldr就会分批处理。bindsize就是定义一次处理的最大字节。例子：bindsize=512000
#rows：它的功能和bindsize一样。它是从行上进行单次处理数据的限制。如果两个都配置，ldr会去较小的那个。使用例子：rows=6400
#dirrct：direct=true时，sqlloader使用直接路径的方法装载，为false时是使用传统的方法进行加载。默认是false
#errors:允许的错误的数目(默认 50),例子：errors=99
#load:要加载的逻辑记录的数目(全部默认),就是可以用来限定处理的数据的条数，例子：load=10000
#log:指定的日志文件，例子：log=’/home/wcg_ldr_test.log’
#bad:指定用来记录那些装载失败数据的文件，例如你有100条数据需要装载，成功99条，失败了1条。失败的这条需要一个文件来记录。如果不指定的话sqlloader会
生成一个和控制文件同名的文件来记录。我们俗称这类文件为坏文件，后缀名为bad,例子：bad=’/home/wcg/wcg_ldr_test.bad’
#silent：隐藏运行过程中屏幕上显示的消息，all是所有信息都不显示
#parallel:是否运行并行加载，默认为false，例子：parallel=true
#resumable:当装载遇到错误时是否运行程序挂起，例如表空间不足时，是否运行程序挂起等待问题处理。和ddl操作时锁等待功能是否开启类似
#resumable_name:为上一个参数resumable指定一个名词。默认为用户名、会话id和实例ID的组合，只有resumable=true时有用
#resumable_timeout:resumable参数的超时时间，只有resumable=true时有用。默认7200秒
#skip:装载开始的行数，在我们装载过程中有可能中途出现意外停止了。下次继续装载时可以使用skip跳过我们装载过的内容，例子：skip=99999

涉及文件说明：
1>后缀名ctl – 控制文件名
2>后缀名log – 日志文件名
3>后缀名bad – 错误文件名（外部文件中不符合终止要求，不入库的数据）
4>后缀名dat – 数据文件名
5>后缀名dsc – 废弃文件名（也就是不符合where条件内容的数据，装载可以加where条件的）

sql*loader调用方式：

1> 直接命令调用：sqlldr userid=wcg/123456 control=wcg_ldr_test.ctl log=wcg_ldr_test.log …
这样调用的话在参数较多的情况下比较混乱，而且命令的数据字数系统也是有一定限制的
2>使用参数文件par,例如建立一个参数文件wcg_ldr_test.par 在文件里面定义：
userid=wcg/123456
control=wcg_ldr_test.ctl
log=wcg_ldr_test.log
…
然后使用sqlldr parfile=wcg_ldr_test.par来执行。这是比较建议的方式，可视性比较强
3> 如果我们为了确保密码的安全性，不想在配置文件中写userid。那么可以使用命令时不填写userid,类似sqlldr control=wcg_ldr_test.ctl
然后系统会提示手动输入的

直接装载和传统装载

传统装载方法使用SQL insert 语句将数据插入到表中，一次插入一个绑定数组大小的数据。直接装载不使用sql insert语句将数据装载到表中，而是格式化Oracle数据块，然后再直接把
它们写入数据文件。这种直接写入的过程省去直接装载表的SQL语句的大量开销。因为直接路径装载方法不争夺数据库资源，所以比传统数据装载快得多。对于量更大的数据装载
，用直接装载方法更好，而且在传统装载所需时间超过要求时，它是唯一的方法。

对比这两种加载方式的区别：
直接装载
1>数据绕过SGA直接写入磁盘的数据文件。
2>数据直接写入高水位线HWM之后的新块，不会扫描HWM之前的空闲块。
3>不对已用空间进行扫描。
4>通过设置UNRECOVERABLE=Y参数，直接装载中可规避写重做信息
5>适用OLAP在线分析场景，增删改不频繁的场景。
6>直接装载不会触发触发器、外部键和检查约束。但是唯一、主键和非空约束还是保留的
受到的限制：
1>不能装载正在使用的群表
2>不能同时装载父表和子表
3>不能装载VARRAY或BFILE列
4>不能在正在异构平台上使用Oracle Net执行装载
5>装载过程中不是使用函数

传统加载特点
1>数据先加载 -> SGA -> 磁盘的数据文件。
2>会扫描高水位线HWM之前的数据块，如果有空闲块(碎片经常DML导致)就会利用，如果没有再插入新块。
3>高水位线HWM之前的数据块是放在SGA区的。
4>会产生redo log和undo数据。
5>安全性高，可恢复数据。
6>传统加载与SQL语句insert插入没区别。

直接装载选项：
direct:如果要是用直接装载，则这个参数必须为direct=true
data_cache:这个参数设置时可以做到相同时间缓存的作用，例如设置data_cache=1000,那么它们缓存1000个装载的日期或者时间戳。
那么缓存的这些时间数据就不用重复装换，因为我们装载常常涉及时间转换（ps:这个参数我没验过，使用的话自己验证一下）
rows:用于限定装载文件的数量，例如：rows=10000,那么它便只会装载10000条
unrecoverable:用户限定重做日志的最小化产生，例如：unrecoverable=true
skip_index_maintenance:用户决定在装载时是否同时维护索引，默认值为false，当为true时便不会维护索引
sorted_indexes:用户决定数据在装载时按某个索引的排序进行装载（ps：传闻这个参数的设置能提升装载的性能，没测过）
columnarrayrows:用户限定单次装载的数组大小，例如：columnarrayrows=10000，那么它一次装载10000条。也就相当于单次事务的大小
streamsize:设置缓存流的大小，单位是字节
multithreading:设置是否开启并行处理，默认为true开启

样例一：装载数据时使用when字句过滤数据

建表：
create table WCG_LDR_TEST
(
v_id NUMBER(18),
v_name VARCHAR2(4000),
v_text VARCHAR2(4000),
v_date DATE,
v_test VARCHAR2(64)
);

控制文件wcg_ldr_test.ctl（测试阶段为了尽量减少工作，就没有使用参数文件，而且只配置了一些主要参数，其他都使用缺省的）：
load data
INFILE *
DISCARDFILE test.dsc

insert
into table wcg_ldr_test
when v_test != ‘12’
(
v_test position(1:2) char,
v_id recnum,
v_name constant “wcg”,
v_text expression “TO_CHAR(sysdate,‘yyyymmddhh24miss’)”,
v_date sysdate
)
BEGINDATA
a12,“34”,56789,ABCDEFG834sdkajsyuebcjuhgduabcj
12,dn,fjk8nc,iojdno1111j
34,qdw,qcerve,vbrgbj

执行命令：
sqlldr wcg/123456 control=wcg_ldr_test.ctl #wcg/123456 为数据库用户名/登录密码

结果：
SQL> select * from wcg_ldr_test;

V_ID V_NAME V_TEXT V_DATE V_TEST

---

1 wcg       20211026083145         2021/10/26/  a1
3 wcg       20211026083145         2021/10/26/  34

#如控制文件中when v_test != '12’筛选所示，装载的结果只有两条，v_test为’12’的数据被排除了

样例二：将长度大于255字节的数据装载到某个字段

如果在做字段映射时使用char,那么就会报Field in data file exceeds maximum length，
即使映射对于的数据库表字段是varchar2(4000),那么我们在在写控制文件中的映射时就需要使用char(1000)代替，这个1000是自定义的，符合你要装载内容大小就行
建表：
create table WCG_LDR_TEST
(
v_id NUMBER(18),
v_name VARCHAR2(4000),
v_text VARCHAR2(4000),
v_date DATE,
v_test VARCHAR2(64)
);
控制文件：
load data
INFILE *
DISCARDFILE test.dsc

insert
into table wcg_ldr_test
FIELDS TERMINATED BY ‘,’
(
v_id integer external,
v_name char,
v_text char,
v_test char(1000)
)
BEGINDATA
312,“34”,56789,ABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjBCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcj12w1reregergfwef
12,dn,fjk8nc,iojdno1111j
34,qdw,qcerve,vbrgbj

#如果v_test char(1000)不是这样定义映射，而是直接v_test char则会报Field in data file exceeds maximum length。对应的行就会装载失败

样例三：将表数据装载到ASCII文件

Oracle用户下建立一个sql文件，名称自定义，例如：wcg_test.sql 然后把如下内容复制到sql文件中（select部分自定义），然后登录Oracle用户执行
SET TERMOUT OFF
SET PAGESIZE 0
SET ECHO OFF
SET FEED OFF
SET HEAD OFF
SET LINESIZE 100
COLUMN v_id FORMAT 999,999
COLUMN v_name FORMAT a15
COLUMN v_text FORMAT a25
SPOOL test.txt
select v_id,v_name,v_text from wcg_ldr_test;
SPOOL OFF

注：
SET TERMOUT：显示脚本中的命令的执行结果，缺省为on
SET ECHO:设置运行命令是否显示语句，缺省为on
SET FEED:回显本次sql命令处理的记录条数，缺省为on
SET HEAD:
SET LINESIZE :输出一行字符个数，缺省为80
set pagesize:输出每页行数，缺省为24，为了避免分页，可设定为0
set trimout:去除标准输出每行的拖尾空格，缺省为off
FORMAT a15：设置列宽为15，但是不能这样设置数字类型，数字类型要如第一个。具体可以搜关键字"oracle 格式化输出"

样例四：UTL_FILE写文件简单样例

UTL_FILE是oracle提供的一个标准的工具包，用来读写文件使用
使用
创建directory目录：create or replace directory wcg_test_dir as ’ /home/oracle ’ ;
#/home/oracle可以是任意Oracle属主的目录，当前directory创建后，后期就可以使用该directory下的文件与数据库进行交互
#wcg_test_dir 为自定义的一个名词

授权：grant read,write on directory wcg_test_dir to wcg;
#wcg为需要对wcg_test_dir这个directory操作的用户
操作模式：

‘r’：读文件(文本)，一定要保证有该文件，不然会报UTL_FILE.INVALID_PATH异常
‘w’：写文件(文本)，没有该文件的话会自动添加；有的话会覆盖
‘a’：追加文件(文本)，如果文件不存在，则会以write模式创建此文件
->带有’b’后缀的为使用byte(字节)模式，BLOB与VARCHAR2不一样，BLOB打开时一定要用带有’b’后,例如rb、wb、ab

declare
file utl_file.file_type;
begin
file:= utl_file.fopen(‘HOME_ORACLE_DIR’,‘abc.txt’,‘a’);
utl_file.put_line( file , ’ I am writing to file ’ );
utl_file.fclose( file );
end ;
/

样例五：同时往两个表插入数据

建表：
create table WCG_LDR_TEST01
(
v_id NUMBER(18),
v_name VARCHAR2(4000),
v_text VARCHAR2(4000),
v_date DATE,
v_test VARCHAR2(64)
);

create table WCG_LDR_TEST
(
v_id NUMBER(18),
v_name VARCHAR2(4000),
v_text VARCHAR2(4000),
v_date DATE,
v_test VARCHAR2(64)
);

控制文件wcg_ldr_test.ctl：
load data
INFILE ‘wcg_ldr_test.dat’
DISCARDFILE test.dsc

insert
into table wcg_ldr_test
when v_name = ‘12’
(
v_name position(1:2) char
)
into table wcg_ldr_test01
when v_name = ‘12’
(
v_name position(1:2) char
)

数据文件wcg_ldr_test.dat：
312,“34”,56789,ABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjBCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcj12w1
12,dn,fjk8nc,iojdno1111j
34,qdw,qcerve,vbrgbj

执行命令：sqlldr userid=wcg/123456 control=wcg_ldr_test.ctl
#如果是使用position这样的位置来获取数据是可以使用上面的例子，但是映射时使用分隔符来获取，那么从第二张表开始就必须加上filler position(1)，
它注明开始的行数。控制文件wcg_ldr_test.ctl如下：
load data
INFILE ‘wcg_ldr_test.dat’
DISCARDFILE test.dsc

insert
into table wcg_ldr_test
when v_name = ‘dn’
fields terminated ‘,’
(
v_id integer external,
v_name char,
v_text char,
v_test char(1000)
)
into table wcg_ldr_test01
when v_name = ‘dn’
fields terminated ‘,’
(
v_id filler position(1) integer external,
v_name char,
v_text char,
v_test char(1000)
)

样例六:参数文件、控制文件、数据文件结合使用的例子

建表：
create table WCG_LDR_TEST
(
v_id NUMBER(18),
v_name VARCHAR2(4000),
v_text VARCHAR2(4000),
v_date DATE,
v_test VARCHAR2(64)
);
数据文件wcg_ldr_test.dat：
312,“34”,56789,ABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjBCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcj12w1reregergfwef
12,dn,fjk8nc,iojdno1111j
34,qdw,qcerve,vbrgbj

控制文件wcg_ldr_test.ctl：
load data
DISCARDFILE test.dsc

insert
into table wcg_ldr_test
fields terminated ‘,’
(
v_id integer external,
v_name char,
v_text char,
v_test char(1000)
)

参数文件wcg_ldr_test.par：
userid=wcg/123456
control=wcg_ldr_test.ctl
log=wcg_ldr_test.log
errors=99
data=wcg_ldr_test.dat
bindsize=512000
silent=all
parallel=false

执行：
sqlldr parfile=wcg_ldr_test.par