当前位置：首页 > 数据库 > oracle > 详细页面

关于Oracle中SqlLoader的用法

时间：2023-10-31来源：系统城装机大师作者：佚名

简介：

SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件（数据）迁移到ORACLE数据库中。SQL*LOADER是大型数据仓库选择使用的加载方法，因为它提供了最快速的途径（DIRECT，PARALLEL）。

目前的理解就是：把文本文件，log文件，excel导入到数据库中

写sqlldr只需2步：

1：建立一个ctl控制文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 OPTIONS (skip=1,rows=128) #sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行
LOAD DATA #控制文件标识
INFILE '/app/audit/shell/monitor/inter.txt' #要导入的文件。目前搜索资料看到的可以是log文件，txt文本文件，excel文件
BADFILE '/app/audit/shell/monitor/inter.dat' #BADFILE 坏数据。这里还可以使用DISCARDFILE 来指定丢弃数据的文件，
truncate #操作类型，用 truncate table 来清除表中原有记录 ,也可以用 insert，append，replace等代替truncate，下面会详细讲解
into table ts_of_ceshi_mid1 #往ts_of_ceshi_mid1中插入数据
Fields terminated by "|" #用于分割一行中各个属性值的符号（例如每个属性值用逗号分割，那么就把 "|" 改为 ","）
Optionally enclosed by '"' #数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时
TRAILING NULLCOLS #表的字段没有对应的值时允许为空如果不加入这行导入到数据库的时候就会自动跳过字段是null值的那一列, 这样数据插入后就会属性和值对应不上
(
orig_name,
true_name,
status,
rec_num number, #字段可以指定类型，否则认为是 CHARACTER 类型, log 文件中有显示
last_rec_num,
last_cycle,
last_status,
finsh_date DATE "YYYY-MM-DD HH24:MI:SS" #可以指定接受日期的格式，相当用 to_date() 函数转换
)

2：在命令窗口执行，或者在shell脚本中写

1	`nohup sqlldr 用户名/密码@数据库名称 control=/app/audit/txt/inter.ctl log=/app/audit//txt/inter.log`

nohup可以不用写，我这是挂到后台执行，所以用nohup。可以直接写：

1	`sqlldr 用户名/密码@数据库名称 control=/app/audit/txt/inter.ctl log=/app/audit//txt/inter.log`

扩展： sqlldr参数

有效的关键字:
userid -- ORACLE username/password
control -- 控制文件
log -- 记录的日志文件
* -- 表示数据文件在控制文件之后，如果是独立的数据文件，则把文件代替*
bad -- 坏数据文件，记录错误的未加载数据
data -- 数据文件,* data参数只能指定一个数据文件,如果控制文件也通过infile指定了数据文件,并且指定多个, 则sqlldr在执行时,先加载data参数指定的数据文件,控制文件中第一个infile指定的数据文件被忽略, 但后续的infile指定的数据文件继续有效
discard -- 丢弃的数据文件,默认情况不产生,必须指定
discardmax -- 允许丢弃数据的最大值 (全部默认)
skip -- 跳过记录数,从数据文件中,从第一行开始要计算要跳过的行数,*,对于多表加载的情况,如果有when条件判断的话，或者直接路径下的多表加载，如果要加载的记录数不同，则该参数无效.
load -- 给你一万行的数据，却只要求你导入10行
errors -- 允许的错误记录数,超过则终止任务 (默认50)
rows -- 常规路径导入时:指绑定数组中的行数;直接路径导入时,指一次从数据文件只读取的行数，该参数同时受bindsize制约,如果rows*每行实际占用大小超出bindsize最大可用值，则rows自动降低达到bindsize最大可用值（每次提交的记录数，默认: 常规路径 64, 所有直接路径）
bindsize -- 为绑定数组指定的最大可用空间，用来存贮一次读取的rows的记录,该值不能太小,至少要放入一条逻辑记录但设置太大也没什么作用。每次提交记录的缓冲区的大小(字节为单位，默认256000)
bind array size=(number of rows)*(sun(fixed field lengths)+sum(maximum varying field lengths)+((number of varying length fields)*(size of length indicator)))
silent -- 禁止输出信息 (header,feedback,errors,discards,partitions)
sqlldr scott/scott control=ldr_case9.ctl silent=header 这样就可以不输出头部信息了.
direct -- 使用直通路径方式导入,不走buffer cache,通过direct path api发送数据到服务器端的加载引擎，加载引擎按照数据块的格式处理数据并直接写向数据文件,因此效率较高(默认FALSE)
parfile -- 高密度的sqlldr导入,省得写参数，就建个脚本吧
parallel -- 并行导入仅在直接路径加载时有效(默认FALSE)
file -- 并行加载时会用到该参数,指定file参数,要加载的内容即只向指定的数据文件写入数据，减少i/o
skip_unusable_indexes --默认为false,如果是true,则加载完数据时，就算此表索引不可用,数据加载完不会改变此索引状态,oracle 数据库中也有此同名参数，但顺序是先看sqlldr,再数据库
skip_index_maintenance --是否跳过索引维护，默认false,直接路径加载有效，如果设置为true,因加载完数据不维护索引,因此索引会失效.
readsize -- 缓冲区大小,默认值:1048576单位字节,最大不超过20m,该参数仅当从数据文件读取时有效，如果是从近制文件读取数据，则默认为64k
external_table -- not_used:不使用外部表，通过常规路径或直接路径加载数据
enerate_only --sqlldr并不执行加载,而是生成创建外部表的sql和处理数据的sql，并保存在log文件中,用户可以修改后拿到sqlplus中执行
execute -- 执行外部表并加载数据
columnarrayrows --指定直接路径加载时流缓冲区的行数 Number of rows for direct path column array(默认5000)
streamsize : --指定直接路径加载时流缓冲区的大小 Size of direct path stream buffer in bytes(默认256000)
multithreading --是否启用多线程，多cpu为true,单cpu false,直接路径加载时有效use multithreading in direct path
resumable --会话等待空闲空间分配,在执行sqlldr时，如果余下空间不足，false参数则直接报错退出,如果设置为true,则等待，让dba手动处理,达到resumable_timeout参数中指定的超时时间，再退出
enable or disable resumable for current session(默认FALSE)
resumable_name --会话标示名，通过查询user_resumable或dba_resumable二个字典来获取信息,text string to help identify resumable statement
resumable_timeout --会话超时,在多少时间未能执行sqlldr则退出,设置true有效，wait time (in seconds) for RESUMABLE(默认7200)
date_cache --日期转换用缓存在，用于提高转换效率.仅在直接路径加载时有用，size (in entries) of date conversion cache(默认1000)