sql loader(sqlldr)的用法

ゝ一纸荒年。 2022-08-11 09:58 409阅读 0赞

sql loader可以把一些以文本格式存放的数据顺利的导入到oracle数据库中，  
是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。                                                
  
sql loader可以把一些以文本格式存放的数据顺利的导入到oracle数据库中，  
是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。  
缺点就速度比较慢，另外对blob等类型的数据就有点麻烦了。  
用法: SQLLDR keyword=value \[,keyword=value,...\]  
   
有效的关键字:  
    userid -- ORACLE username/password  
   control – 控制文件  
       log – 记录的日志文件  
       bad – 坏数据文件  
      data – 数据文件  
   discard – 丢弃的数据文件  
discardmax – 允许丢弃数据的最大值        (全部默认)  
      skip -- Number of logical records to skip  (默认0)  
      load -- Number of logical records to load  (全部默认)  
    errors – 允许的错误记录数          (默认50)  
      rows -- Number of rows in conventional path bind array or between direct path data saves  
                （每次提交的记录数，默认: 常规路径 64, 所有直接路径）  
  bindsize -- Size of conventional path bind array in bytes(默认256000)  
                每次提交记录的缓冲区的大小(字节为单位，默认256000)  
    silent --禁止输出信息 (header,feedback,errors,discards,partitions)  
    direct – 使用直通路径方式导入                    (默认FALSE)  
   parfile -- parameter file: name of file that contains parameter specifications  
  parallel -- 并行导入                   (默认FALSE)  
       file -- File to allocate extents from  
  与bindsize成对使用，其中较小者会自动调整到较大者  
sqlldr先计算单条记录长度，乘以rows，如小于bindsize，不会试图扩张rows以填充bindsize；如超出，则以bindsize为准。  
   
external\_table  
           -- use external table for load; NOT\_USED, GENERATE\_ONLY, EXECUTE(默认NOT\_USED)  
columnarrayrows  
           -- Number of rows for direct path column array(默认5000)  
streamsize -- Size of direct path stream buffer in bytes(默认256000)  
multithreading  
           -- use multithreading in direct path  
 resumable -- enable or disable resumable for current session(默认FALSE)  
resumable\_name  
           -- text string to help identify resumable statement  
resumable\_timeout  
           -- wait time (in seconds) for RESUMABLE(默认7200)  
date\_cache -- size (in entries) of date conversion cache(默认1000)  
   
注意：有两种方式可以指定命令行参数：通过位置或者通过关键字。前者的例子：'sqlldr scott/tiger foo'；  
后者的例子：'sqlldr control=foo userid=scott/tiger'；  
不能前面使用关键字指定后面通过位置制定的混合方式；  
比如：'sqlldr scott/tiger control=foo logfile=log' 是允许的，  
但'sqlldr scott/tiger control=foo log'不允许。  
为清楚起见最好所有命令行参数都用关键字指定。  
控制文件：  
一个控制命令的脚本文件，通常以ctl结尾，内容如下：  
LOAD DATA  
INFILE 't.dat'              要导入的文件  
// INFILE 'tt.date' 导入多个文件  
// INFILE \*               表示要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容  
   
INTO TABLE table\_name    指定装入的表  
BADFILE 'c:\\bad.txt'    可选，指定坏文件地址,缺省在当前目录下生成与原文件名一致的.bad文件  
   
\*\*\*\*\*\*\*\*\*\*\*\*\* 以下是4种装入表的方式  
APPEND             原先的表有数据 就加在后面  
INSERT             装载空表 如果原先的表有数据 sqlloader会停止 默认值  
REPLACE           原先的表有数据 原先的数据会全部删除  
TRUNCATE        指定的内容和replace的相同 会用truncate语句删除现存数据  
   
\*\*\*\*\*\*\*\*\*\*\*\*\* 指定分隔符  
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  
// TERMINATED BY WRITESPACE 以空白分割  
   
TRAILING NULLCOLS            表的字段没有对应的值时允许为空  
   
\*\*\*\*\*\*\*\*\*\*\*\*\* 下面是表的字段  
(  
col\_1 , col\_2 ,col\_filler FILLER // FILLER 关键字 此列的数值不会被装载  
// 如: lg,lg,not 结果 lg lg  
)  
如果没声明FIELDS TERMINATED BY ',' 时，可以用下面两种方式实现同样功能：  
1．为每一列指定分隔符  
 (  
 col\_1 \[interger external\] TERMINATED BY ',' ,  
 col\_2 \[date "dd-mon-yyy"\] TERMINATED BY ',' ,  
 col\_3 \[char\] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'  
 )  
2．用位置告诉字段装载数据  
 (  
col\_1 position(1:2),  
 col\_2 position(3:10),  
 col\_3 position(\*:16), // 这个字段的开始位置在前一字段的结束位置  
 col\_4 position(1:16),  
 col\_5 position(3:10) char(8) // 指定字段的类型  
 )  
   
BEGINDATA         对应开始的 INFILE \* 要导入的内容就在control文件里  
10,Sql,what  
20,lg,show

一个简单的例子：

目前所做的项目需要从一个按行记录的数据文件取出部分字段，再将这些字段的按行存储到Oracle数据库。这些数据大约有2 700 000条，原始数据文件有450M左右。至少有两种方法可以实现：

一.将原始数据文件读进内存流中，每读一行解析一条数据，接着执行一条SQL语句，将解析到的数据插入数据库表；

二..将原始数据文件读进内存流中，每读一行解析一条数据，接着将解析到的数据按行写入一个临时文件data.tmp，待所有数据均被解析完毕且写入临时文件后，调用SQL Loader，将所有数据一次性从临时文件导入数据库表。

一开始是按照第一种方法编程的。在本机（Oracle服务器装在本机）测试顺利通过，但是花了大约150分钟才导入解析和完成；当部署到实际环境中运行时，却出现了问题。跟踪发现是因为网络不稳定（程序所在服务器与数据库服务器处于不同的局域网，由防火墙隔开，做了几个特定端口的路由），有时候连接不到数据库，往往只导入了很少的几千条甚至几百条数据时，数据库就连接不上了。

后来分析，对于大量数据的导入，采用第一种方法会导致频繁写库，每次都要打开数据库（禁用了连接池，后续文章会提到禁用的原因），对数据库造成较大的压力，加上网络环境本来就不够稳定，所以很容易出现问题。于是改用第二种方法。

使用第二种方法后，解析花的时间大约为90分钟，导入则只花了5分钟左右。看来SQL Loader的效率真的很高。

下面讲讲SQL Loader的基本用法。  
       
     1、在Oracle中按照导入数据的格式建立一个空表

2、编写一个控制文件control.ctl，内容如下

![None.gif][]LOAD DATA   
![None.gif][]INFILE 'E:\\test\\data.tmp'   
![None.gif][]BADFILE 'E:\\test\\data.bad'  
![None.gif][]DISCARDFILE 'E:\\test\\data.dsc'  
![None.gif][]DISCARDMAX 1000  
![None.gif][]  
![None.gif][]APPEND  
![None.gif][]INTO TABLE "TB\_TEST"  
![None.gif][]FIELDS TERMINATED BY '|'  
![None.gif][]TRAILING NULLCOLS  
![None.gif][]  (  
![None.gif][]   USER\_ID,   
![None.gif][]   USER\_NAME,   
![None.gif][]   REG\_TIME  DATE(20) "YYYY-MM-DD HH24:MI:SS"  
![None.gif][]   )

操作分类：

a、insert，为缺省方式，在数据装载开始时要求表为空  
        b、append，在表中追加新记录  
        c、replace，删除旧记录，替换成新装载的记录  
        d、truncate，同上

3、在命令行中执行以下语句：

sqlldr userid/password@database e:\\test\\control.ctl

参考：1. [Oracle SQL\*Loader][Oracle SQL_Loader]  -- 英文，Sql Loader 的官方使用说明，包含多种类型的  Demo  
        2. [sql loader的用法][sql loader]  -- 列出了 sql loader 的选择参数的中文说明  
        3. [使用SQL Loader导入大量数据，避免使用SQL频繁写库][SQL Loader_SQL] -- 一个简单的例子，快带了解 Sql Loader 的用法  
        4. [Oracle SQL Loader的详细语法][Oracle SQL Loader]  
        5. [oracle sql loader全攻略][oracle sql loader] -- 还算名符其实。并讲了如何用 SQL \*Plus 的  spool 或 UTL\_FILE 包生成数据文件  
        6. [SQL\*Loader Control File Reference][SQL_Loader Control File Reference]   -- 英文，控制文件使用参考  
        7. [学习oracle sql loader 的使用][oracle sql loader 1]   
        8. [用sqlloader(sqlldr)装载LOB数据][sqlloader_sqlldr_LOB]  -- LOB 的内类是一个外部文件，用 sql loader 导入到数据库  
        9. [SQLLDR直接加载几个参数的测试][SQLLDR]  
        10.[Maximizing SQL\*Loader Performance][Maximizing SQL_Loader Performance]

[None.gif]: /images/20220810/2c63ce7837ec48179ca194c6356deb7b.png
[Oracle SQL_Loader]: http://www.psoug.org/reference/sqlloader.html
[sql loader]: http://www.myfaq.com.cn/2005September/2005-09-13/206612.html
[SQL Loader_SQL]: http://www.cnblogs.com/chinaray/archive/2008/04/18/1160466.html
[Oracle SQL Loader]: http://www.yuanma.org/data/2006/0922/article_1573.htm
[oracle sql loader]: http://soft.ccw.com.cn/programing/database/htm2008/20080925_509432.shtml
[SQL_Loader Control File Reference]: http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch05.htm
[oracle sql loader 1]: http://blog.csdn.net/s98/archive/2005/11/08/525387.aspx
[sqlloader_sqlldr_LOB]: http://www.knowsky.com/388788.html
[SQLLDR]: http://jolly10.itpub.net/post/7268/466996
[Maximizing SQL_Loader Performance]: http://www.remote-dba.net/teas_rem_util18.htm