sqlldr是Oracle内置的⼯具，⽤来快速导⼊⼤批量数据
什么是sqlldr
sqlldr是Oracle内置的⼯具，⽤来快速导⼊⼤批量数据，直接在命令⾏调⽤。
sqlldr参数
下表是sqlldr的部分参数，其中加粗的为常⽤参数，标红为必需参数。
注：sqlldr的命令⾏参数可以直接写在命令⾏，也可以放在控制⽂件，也可以直接放在参数⽂件PARFILE⾥⾯（在参数重复指定时，命令⾏中的参数具有最⾼的权重）
属性值含义默认值
userid Oracle的username/password[@servicename]
control 控制⽂件
log ⽇志⽂件默认与控制⽂件同名
bad 坏数据⽂件默认与数据⽂件同名
discard 丢弃的数据⽂件
discardmax 允许丢弃数据的最⼤值全部
skip 跳过的⾏/记录数，⽐如导⼊的数据⽂件前⼏⾏是表头或者描述 0
load 加载的⾏/记录数全部
errors 允许的错误⾏/记录数 50
rows 每次提交的记录数，该参数受bindsize影响常规路径：64 直接路径：全部
bindsize 每次提交记录的缓冲区的⼤⼩（字节） 256000
silent 禁⽌输出信息（header,feedback,errors,discards,partitions）
parfile 参数⽂件：包含参数规范的⽂件名
direct 使⽤直通路径⽅式导⼊，不⾛buffer cache，通过direct path api发送数据到服务器端的加载引擎，加载引擎按照数据块的格式处理数据并直接写向数据⽂件，因此效率较⾼ false
parallel 并⾏导⼊，仅在直接路径加载时有效，推荐设置true false
file 并⾏加载时会⽤到该参数，指定file参数，要加载的内容即只向指定的数据⽂件写⼊数据，减少i/o
skip_unusable_indexes 不允许/允许不可⽤的索引或索引分区 false
skip_index_maintenance 不维护索引，将受影响的索引标记为不可⽤ false
commit_discontinued 停⽌加载时提交加载的⾏ false
readsize 缓冲区⼤⼩（字节） 1048576，最⼤不超过20m，该参数仅当从数据⽂件读取时有效，如果是从近制⽂件读取数据，则默认为64k external_table 使⽤外部表进⾏加载不使⽤
generate_only sqlldr并不执⾏加载，⽽是⽣成创建外部表的sql和处理数据的sql，并保存在log⽂件中
execute 执⾏外部表并加载数据
columnarrayrows 指定直接路径加载时流缓冲区的⾏数 5000
streamsize 指定直接路径加载时流缓冲区的⼤⼩（字节） 256000
multithreading 指定直接路径加载时是否启⽤多线程
date_cache 指定直接路径加载时⽇期转换⽤缓存⼤⼩（以条⽬为单位） 1000
no_index_errors 在任何索引错误上中⽌加载 false
sqlldr导⼊有两种模式，常规路径加载和直接路径加载，默认使⽤常规路径加载，当direct=y或者direct=true时，使⽤直接路径加载。直接路径加载⽐常规路径拥有更⾼的效率，但也有诸多限制。
常规路径加载
数据经过buffer cache，使⽤SQL处理数据，COMMIT提交操作，⼀次加载可能会涉及到多个事务处理，会产⽣⼤量undo数据（回滚数据）通过undo回滚
触发INSERT 触发器
操作过程中表仍可被并发访问
直接路径加载
数据不经过buffer cache，从PGA直接把数据格式化成Oracle块，再写⼊数据⽂件，⼏乎不会产⽣undo数据。但是如果表上有索引，会产⽣索引的undo数据，⽽且索引的块会被读进buffer cache，这将会花费⼤量时间在索引的维护上。因此，在向表中传送⼤量数据时，建议先将表上的索引设置为unusable（或者使⽤skip_index_maintenance=true），待插⼊结束后，再rebuild索引（alter index index_name rebuild nologging）
数据不会写⼊HWM（⾼⽔位线）以下的数据块，⽽是在HWM之后写⼊，通过HWM回滚
触发器在进⾏直接路径加载之前已禁⽤，在加载结束时会重新启⽤，如果重新启⽤时不能访问某个被引⽤对象，这些触发器可能会保持为禁⽤状态
操作过程中对应的表会将会被锁定，所有在这张表上的CRUD操作将会被禁⽌，不能并发访问
控制⽂件
控制⽂件为sqlldr的必需项，在这个⽂件⾥指定了数据源，编码，操作类型，要操作的表等⼀系列参数，控制⽂件常见设置如下：
--关闭归档⽇志，提⾼导⼊速度（仅直接路径时有效）
--unrecoverable
load data
--指定编码
characterset 'UTF8'
truncate删除数据
--1.指定要加载的数据⽂件
--INFILE 和INDDN是同义词，它们后⾯都是要加载的数据⽂件。如果⽤ * 则表⽰数据就在控制⽂件内。[ { INFILE | INDDN } {file | * } ]
--BADFILE和BADDN是同义词。file指定坏数据保存的⽂件
[{ BADFILE | BADDN } file ]
--DISCARDFILE和DISCARDDN是同义词。file指定丢弃的数据⽂件
[{ DISCARDFILE | DISCARDDN } file ]
--2.指定操作类型
--insert：默认值，装载空表，如果原先的表有数据，sqlloader会停⽌
--append：原先的表有数据就在表中追加新记录
--replace：删除旧记录(⽤ delete from table 语句)，替换成新装载的记录
--truncate：删除旧记录(⽤ truncate table 语句)，替换成新装载的记录
[ APPEND | REPLACE | INSERT | TRUNCATE ]
--3.指定操作的表
INTO TABLE [user.]table
--4.指定过滤条件
--[when id = id_memo]
--5.指定字段分隔符
--字段分隔符
fields terminated by ','
--字段⽤什么字符包括起来
optionally enclosed by '"'
--字段没有对应的值时允许为空
trailing nullcols
--6.指定表字段
--常见数据类型
--CHAR 字符
--DATE ⽇期
--INTEGER 整数
--FLOAT 普通符点
--DOUBLE 双精度符点
(
id,--类型未指定时，默认为character，每个字段的实际解析类型见log⽂件
code integer,
name char(1000),
sum double,
create_date date "yyyy-mm-dd hh24:mi:ss",
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
执⾏sqlldr
编写好控制⽂件l后，就可以通过命令⾏执⾏导⼊，由于我使⽤的数据源⽂件为PL/SQL直接导出的csv⽂件，因此要跳过第⼀⾏记录（表头），以下为windows下的使⽤⽰例：
//常规路径
//调整每次提交的⾏数和缓冲区⼤⼩，以减少事务提交的次数
sqlldr userid=xxx/xxxxxx@xxxxxx l log=test.log skip=1 rows=5000 bindsize=20971520 readsize=20971520
//直接路径
//开启parallel和unrecoverable（控制⽂件中）
sqlldr userid=xxx/xxxxxx@xxxxxx l log=test.log skip=1 direct=true parallel=true skip_index_maintenance=true
readsize=20971520 columnarrayrows=10000 streamsize=20971520 date_cache=5000
1
2
3
4
5
6
7
执⾏完后，结果将会保存⾄test.log⽂件中（不管成功还是失败），经测试，200万数据，使⽤常规路径加载在有索引的情况下导⼊时间约为15分钟左右，使⽤直接路径加载导⼊时间约为4分钟左右。
常见问题及解决
1.数据⽂件的字段超出最⼤长度：
检查报错字段长度是否超过255，若超过255，需要在控制⽂件中指明字段长度，如：char(1000)，否则Oracle会默认该字段为VARCHAR(255)/CHAR(255)
2. TERMINATED 和 ENCLOSED 字段后没有终⽌定界符：
1).检查数据源⽂件是否格式正确
2).检查报错字段的前⼀个字段是否为数字，如果该字段为数字，并且在控制⽂件中指定了数据类型，尝试将指定的数据类型删掉，使⽤默认的类型，避免转换时匹配不⼀致
3. ORA-01722: ⽆效数字：
检查报错字段在控制⽂件中是否指定了数据类型，尝试将指定的数据类型删掉，使⽤默认的类型，避免转换时匹配不⼀致
4.ORA-01438: 值⼤于为此列指定的允许精度：
检查数据库字段类型与控制⽂件中指定的字段类型是否⼀致
5.SQL*Loader-700: 执⾏重要的分配 [7] 时内存耗尽：
这个异常⼀般发⽣在直接路径加载时，如果表字段很多，或者每次向内存加载的⾏数过⼤时（columnarrayrows），sql*loader⽆法向系统申请到⾜够的内存空间，就会报出out of memory的错误。
检查执⾏的命令⾏⾥是否包含columnarrayrows参数，如果有，将该参数的值调⼩，如果没有，加⼊该参数并且设置值⼩于5000（5000为默认值）
6.sqlldr不是内部命令或外部命令：
<在Oracle安装路径的BIN⽂件夹⾥，先检查是否安装Oracle，如果未安装，则需要安装Oracle；如果已安装，则检查环境变量PATH是否包含Oracle安装路径的BIN⽂件夹，如果没有，在Path后加上Oracle_Path\BIN
7.SQL*Loader-704: 内部错误: ulconnect: OCIServerAttach [0]
ORA-12154: TNS: ⽆法解析指定的连接标识符
检查命令⾏userid参数的值username/password[@servicename]是否正确
8.sqlldr导⼊中⽂乱码：
检查Oracle导出服务端与导⼊服务端字符集是否⼀致

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。