sqlldr的使用

收到一个任务，需要往oracle库里导入很多excel文件，而且excel文件很大，每个都在500M左右，最后考虑采用sqlldr。先来了解下sqlldr

1. [oracle@linux ~]$ sqlldr  
2.   
3. SQL*Loader: Release 11.2.0.1.0 - Production on 星期四 3月 1 13:15:47 2012  
4.   
5. Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.  
6.   
7.   
8. 用法: SQLLDR keyword=value [,keyword=value,...]  
9.   
10. 有效的关键字:   
11.   
12.     userid -- ORACLE 用户名/口令          
13.    control -- 控制文件名                  
14.        log -- 日志文件名                      
15.        bad -- 错误文件名                     
16.       data -- 数据文件名                    
17.    discard -- 废弃文件名  
18. discardmax -- 允许废弃的文件的数目         (全部默认)  
19.       skip -- 要跳过的逻辑记录的数目  (默认 0)  
20.       load -- 要加载的逻辑记录的数目  (全部默认)  
21.     errors -- 允许的错误的数目         (默认 50)  
22.       rows -- 常规路径绑定数组中或直接路径保存数据间的行数  
23.                (默认: 常规路径 64, 所有直接路径)  
24.   bindsize -- 常规路径绑定数组的大小 (以字节计)  (默认 256000)  
25.     silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)  
26.     direct -- 使用直接路径                     (默认 FALSE)  
27.    parfile -- 参数文件: 包含参数说明的文件的名称  
28.   parallel -- 执行并行加载                    (默认 FALSE)  
29.       file -- 要从以下对象中分配区的文件       
30. skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区  (默认 FALSE)  
31. skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用  (默认 FALSE)  
32. commit_discontinued -- 提交加载中断时已加载的行  (默认 FALSE)  
33.   readsize -- 读取缓冲区的大小               (默认 1048576)  
34. external_table -- 使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE  (默认 NOT_USED)  
35. columnarrayrows -- 直接路径列数组的行数  (默认 5000)  
36. streamsize -- 直接路径流缓冲区的大小 (以字节计)  (默认 256000)  
37. multithreading -- 在直接路径中使用多线程  
38.  resumable -- 启用或禁用当前的可恢复会话  (默认 FALSE)  
39. resumable_name -- 有助于标识可恢复语句的文本字符串  
40. resumable_timeout -- RESUMABLE 的等待时间 (以秒计)  (默认 7200)  
41. date_cache -- 日期转换高速缓存的大小 (以条目计)  (默认 1000)  
42. no_index_errors -- 出现任何索引错误时中止加载  (默认 FALSE)  
43.   
44. PLEASE NOTE: 命令行参数可以由位置或关键字指定  
45. 。前者的例子是 'sqlldr  
46. scott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo  
47. userid=scott/tiger'。位置指定参数的时间必须早于  
48. 但不可迟于由关键字指定的参数。例如,  
49. 允许 'sqlldr scott/tiger control=foo logfile=log', 但是  
50. 不允许 'sqlldr scott/tiger control=foo log', 即使  
51. 参数 '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文件

1. OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的 选项可以写到这里边来,skip=1 用来跳过数据中的第一行  
2. LOAD DATA  
3. INFILE "users_data.csv" --指定外部数据文件，可以写多 个 INFILE "another_data_file.csv" 指定多个数据文件  
4. --这里还可以使 用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件，  
5. truncate --操作类型，用 truncate table 来清除表中原有 记录  
6. INTO TABLE users -- 要插入记录的表  
7. Fields terminated by "," -- 数据中每行记录用 "," 分隔  
8. Optionally enclosed by '"' -- 数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时  
9. trailing nullcols --表的字段没有对应的值时允 许为空  
10. (  
11.   virtual_column FILLER, --这是一个虚拟字段，用来跳 过由 PL/SQL Developer 生成的第一列序号  
12.   user_id number, --字段可以指定类型，否则认 为是 CHARACTER 类型, log 文件中有显示  
13.   user_name,  
14.   login_times,  
15.   last_login DATE "YYYY-MM-DD HH24:MI:SS" -- 指定接受日期的格式，相当用 to_date() 函数转换  
16. )  

说 明：在操作类型 truncate 位置可用以下中的一值：

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