收到一个任务,需要往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




Logo

更多推荐