Oracle SQL*Loader 应用

来了一大批单个大小动辄7G的文本文件，导入数据表中，怎么办!!!!尝试用PL/SQL Developer中的文本导入器，结果====卡死=====很久之后====卡死

背景

Oracle常用的表数据迁移方法有以下几种：

  1. A表导出包含 insert 的sql，逐条插入B表中

  2. 建立数据库间的 dblink，然后用 create table B as select * from A@dblink where，或 insert into B select * from A@dblink where

  3. expdp A 表，再 impdp 到 B 表

  4. 文本数据采用SQL*Loader (sqlldr)

    第1、2种方式可用以下方式优化导入效率：

    alter table B nologging;  
insert /* +APPEND */ into B(col1,col2) values(val1,val2);  
insert /* +APPEND */ into B select * from A@dblink where  ;

    基本用法

  5. SQL*Loader 使用过程中需要创建一个控制文件.ctl，示例如下：

OPTIONS (rows=100000)  /*定义sqlldr部分参数*/
load data
infile'F:\2014-10-31.txt'	/*指定数据文件*/
insert
/*
在操作类型 insert 位置可选一下值：
1) insert   --为缺省方式，在数据装载开始时要求表为空
2) append   --在表中追加新记录
3) replace  --删除旧记录(用 delete from table 语句)，替换成新装载的记录
4) truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录
*/
into table user_table
fields terminated by ','	/*指定字段分隔符*/
Optionally enclosed by '"' 	/*可选。 数据中每个字段用 '"' 框起，如果字段中有 "," 分隔符，可避免将字段截断*/
(
PLATE,
ACROSS_TIME DATE"YYYY-MM-DD HH24:MI:SS",  /*指定接受日期的格式，相当用 to_date() 函数转换*/
VEH_SPD NUMBER	/*字段指定类型，默认是 CHARACTER 类型*/
)
  1. 然后再执行sqlldr命令，
sqlldr userid@service control=user.ctl

命令包含以下关键字：

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

以上部分参数可以在控制文件中以OPTIONS()来定义

性能提升

  1. Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中，可以提高导入数据的性能。当然，在很多情况下，不能使用此参数(如果主键重复的话会使索引的状态变成UNUSABLE)
  2. 使用 ROWS=n 限定commit频率
  3. 使用并行载入，只在** Direct path loads模式下可用，这个选项可让SQL*Loader**同时执行多个任务。
    sqlldr control=first.ctl  parallel=true direct=true	
sqlldr control=second.ctl parallel=true direct=true
  4. 使用unrecoverable，此选项禁止redo日志的产生，只在** Direct path loads**模式可用。
  • 效果
    效果

Reference

  1. SQL*Loader详解
  2. Maximizing SQL*Loader Performance