Oracle-工具之SQLLDR

大家好，欢迎来到IT知识分享网。

1. 什么是SQLLDR

SQLLDR是Oracle服务端自带的工具，用于快速导入大批量数据。

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-sql-loader.html

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的常用参数说明

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", FILLER_1 FILLER, --此列的值不会被装载，即数据文件中col_1对应的列的值不会被装载，这里要注意，如果你对col_1列有其他处理，这里的列名就不能写成col_1，需要重新编写一个列名，否则会报错："SQL*Loader-404: Column TMP1 present more than once in D_CBS_KDPL_ZHMINX's INTO TABLE block."，另外如果不适用TRAILING NULLCOLS，则会报错 col_1 CONSTANT 'UntifA', --此列的值不会使用数据文件中col_1对应的值，而是默认为为常量UntifA， col_2 "substr(:col4,-3,3)", --取col4的后三位 col_3, col_4, clo_5 "to_date('','YYYY-MM-DD')" --日期格式字段插入固定日期 取值的话应该写为 col_5 "to_date(:col_5,'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('','YYYY-MM-DD')" --日期格式字段插入固定日期 ) 

例子2：

OPTIONS (DIRECT=true,SKIP_INDEX_MAINTENANCE=true,PARALLEL=true) LOAD DATA INFILE 'nor.dat' BADFILE 'nor.bad' DISCARDFILE 'nor.dsc' APPEND INTO TABLE p95169.DISEASE_EXPERT_RELATION --APPEND -> 吃大亏！！！ 一定要放到INTO TABLE之前 不然使用WHEN的时候会报错！！！！！  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')" ) APPEND INTO TABLE p95169.DISEASE_EXPERT_RELATION --APPEND --APPEND -> 吃大亏！！！ 一定要放到INTO TABLE之前 不然使用WHEN的时候会报错！！！！！  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')" ) APPEND INTO TABLE p95169.DISEASE_EXPERT_RELATION --APPEND --APPEND -> 吃大亏！！！ 一定要放到INTO TABLE之前 不然使用WHEN的时候会报错！！！！！  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')" ) 

控制文件还有很多对表、对列的单独控制，如果有需求可以百度查找如何使用

实例：

sqlldr $DB_USER/$DB_PASSWD skip=1 control=ctlname.ctl parfile=parfilename.parfile 

SQL Loader的使用详解