Oracle数据库导入csv文件（SQLLDR命令行）[大文件速度快]

淩亂°似流年 2024-03-22 17:33 64阅读 0赞

#### 文章目录 ####

*  前言
 *   *  1.说明
     *  2.sqlldr用法
     *  3.获取csv文件
     *  4.创建USER\_INFO表
     *  5.创建ctl文件
     *  6.使用sqlldr导入
     *  7.direct直接路径方式
     *  8.创建ctl文件(直接路径方式)
     *  9.删除主键
     *  10.使用sqlldr导入(直接路径方式)
     *  11.重建主键
     *  12.parallel并行导入
     *  13.创建ctl文件(并行方式)
     *  14.使用sqlldr导入(并行方式)
     *  15.sqlldr命令说明
     *   *  15.1.用法
         *  15.2.参数说明
         *  15.3.注意事项
         *  15.4.重要参数
     *  16.执行结果详细日志
 *  总结

## 前言 ##

**本篇讲述SQLLDR的最基本使用。**

### 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表示导入方式：**

> insert :默认方式，在导入记录前要求表为空；  
> append :在表中追加新导入的记录；  
> replace :删除旧记录(等价delete from table语句)，替换成新导入的记录；  
> 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
    characterset utf8
    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

**或:**

sqlldr userid=dct/123456 control=loadData_hky.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 RESUMABLE</td> 
   <td>的等待时间 (以秒计) (默认 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

## 总结 ##

> 如果此篇文章有帮助到您, 希望打大佬们能`关注`、`点赞`、`收藏`、`评论`支持一波，非常感谢大家！  
> 如果有不对的地方请指正!!!

[参考1][1]

[1]: https://blog.csdn.net/londa/article/details/108051914