sqlldr的使用
收到一个任务,需要往oracle库里导入很多excel文件,而且excel文件很大,每个都在500M左右,最后考虑采用sqlldr。先来了解下sqlldr[oracle@linux ~]$ sqlldr SQL*Loader: Release 11.2.0.1.0 - Production on 星期四 3月 1 13:15:47 2012 Copyri
收到一个任务,需要往oracle库里导入很多excel文件,而且excel文件很大,每个都在500M左右,最后考虑采用sqlldr。先来了解下sqlldr
- [oracle@linux ~]$ sqlldr
- SQL*Loader: Release 11.2.0.1.0 - Production on 星期四 3月 1 13:15:47 2012
- Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
- 用法: SQLLDR keyword=value [,keyword=value,...]
- 有效的关键字:
- userid -- ORACLE 用户名/口令
- control -- 控制文件名
- log -- 日志文件名
- bad -- 错误文件名
- data -- 数据文件名
- discard -- 废弃文件名
- discardmax -- 允许废弃的文件的数目 (全部默认)
- skip -- 要跳过的逻辑记录的数目 (默认 0)
- load -- 要加载的逻辑记录的数目 (全部默认)
- errors -- 允许的错误的数目 (默认 50)
- rows -- 常规路径绑定数组中或直接路径保存数据间的行数
- (默认: 常规路径 64, 所有直接路径)
- bindsize -- 常规路径绑定数组的大小 (以字节计) (默认 256000)
- silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)
- direct -- 使用直接路径 (默认 FALSE)
- parfile -- 参数文件: 包含参数说明的文件的名称
- parallel -- 执行并行加载 (默认 FALSE)
- file -- 要从以下对象中分配区的文件
- skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区 (默认 FALSE)
- skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用 (默认 FALSE)
- commit_discontinued -- 提交加载中断时已加载的行 (默认 FALSE)
- readsize -- 读取缓冲区的大小 (默认 1048576)
- external_table -- 使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE (默认 NOT_USED)
- columnarrayrows -- 直接路径列数组的行数 (默认 5000)
- streamsize -- 直接路径流缓冲区的大小 (以字节计) (默认 256000)
- multithreading -- 在直接路径中使用多线程
- resumable -- 启用或禁用当前的可恢复会话 (默认 FALSE)
- resumable_name -- 有助于标识可恢复语句的文本字符串
- resumable_timeout -- RESUMABLE 的等待时间 (以秒计) (默认 7200)
- date_cache -- 日期转换高速缓存的大小 (以条目计) (默认 1000)
- no_index_errors -- 出现任何索引错误时中止加载 (默认 FALSE)
- PLEASE NOTE: 命令行参数可以由位置或关键字指定
- 。前者的例子是 'sqlldr
- scott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo
- userid=scott/tiger'。位置指定参数的时间必须早于
- 但不可迟于由关键字指定的参数。例如,
- 允许 'sqlldr scott/tiger control=foo logfile=log', 但是
- 不允许 'sqlldr scott/tiger control=foo log', 即使
- 参数 'log' 的位置正确。
rows —— 加载的行数,默认值:常规路劲:64,直接路径:全部。常规路径导入时,指定绑定数组中的行数。直接路径导入时,指一次从数据文件只读取的行数。该参数同时受bindsize制约,如果rows每行实际占用大小超出bindsize的最大可用值,则rows自动降低到bindsize最大可用值。
bindsize —— 默认值:256000,以字节为单位。为绑定数组指定最大可用空间,用来存储一次读取的rows的记录,该值不能太小,至少要能放入一条逻辑记录,不然有可能造成加载失败,但是设置一个超大值也没有意义,浪费空间而且起不到任何效果。
direct ——直接路径。默认值是false。直接路径加载并不是通过insert语句的方式提交数据,直接路径的加载甚至不走db_buffer,因为不会存在缓冲区争用,直接路径加载直接向数据文件写数据,因此效率较高。
parallel —— 并行度。默认值false。仅仅在直接路径加载时有效。
file —— 指定写入的表空间数据文件。通常是并行加载时应该会用到该参数,指定file文件后,要加载的内容即只向指定的数据文件写入数据,通过这种方式某些情况下可以减小磁盘间的竞争。
skip_unusable_indexes —— 是否跳过不可用的索引。默认值:默认为false。如果设置为true。在加载数据的时候,如果没加载的表中索引为unusable,则该索引数据不维护,数据加载完以后也不会改变索引的状态。在oracle中也有一个参数叫skip_unusable_indexes,但是sqlldr中的这个参数优先级比oracle数据库内部的这个参数优先级高。如果sqlldr中未设置这个参数,则以oracle中的skip_unusable_indexes参数为准。
skip_index_maintenance —— 是否跳过索引维护。默认值为false。该参数仅在直接路径加载时有效,如果设置为true,则数据加载过程中并不会维护索引,因此数据加载完后会导致索引无效。
readsize —— 缓冲区大小。默认值1048576字节。最大不超过20M。该参数设置的值仅当从数据文件中读取数据时有效,如果数据包含在控制文件中则为64K,并不可修改。
streamsize —— 流缓冲区大小。默认值是256000字节。指定直接路径加载时流缓冲区大小。
multithreading —— 是否启用多线程。默认值:多cpu系统默认为true,单cpu的系统默认则为false。该参数仅仅在直接路径加载时有效。date_cac存。默认值为1000条。指定一个专门用于日志转换的缓存区,如果处理的日期在其中,则不需要转换,直接取出来使用。
首先数据的格式要整理好。编辑一个ctl文件
- OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的 选项可以写到这里边来,skip=1 用来跳过数据中的第一行
- LOAD DATA
- INFILE "users_data.csv" --指定外部数据文件,可以写多 个 INFILE "another_data_file.csv" 指定多个数据文件
- --这里还可以使 用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件,
- truncate --操作类型,用 truncate table 来清除表中原有 记录
- INTO TABLE users -- 要插入记录的表
- Fields terminated by "," -- 数据中每行记录用 "," 分隔
- Optionally enclosed by '"' -- 数据中每个字段用 '"' 框起,比如字段中有 "," 分隔符时
- trailing nullcols --表的字段没有对应的值时允 许为空
- (
- virtual_column FILLER, --这是一个虚拟字段,用来跳 过由 PL/SQL Developer 生成的第一列序号
- user_id number, --字段可以指定类型,否则认 为是 CHARACTER 类型, log 文件中有显示
- user_name,
- login_times,
- last_login DATE "YYYY-MM-DD HH24:MI:SS" -- 指定接受日期的格式,相当用 to_date() 函数转换
- )
1) insert --为缺省方式,在数据装载开始时要求表为空
2) append --在表中追加新记录
3) replace --删除旧记录(用 delete from table 语句),替换成新装载的记录
4) truncate --删除旧记录(用 truncate table 语句),替换成新装载的记录
执行的命令:
sqlldr user/pass control='/home/oracle/control.ctl'
sqlldr user/pass control='/home/oracle/control.ctl' rows=5000 readsize=10340000 bindsize=10340000
最后说下有关 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 就算不错了,开始可用结构 相同的文件,但只有少量数据,成功后开始加载大量数据,这样可以避免时间的浪费。
(注意:一般只能用ASCII码形式,切记要转换编码,不然导入数据为空)
参考:1. Oracle SQL*Loader -- 英文,Sql Loader 的官方使用说明,包含多种类型的 Demo
2. sql loader的用法 -- 列出了 sql loader 的选择参数的中文说明
3. 使用SQL Loader导入大量数据,避免使用SQL频繁写库 -- 一个简单的例子,快带了解 Sql Loader 的用法
4. Oracle SQL Loader的详细语法
5. oracle sql loader全攻略 -- 还算名符其实。并讲了如何用 SQL *Plus 的 spool 或 UTL_FILE 包生成数据文件
6. SQL*Loader Control File Reference -- 英文,控制文件使用参考
7. 学习oracle sql loader 的使用
8. 用 sqlloader(sqlldr)装载LOB数据 -- LOB 的内类是一个外部文件,用 sql loader 导入到数据库
9. SQLLDR直 接加载几个参数的测试
10.Maximizing SQL*Loader Performance
更多推荐
所有评论(0)