1. 什么是SQLLDR
SQLLDR是Oracle服务端自带的工具,用于快速导入大批量数据。
2. 常规路径加载/直接路径加载
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操作将会被禁止,不能并发访问
3. SQLLDR的常用参数说明
属性值 | 含义 | 默认值或例子 |
---|---|---|
userid | 连接数据库的信息username/password@ip:prot/dbname | userid=UntifA/UntifA@192.168.101.88:1521/oracledb |
control | 控制文件:控制导入细节的文件 | control=control_name.txt |
parfile | 参数文件:包含参数细节的文件 | parfile=parfile_name.txt |
log | 日志文件 | 默认与控制文件同名.log或者自己指定log=log_name.log |
bad | 坏数据文件 | 默认与控制文件同名.bad或者自己指定bad=bad_name.bad |
discard | 丢弃的数据文件 | |
discardmax | 允许丢弃数据的最大值 | 全部 |
skip | 跳过的行/记录数 | 默认值为0 ,如果前5行为表头,则可以指定跳过表头所占的行数skip=5 |
load | 加载的记录数 | 默认值为全部 |
errors | 允许的错误记录数 | |
direct | 导入模式 | 默认为false:常规路径加载 true:直接路径加载 |
rows | 每次提交的记录数 | 常规路径:64 直接路径:全部 |
parallel | 并行导入,仅在直接路径加载时有效,推荐设置true | 默认值为false |
file | 并行加载时会用到该参数,指定file参数,要加载的内容即只向指定的数据文件写入数据,减少i/o | |
columnarrayrows | 指定直接路径加载时流缓冲区的行数 | 5000 |
streamsize | 指定直接路径加载时流缓冲区的大小(字节) | 256000 |
multithreading | 指定直接路径加载时是否启用多线程 | |
date_cache | 指定直接路径加载时日期转换用缓存大小(以条目为单位) | 1000 |
bindsize | 每次提交记录的缓冲区的大小(字节) | 256000 |
silent | 禁止输出信息(header,feedback,errors,discards,partitions) | |
skip_unusable_indexes | 不允许/允许不可用的索引或索引分区 | false |
skip_index_maintenance | 不维护索引,将受影响的索引标记为不可用 | false |
commit_discontinued | 停止加载时提交加载的行 | false |
external_table | 使用外部表进行加载 | 不使用 |
generate_only | sqlldr并不执行加载,而是生成创建外部表的sql和处理数据的sql,并保存在log文件中 | |
execute | 执行外部表并加载数据 | |
no_index_errors | 在任何索引错误上中止加载 | false |
4. 控制文件control=control_name.txt
控制文件指定了数据源、编码格式、列的控制方式等一系列参数,我们只举例说明其中的常用内容。
语法格式:
--关闭归档日志,提高导入速度(仅直接路径时有效)
--unrecoverable
LOAD DATA
--导入字符集格式
CHARACTERSET 'UFT8'
--1.指定要加载的数据文件
--INFILE 和INDDN是同义词,它们后面都是要加载的数据文件的绝对路径。如果用 * 则表示数据就在控制文件内。
--INFILE "/home/oracle/user/UntifA/load_file.txt"
[ { INFILE | INDDN } {file | * } ]
--BADFILE和BADDN是同义词。file指定坏数据保存的文件
--BADFILE "/home/oracle/user/UntifA/load_file.bad"
[{ BADFILE | BADDN } file ]
--DISCARDFILE和DISCARDDN是同义词。file指定丢弃的数据文件
--DISCARDFILE "/home/oracle/user/UntifA/load_file.dis"
[{ 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",
col_1 FILLER, --此列的值不会被装载
col_1 CONSTANT 'UntifA', --此列的值默认为常量UntifA
col_2 "substr(:col4,-3,3)", --取col4的后三位
col_3,
col_4,
clo_5 "to_date('20210202','YYYY-MM-DD')" --日期格式字段插入固定日期
)
例子1:
LOAD DATA
CHARACTERSET 'UFT8'
INFILE "/home/oracle/user/UntifA/load_file.txt"
BADFILE "/home/oracle/user/UntifA/load_file.bad"
DISCARDFILE "/home/oracle/user/UntifA/load_file.dis"
TRUNCATE
INTO TABLE test_01
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
id,--类型未指定时,默认为character,每个字段的实际解析类型见log文件
code integer,
name char(1000),
sum double,
create_date date "yyyy-mm-dd hh24:mi:ss",
col_1 FILLER, --此列的值不会被装载
col_1 CONSTANT 'UntifA', --此列的值默认为常量UntifA
col_2 "substr(:col4,-3,3)", --取col4的后三位
col_3,
col_4,
clo_5 "to_date('20210202','YYYY-MM-DD')" --日期格式字段插入固定日期
)
例子2:
OPTIONS (DIRECT=true,SKIP_INDEX_MAINTENANCE=true,PARALLEL=true)
LOAD DATA
INFILE 'nor.dat'
BADFILE 'nor.bad'
DISCARDFILE 'nor.dsc'
INTO TABLE p95169.DISEASE_EXPERT_RELATION
APPEND
WHEN len='3'
FIELDS TERMINATED BY WHITESPACE
(
len FILLER POSITION(1) CHAR,
DISEASEEXPERTUUID EXPRESSION "SYS_GUID()",
EXPERTUUID CHAR,
DISEASEUUID CHAR,
DISEASESORTCODE EXPRESSION "NULL",
DISEASENAME CHAR,
CREATEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')",
MODIFIEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')"
)
INTO TABLE p95169.DISEASE_EXPERT_RELATION
APPEND
WHEN len='2'
FIELDS TERMINATED BY WHITESPACE
(
len FILLER POSITION(1) CHAR,
DISEASEEXPERTUUID EXPRESSION "SYS_GUID()",
EXPERTUUID CHAR,
DISEASEUUID EXPRESSION "NULL",
DISEASESORTCODE EXPRESSION "NULL",
DISEASENAME CHAR,
CREATEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')",
MODIFIEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')"
)
INTO TABLE p95169.DISEASE_EXPERT_RELATION
APPEND
WHEN len='1'
FIELDS TERMINATED BY WHITESPACE
(
len FILLER POSITION(1) CHAR,
DISEASEEXPERTUUID EXPRESSION "SYS_GUID()",
EXPERTUUID CHAR,
DISEASEUUID EXPRESSION "NULL",
DISEASESORTCODE EXPRESSION "NULL",
DISEASENAME EXPRESSION "NULL",
CREATEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')",
MODIFIEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')"
)
控制文件还有很多对表、对列的单独控制,如果有需求可以百度查找如何使用
文章来源:https://www.toymoban.com/news/detail-673316.html
参考帖子:
[Oracle] SQL*Loader 详细使用教程(1)- 总览
[Oracle] SQL*Loader 详细使用教程(2)- 命令行参数
[Oracle] SQL*Loader 详细使用教程(3)- 控制文件
[Oracle] SQL*Loader 详细使用教程(4)- 字段列表文章来源地址https://www.toymoban.com/news/detail-673316.html
到了这里,关于Oracle工具之SQLLDR的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!