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 的性能与并发操作
- ROWS 的默认值为 64,你可以根据实际指定更合适的 ROWS 参数来指定每次提交记录数。(体验过在 PL/SQL Developer 中一次执行几条条以上的 insert 语句的情形吗?)
2) 常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE),而直接将数据导入到数据文件中,可以提高导入数据的 性能。当然,在很多情况下,不能使用此参数(如果主键重复的话会使索引的状态变成UNUSABLE!)。
-
通过指定 UNRECOVERABLE选项,可以关闭数据库的日志(是否要 alter table table1 nologging 呢?)。这个选项只能和 direct 一起使用。
-
对于超大数据文件的导入就要用并发操作了,即同时运行多个导入任务.
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
,欢迎大佬前来教学。