Oracle数据库导入csv文件（SQLLDR命令行）

我就是我 2022-11-28 10:41 353阅读 0赞

# 1.说明 #

Oracle数据库导入csv文件，  
当csv文件较小时，  
可以使用数据库管理工具，  
比如DBevaer导入到数据库，  
当csv文件很大时，  
可以使用Oracle提供的sqlldr命令行工具，  
能够快速导入大量数据。  
在数据库之间进行数据迁移时，  
特别是不同类型的数据库，  
使用csv文件是一种很好的选择。

# 2.sqlldr用法 #

下面介绍sqlldr用法，  
有一个USER\_INFO.csv文件，  
需要导入到数据库中USER\_INFO表，  
而且有1.5亿条数据，  
要求导入耗时尽量小。  
这里先介绍基本的导入方式，  
然后介绍direct导入方式，  
最后介绍parallel导入方式。

# 3.获取csv文件 #

USER\_INFO.csv文件内容如下，  
这里仅选取了三条测试数据:

MSISDN,PROVINCE_CODE,CREATE_TIME
    8617512570551,30,2019-10-31 17:16:09
    8613063310680,33,2019-10-31 17:16:12
    8613295281345,80,2019-10-31 17:16:14

# 4.创建USER\_INFO表 #

CREATE TABLE "USER_INFO" ( 
    "MSISDN" VARCHAR2(16),
    "PROVINCE_CODE" VARCHAR2(4),
    "CREATE_TIME"   DATE,
    CONSTRAINT "PK_USER_INFO_MSISDN" PRIMARY KEY ("MSISDN") 
    ) TABLESPACE "USERS";
     
    COMMENT ON COLUMN "USER_INFO"."MSISDN" IS '用户手机号';
    COMMENT ON COLUMN "USER_INFO"."PROVINCE_CODE" IS '省份编码'; 
    COMMENT ON COLUMN "USER_INFO"."CREATE_TIME" IS '用户创建时间';

注意表中字段要和csv文件对应。

# 5.创建ctl文件 #

创建名为loaddata\_USER\_INFO.ctl的控制文件：

options(skip=1,BINDSIZE=20971520, ROWS=10000, READSIZE=20971520, ERRORS=999999999)
    load data
    infile '/home/oracle/USER_INFO.csv'
    insert into table "USER_INFO"
    fields terminated by ','  
    Optionally enclosed by '\''  
    (MSISDN,PROVINCE_CODE,CREATE_TIME "to_date(:CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss')")

第1行是导入参数配置，  
已经是支持大量数据导入的参数方案。

第3行infile指定导入的文件是USER\_INFO.csv；

第4行into table前面的insert表示导入方式：

1.  insert :默认方式，在导入记录前要求表为空；
2.  append :在表中追加新导入的记录；
3.  replace :删除旧记录(等价delete from table语句)，替换成新导入的记录；
4.  truncate:删除旧记录(等价truncate table语句)，替换成新导入的记录；  
    into table后面指定导入数据库表USER\_INFO，  
    且表名必须大写；

第5行指定每一行的字段是以逗号(,)分隔；

第6行指定字段是用两个分号(')包围起来的，可选的；

最后一行对应导入的字段，  
注意如果导入的是时间字段，  
需要指明时间转换的格式。

# 6.使用sqlldr导入 #

指定需要导入的数据库，  
以及使用的control文件：

sqlldr userid=yuwen/ai123456@10.21.13.14:1521/orcl control=loaddata_USER_INFO.ctl

导入成功输出日志如下：

SQL*Loader: Release 12.1.0.2.0 - Production on Thu Oct 31 17:37:49 2019
     
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
     
    Path used:      Conventional
    Commit point reached - logical record count 3
     
    Table "USER_INFO":
      3 Rows successfully loaded.
     
    Check the log file:
      loaddata_USER_INFO.log
    for more information about the load.

看到3条数据成功导入USER\_INFO，  
同时会生成loaddata\_USER\_INFO.log日志，  
通过该日志可以查看更详细的导入信息，  
如果导入时发生错误，  
可以查看到详细的错误信息，  
而且还会生成USER\_INFO.bad文件，  
bad文件原样输出了导入失败的数据。  
默认情况下ERRORS达到50个就会停止导入，  
如果需要忽略错误继续导入，  
可以配置ERRORS最大值为999999999。

# 7.direct直接路径方式 #

sqlldr支持direct参数，  
使用直接路径方式导效率非常高,  
direct参数默认为FALSE，  
上面第6步使用的就是默认常规路径方式，  
下面使用直接路径方式，  
进一步加快数据导入速度。  
但是使用直接路径方式会导致表的唯一索引(或主键)失效，  
可以在导入前先删除表的唯一索引，  
然后在导入完成后重建索引即可，  
如果有重复的数据，  
需要先去重才能重建索引成功，  
经过测试即使需要重建索引，  
使用直接路径方式还是比常规路径方式快。

# 8.创建ctl文件(直接路径方式) #

创建名为loaddata\_USER\_INFO\_direct.ctl的控制文件：

options(skip=1,COLUMNARRAYROWS=20971520, ROWS=10000, READSIZE=20971520, ERRORS=999999999)
    load data
    infile '/home/oracle/USER_INFO.csv'
    insert into table "USER_INFO"
    fields terminated by ','  
    Optionally enclosed by '\''  
    (MSISDN,PROVINCE_CODE,CREATE_TIME "to_date(:CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss')")

这个文件和第5步中的区别在于，  
options中的BINDSIZE换成了COLUMNARRAYROWS，  
因为BINDSIZE是常规路径绑定数组的大小，  
而COLUMNARRAYROWS是直接路径列数组的行数。

# 9.删除主键 #

导入前需要先删除USER\_INFO表的主键：

ALTER TABLE USER_INFO DROP CONSTRAINT PK_USER_INFO_MSISDN;

# 10.使用sqlldr导入(直接路径方式) #

基本与第6步的命令相同，增加direct=true：

sqlldr userid=yuwen/ai123456@10.21.13.14:1521/orcl control=loaddata_USER_INFO_direct.ctl direct=true

数据成功导入日志如下：

SQL*Loader: Release 12.1.0.2.0 - Production on Fri Nov 1 10:48:10 2019
     
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
     
    Path used:      Direct
     
    Load completed - logical record count 3.
     
    Table "USER_INFO":
      3 Rows successfully loaded.
     
    Check the log file:
      loaddata_USER_INFO_direct.log
    for more information about the load.

# 11.重建主键 #

ALTER TABLE USER_INFO ADD CONSTRAINT PK_USER_INFO_MSISDN PRIMARY KEY(MSISDN);

至此使用直接路径方式导入数据成功，  
经过测试1.5亿条不重复数据direct方式导入，  
花费4分02秒，  
然后重建主键花费19分14秒，  
总耗时23分16秒。  
然而使用常规路径方式导入需要48个小时，  
导入效率得到了极大的提升。

# 12.parallel并行导入 #

前面使用的是串行方式导入数据，  
通过设置parallel为true可以并行导入，  
在多核服务器上面能进一步提升导入效率。  
需要注意并行方式仅仅在直接路径方式时有效。

# 13.创建ctl文件(并行方式) #

创建名为loaddata\_USER\_INFO\_direct\_parallel.ctl的控制文件：

options(skip=1,COLUMNARRAYROWS=20971520, ROWS=10000, READSIZE=20971520, ERRORS=999999999)
    load data
    infile '/home/oracle/USER_INFO.csv'
    append into table "USER_INFO"
    fields terminated by ','  
    Optionally enclosed by '\''  
    (MSISDN,PROVINCE_CODE,CREATE_TIME "to_date(:CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss')")

这个文件和第8步中的区别在于，  
并行方式只支持append导入，  
而且要注意USER\_INFO表不能有索引，  
如果有需要先删除。

# 14.使用sqlldr导入(并行方式) #

基本与第10步的命令相同，增加parallel=true：

sqlldr userid=yuwen/ai123456@10.21.13.14:1521/orcl control=loaddata_USER_INFO_direct_parallel.ctl direct=true parallel=true

数据成功导入日志如下：

SQL*Loader: Release 12.1.0.2.0 - Production on Fri Nov 1 11:08:51 2019
     
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
     
    Path used:      Direct
    SQL*Loader-281: Warning: ROWS parameter ignored in parallel mode.
     
    Load completed - logical record count 3.
     
    Table "USER_INFO":
      3 Rows successfully loaded.
     
    Check the log file:
      loaddata_USER_INFO_direct_parallel.log
    for more information about the load.

# 15.sqlldr命令说明 #

## 15.1.用法 ##

SQLLDR keyword=value [,keyword=value,...]

## 15.2.参数说明 ##

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>userid</td> 
   <td>ORACLE 用户名/口令</td> 
  </tr> 
  <tr> 
   <td>control</td> 
   <td>控制文件名</td> 
  </tr> 
  <tr> 
   <td>log</td> 
   <td>日志文件名</td> 
  </tr> 
  <tr> 
   <td>bad</td> 
   <td>错误文件名</td> 
  </tr> 
  <tr> 
   <td>data</td> 
   <td>数据文件名</td> 
  </tr> 
  <tr> 
   <td>discard</td> 
   <td>废弃文件名</td> 
  </tr> 
  <tr> 
   <td>discardmax</td> 
   <td>允许废弃的文件的数目 (全部默认)</td> 
  </tr> 
  <tr> 
   <td>skip</td> 
   <td>要跳过的逻辑记录的数目 (默认 0)</td> 
  </tr> 
  <tr> 
   <td>load</td> 
   <td>要加载的逻辑记录的数目 (全部默认)</td> 
  </tr> 
  <tr> 
   <td>errors</td> 
   <td>允许的错误的数目 (默认 50)</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>常规路径绑定数组中或直接路径保存数据间的行数,默认: 常规路径 64, 所有直接路径)</td> 
  </tr> 
  <tr> 
   <td>bindsize</td> 
   <td>常规路径绑定数组的大小 (以字节计) (默认 256000)</td> 
  </tr> 
  <tr> 
   <td>silent</td> 
   <td>运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)</td> 
  </tr> 
  <tr> 
   <td>direct</td> 
   <td>使用直接路径 (默认 FALSE)</td> 
  </tr> 
  <tr> 
   <td>parfile</td> 
   <td>参数文件: 包含参数说明的文件的名称</td> 
  </tr> 
  <tr> 
   <td>parallel</td> 
   <td>执行并行加载 (默认 FALSE)</td> 
  </tr> 
  <tr> 
   <td>file</td> 
   <td>要从以下对象中分配区的文件</td> 
  </tr> 
  <tr> 
   <td>skip_unusable_indexes</td> 
   <td>不允许/允许使用无用的索引或索引分区 (默认 FALSE)</td> 
  </tr> 
  <tr> 
   <td>skip_index_maintenance</td> 
   <td>没有维护索引, 将受到影响的索引标记为无用 (默认 FALSE)</td> 
  </tr> 
  <tr> 
   <td>commit_discontinued</td> 
   <td>提交加载中断时已加载的行 (默认 FALSE)</td> 
  </tr> 
  <tr> 
   <td>readsize</td> 
   <td>读取缓冲区的大小 (默认 1048576)</td> 
  </tr> 
  <tr> 
   <td>external_table</td> 
   <td>使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE (默认 NOT_USED)</td> 
  </tr> 
  <tr> 
   <td>columnarrayrows</td> 
   <td>直接路径列数组的行数 (默认 5000)</td> 
  </tr> 
  <tr> 
   <td>streamsize</td> 
   <td>直接路径流缓冲区的大小 (以字节计) (默认 256000)</td> 
  </tr> 
  <tr> 
   <td>multithreading</td> 
   <td>在直接路径中使用多线程</td> 
  </tr> 
  <tr> 
   <td>resumable</td> 
   <td>启用或禁用当前的可恢复会话 (默认 FALSE)</td> 
  </tr> 
  <tr> 
   <td>resumable_name</td> 
   <td>有助于标识可恢复语句的文本字符串</td> 
  </tr> 
  <tr> 
   <td>resumable_timeout</td> 
   <td>RESUMABLE 的等待时间 (以秒计) (默认 7200)</td> 
  </tr> 
  <tr> 
   <td>date_cache</td> 
   <td>日期转换高速缓存的大小 (以条目计) (默认 1000)</td> 
  </tr> 
  <tr> 
   <td>no_index_errors</td> 
   <td>出现任何索引错误时中止加载 (默认 FALSE)</td> 
  </tr> 
 </tbody> 
</table>

## 15.3.注意事项 ##

命令行参数可以由位置或关键字指定。  
前者的例子是 'sqlldr scott/tiger foo';  
后这的例子是 'sqlldr control=foo userid=scott/tiger'。  
位置指定参数的时间必须早于，  
但不可迟于由关键字指定的参数。  
例如允许 'sqlldr scott/tiger control=foo logfile=log',  
但是不允许 'sqlldr scott/tiger control=foo log',  
即使参数 'log' 的位置正确。

## 15.4.重要参数 ##

rows：  
加载的行数，默认值：常规路径：64，直接路径：全部。  
常规路径导入时，指定绑定数组中的行数。直  
接路径导入时，指一次从数据文件只读取的行数。  
该参数同时受bindsize制约，  
如果rows每行实际占用大小超出bindsize的最大可用值，  
则rows自动降低到bindsize最大可用值。

bindsize：  
默认值：256000，以字节为单位。  
为绑定数组指定最大可用空间，  
用来存储一次读取的rows的记录，  
该值不能太小，至少要能放入一条逻辑记录，  
不然有可能造成加载失败，  
但是设置一个超大值也没有意义，  
浪费空间而且起不到任何效果。

direct：  
直接路径，默认值是false。  
直接路径加载并不是通过insert语句的方式提交数据，  
直接路径的加载甚至不走db\_buffer，  
因为不会存在缓冲区争用，  
直接路径加载直接向数据文件写数据，  
因此效率较高。

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\_cache：  
日期转换缓存，默认值为1000条。  
指定一个专门用于日志转换的缓存区，  
如果处理的日期在其中，则不需要转换，  
否则直接取出来使用。

# 16.执行结果详细日志 #

给出loaddata\_USER\_INFO\_direct\_parallel.log日志内容，  
可以看到导入使用的配置，耗时等详细信息，  
供给大家参考：

SQL*Loader: Release 12.1.0.2.0 - Production on Fri Nov 1 11:20:07 2019
     
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
     
    Control File:   loaddata_USER_INFO_direct_parallel.ctl
    Data File:      /home/oracle/USER_INFO.csv
      Bad File:     USER_INFO.bad
      Discard File:  none specified
     
     (Allow all discards)
     
    Number to load: ALL
    Number to skip: 1
    Errors allowed: 999999999
    Continuation:    none specified
    Path used:      Direct - with parallel option.
     
    Table "USER_INFO", loaded from every logical record.
    Insert option in effect for this table: APPEND
     
       Column Name                  Position   Len  Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    MSISDN                              FIRST     *   ,  O(') CHARACTER            
    PROVINCE_CODE                        NEXT     *   ,  O(') CHARACTER            
    CREATE_TIME                          NEXT     *   ,  O(') CHARACTER            
        SQL string for column : "to_date(:CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss')"
     
    SQL*Loader-281: Warning: ROWS parameter ignored in parallel mode.
     
     
    Table "USER_INFO":
      3 Rows successfully loaded.
      0 Rows not loaded due to data errors.
      0 Rows not loaded because all WHEN clauses were failed.
      0 Rows not loaded because all fields were null.
     
    Bind array size not used in direct path.
    Column array  rows :   10000
    Stream buffer bytes:  256000
    Read   buffer bytes:20971520
     
    Total logical records skipped:          1
    Total logical records read:             3
    Total logical records rejected:         0
    Total logical records discarded:        0
    Total stream buffers loaded by SQL*Loader main thread:        1
    Total stream buffers loaded by SQL*Loader load thread:        0
     
    Run began on Fri Nov 01 11:20:07 2019
    Run ended on Fri Nov 01 11:20:13 2019
     
    Elapsed time was:     00:00:05.60
    CPU time was:         00:00:00.44

# 17.参考文章 #

[将CSV文件数据导入Oracle数据库][CSV_Oracle]  
[（sqlldr）将xx.csv文件导入oracle数据库][sqlldr_xx.csv_oracle]  
[sqlldr总结参数介绍][sqlldr]  
[Loading data in direct mode and unique index ORA-26026][]

[CSV_Oracle]: https://links.jianshu.com/go?to=https%3A%2F%2Fblog.csdn.net%2Fliyang3462%2Farticle%2Fdetails%2F80104522
[sqlldr_xx.csv_oracle]: https://links.jianshu.com/go?to=https%3A%2F%2Fblog.csdn.net%2Fchmldch%2Farticle%2Fdetails%2F52800058
[sqlldr]: https://links.jianshu.com/go?to=https%3A%2F%2Fwww.iteye.com%2Fblog%2Fdbua-1570018
[Loading data in direct mode and unique index ORA-26026]: https://links.jianshu.com/go?to=http%3A%2F%2Fdbaora.com%2Floading-data-in-direct-mode-and-unique-index-ora-26026%2F