Oracle sqlldr命令和ctl控制文件

oracle sqlldr命令与以及ctl文件

插入表的4种方式

insert，为缺省方式，在数据装载开始时要求表为空
append，在表中追加新记录
replace，(delete table) 删除旧记录，替换成新装载的记录
truncate，(truncate table)删除旧记录，替换成新装载的记录
具体操作如下：

第一步：先编辑好数据控制文件 xx.ctl，如lee.ctl

options(skip=1)   --跳过第一行（看实际情况）
load data #	加载数据
CHARACTERSET ZHS16GBK --转码
truncate --将表数据清空插入数据，此外还有insert、replace、append方式
into table lee 
fields terminated by '|'   --数据的分隔符，字段拆分
TRAILING NULLCOLS
(a,
 b,
 c,
 d char（2000）)    --数据为空则为空，括号里是字段（包括字段类型的转换），如果d列数据长度超过255（char类型长度），必须改为char(2000),具体见 https://blog.csdn.net/a545812327/article/details/102665871

第二步：在cmd命令行窗口执行以下语句

sqlldr icrm/icrm@18.20.20.20:1521/crmdb 
data = '/home/lee/dapai.dat'  #数据文件目录
bad = /home/lee/adpai.bad   #错误数据存放
control = /home/lee/adpai.ctl # 控制文件
direct = y  #这块需要特别注意，根据实际业务场景使用，不要随便使用
log = /home/lee/adpai.log    #日志文件
errors = 100000

常用参数：

Keyword	默认值	描述
userid		ORACLE 用户名/口令
control		控制文件名
log		日志文件名
bad		错误文件名
data		数据文件名
discard		废弃文件名
discardmax	全部	允许废弃的文件的数目
skip	0	要跳过的逻辑记录的数目
load	全部	要加载的逻辑记录的数目
errors		允许的错误的数目
rows	常规：64 默认路径：全部	常规路径绑定数组中或直接路径保存数据间的行数
bindsize	256000	常规路径绑定数组的大小
direct	FALSE	使用直接路径
parfile		参数文件: 包含参数说明的文件的名称
parallel	FALSE	执行并行加载
file		执行文件
skip_unusable_indexes	FALSE	不允许/允许使用无用的索引或索引分区
skip_index_maintenance	FALSE	没有维护索引, 将受到影响的索引标记为无用
commit_discontinued	FALSE	提交加载中断时已加载的行
readsize	1048576	读取缓冲区的大小
external_table	NOT_USED	使用外部表进行加载;
NOT_USED, GENERATE_ONLY, EXECUTE
columnarrayrows	5000	直接路径列数组的行数
streamsize	256000	直接路径流缓冲区的大小 (以字节计)
multithreading		在直接路径中使用多线程
resumable	FALSE	启用或禁用当前的可恢复会话
resumable_name		有助于标识可恢复语句的文本字符串
resumable_timeout	7200	RESUMABLE 的等待时间 (以秒计)
date_cache	1000	日期转换高速缓存的大小 (以条目计)

有关 SQL *Loader 的性能与并发操作

1. ROWS 的默认值为 64，你可以根据实际指定更合适的 ROWS 参数来指定每次提交记录数。(体验过在 PL/SQL Developer 中一次执行几条条以上的 insert 语句的情形吗？)

2） 常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中，可以提高导入数据的 性能。当然，在很多情况下，不能使用此参数(如果主键重复的话会使索引的状态变成UNUSABLE!)。

3. 通过指定 UNRECOVERABLE选项，可以关闭数据库的日志(是否要 alter table table1 nologging 呢?)。这个选项只能和 direct 一起使用。

4. 对于超大数据文件的导入就要用并发操作了，即同时运行多个导入任务.

sqlldr userid=/ control=result1.ctl direct=true parallel=true
sqlldr userid=/ control=result2.ctl direct=true parallel=true
sqlldr userid=/ control=result2.ctl direct=true parallel=true

当加载大量数据时（大约超过10GB），最好抑制日志的产生：

SQL>ALTER TABLE RESULTXT nologging;

这样不产生REDO LOG，可以提高效率。然后在 CONTROL 文件中 load data 上面加一行：unrecoverable， 此选项必须要与DIRECT共同应用。

在并发操作时，ORACLE声称可以达到每小时处理100GB数据的能力！其实，估计能到 1－10G 就算不错了，开始可用结构 相同的文件，但只有少量数据，成功后开始加载大量数据，这样可以避免时间的浪费。

ctl文件注意

使用sqlldr灌数的时候经常会发生明明字段都是一样长度的但是一直报字段超长的情况，通常这时候有两种常见情况：

建表：

create table lee(
lee VARCHAR(2000)
);

使用sqlldr导数时出现如下错误：

" Record 1: Rejected - Error on table PC_PLANNAME, column PLANNAME.
Field in data file exceeds maximum length "

1、数据文件是gbk格式，你的目标数据库是utf-8格式，这两种格式的中文需要扩大1.5倍字段长度。
2、数据长度超过255（char类型长度）。

原来表中定义的字段 lee 虽然是VARCHAR(2000);
但是用控制文件(CTL)中默认为是char类型。
所以，当该列数据长度超过255（char类型长度）时会提示出错。
解决办法：
将CTL文件中的 "lee " 改为 “lee char(2000)” 即可！注意是 char 类型 而不是 varchar类型！

at last:
想建一个数据库

技术的交流群，用于磨炼提升技术能力.群号： 130730832 ,欢迎大佬前来教学。