用sqlldr进行数据迁移

公司的业务场景如下：

某一个体系需要进行重构，其中包括表的重新设计和库表数据迁移。而这一块是我负责的，经历了从Java，到DML，到PLSQL的方案转移。之所以写这么多，是想记录我自己的探索过程，如果想看使用sqlldr进行数据迁移的直接步骤，直接跳到“方案3”的结尾处。

 

方案1：用Java进行数据迁移

最先开始选择用Java，是因为之前接触存储过程不是很多，用Java来写，操作对象集合，配置2个datasource，直接查询出来了再insert进行，比较方便。因为这个不是我们这次的重点，就不详叙。简单大致说一下，用的框架是jdbcTemplate，其中插入的时候需要注意的是，采用jdbcTemplate的batchInsert的批量提交方式，减少与数据库的交互次数，提供性能。去掉索引PK等越是后，效率大概是3w条/s。但有一个表特别大有将近2kw条记录，然后领导说，这个方案不行，让我用sqlldr。方案1流产

 

方案2：用DML语句进行数据迁移

由于没用过sqlldr啊，没办法，看想想能不能用其他的方式来做，既然公司的库都是在一个局域网，那我干嘛不直接用DBLINK来做呢？话不多说，我们上代码

-- 建立 DBLINK
create public database link my_dblink connect to old_username  identified by old_password
　　 using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.119)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =old_service)
)
)';

建好DB_LINK之后，我们直接以如下形式进行数据插入（实验证明，这也是最高效的）

insert into XXX (select colnum_a,colnum_b from old_service.YYY@my_dblink) 

不要用下面这种形式，进行创建。因为这样创建，oracle会给你自动优化，你的字段类型本来想是定义为varchar2(255)的，说不定就只有varchar2(60)了，所以表还是创建好了再进行插入（PS.虽然这种方式比上面的方式快。一个是DML一个是DDL，效率肯定是不一样的）

create table XXX as (select colnum_a,colnum_b from old_service.YYY@my_dblink)

这种确确实实是最高效的，但这个方案出现了2个问题

1.有人说，你这个没法记录日志

2.领导坚持用sqlldr

没办法，那只能用sqlldr了

PS.这里我要很愤慨的说一句，数据迁移强烈建议大家用这种DML的方式进行数据插入。有人说没日志，下面的第三种方案是有日志了，但谁TM看呢，我去。第二，领导说用sqlldr，为什么？因为他觉得sqlldr快，既然他要的是快，你就告诉他，sqlldr不可能有这种DML的形式快（sqlldr慢不在于插入慢，在于前面的准备工作慢，后面我会细说sqlldr）

坑爹的，第二个方案也这样流产了

 

方案3：用sqlldr进行数据迁移

没办法，那就老老实实用sqlldr吧，不会就去学嘛。sqlldr在输入插入方面是特别快的，比insert快几个数量级，但为什么我说sqlldr的方案比第二个方案慢呢？因为sqlldr主要负责的是把数据导进去，那数据从哪来呢？

 

这里我简单说一下sqlldr吧

首先你有一个文件叫“user.csv”，内容是这样的

1,tom,23,11-AUG-14

2,jack,25,11-AUG-25

然后你有一个表USER，字段如下 id,name,age,created_time

然后用sqlldr就可以很轻松的把你文件里的数据导到表里面去，sqlldr来导，他怎么知道1对应id，tom对应name，又或者不是“tom,23”对应name呢？

 

执行sqlldr的时候，需要一个ctl文件，我们命名这个文件叫“user.ctl”，内容如下

LOAD DATA
INFILE '/home/oracle/data/user.csv'
APPEND INTO TABLE user
FIELDS TERMINATED BY ','
TRAILING NULLCOLS 
(id,name,age,created_time)

然后我们到oracle的bin目录下，确认找得到sqlldr这个命令，然后在这个目录下执行这个命令

-- silent=feedback,header表示去掉一些回显，如果不加的话，就一直在命令行打数据“插入XXX条”，这样会降低效率的
-- direct=true最好加上，这个是不考虑索引约束啊之类的，总之加了这个，数据会快很多（大概快一倍），不加他会插入的时候考虑一些别的因素，导致插入变慢。
sqlldr new_username/new_password /home/oracle/data/user.ctl silent=feedback,header direct=true

 

PS.大家插入数据的时候，最好还是只建表，不要加索引PK之类，弄好了再加，不然会特别耗时，虽然数据导完了再加索引，本身也是很耗时的- -！不过毕竟比一边插一边检查来得好一些

执行完后，就插入完毕了，去数据库检查一下数据，然后加上你的索引，唯一，PK等，这样就算是迁移完毕了。

 

是不是看上去像童话一样美好？

BUT！理论上的东西，放到现实场景下，就会出现各种问题，我这里还是想重申一个观点就是，HelloWorld确实很重要的，但你要是觉得会写HelloWorld，就能把实际案例上，工作中的问题解决了，那就大错特错了

 

首先我们来说一下用sqlldr会出现的坑

坑1：数据哪来呢？

我们这个demo里面是2条数据，都是我们手写的，但生产上，数据2条吗？2kw还差不多，那这2kw数据怎么来。目前我所知道的比较快捷的方法是用PL/SQL工具来导出是最快的，把你需要的select语句写好，然后放到PL/SQL中去执行，选择导出为CSV文件

这样导是很快的，2kw数据也用不了10分钟，但如果这么轻轻松松就搞定了，还叫坑吗？

不要急，我们一步步发现问题，一步步来解决问题

 

坑2：导出文件有问题？

我们先看看导出来的数据是什么样子的（这里我们就先只导了4条）

首先我们看到，用这个“导出为CSV文件”这个功能，导出来的会自带一条记录--“字段名称”，显然如果我们要用sqlldr来把这个“字段名称”插进去肯定是不行的，需要我们删掉第一行

（注：sqlldr可能有参数设定，不扫描第一行，又或者说这个“导出为CSV文件”功能，可能可以不导这个字段行。这2个都有可能，但笔者没试过，如果有试过的朋友，麻烦评论一下，也好帮助大家）

 

坑3：单元数据如何分隔

接着上面的坑，不管能不能让他不出现第一行，就算是出现，也可以手动删掉第一行，但问题大头不仅仅是在这第一行的问题上折腾。大家看一下 它的规则，他是用引号进行包裹，然后以逗号分隔，那你觉得某个表的memo字段（备注信息），可不可能出现引号和冒号的情况呢？答案是，肯定有！

 

鉴于对PL/SQL的”导出为CSV文件“功能不熟，我们只能另寻他路，既然摆脱工具的束缚，就不会出现上面所述的坑，既然这样那我们就自己来写数据

 

怎么写数据？答案是跑存储过程

我们也不卖关子了，现在一次性把所有可能遇到的坑全部说出来，再上代码

 

1.确保你的分隔符不会出现在数据信息中

比如一开始我想用”{“来做分隔符，结果我发现有这样的数据：”用户备注{用户明天来取货}“。然后我想用”|||“来做分隔符，没有人用这个吧，结果发现又有这样的数据”- -|||“

总结：选分隔符的时候，尽量选少重复的，然后对各个可能出现这个分隔符的做查询，有则修改，比如把所有的”|||“改成空字符等

 

2.去掉换行符

因为sqlldr默认是一行数据为一条记录，有的字段，他有回车换行符，这样导致你得到的数据会换行，这时候sqlldr会认为是2条记录，这样肯定是会插入失败的

 

3.时间问题

时间建议大家还是将date数据转为字符串，然后读取的时候，再按指定格式的字符串来转换，不然有时候是真的会出现不识别的情况的（测试环境直接拉下来的date可以用sqlldr，生产环境拉下来的date由于编码不一样，变成中文了，导致sqlldr不识别）

然后在ctl文件中，字段后面加上 "TO_DATE(:created_time,'yyyy-mm-dd hh24:mm:ss')"，当然你写出来的date，也要是这种格式的

 

4.字段数据过长

如果不指定，sqlldr有一个默认最长字符的限制，sqlldr默认字符串的最大长度为255，多于255的会舍弃，但我们有的字段，长度确确实实是多于255，那怎么解决呢？

好解决，在ctl文件中，字段后面加上”char(2000)“，具体多少根据你的实际需求来

话不多说，上代码

 

写数据文件的存储过程函数

create or replace directory MY_DIR
  as '/home/oracle/data';

declare
  file_out  Utl_File.file_type;
  log_out   Utl_File.file_type;
  file_path varchar2(100);
  file_name varchar2(100);
  log_name  varchar2(100);
  count_sum number(10);
  date_start Date;
  date_end Date;

  cursor c is (
  select
   r.id ID,
   s.id UID_ORI,
   replace(replace(r.password,CHR(10),''),CHR(13),'') TX_PASSWORD,
   s.password LOGIN_PASSWORD,
   s.username SYSTEM_NAME,
   replace(replace(r.name,CHR(10),''),CHR(13),'') NAME,
   case
     when r.gender like '%男%' then
      0
     when r.gender = 'male' then
      0
     when r.gender like '%女%' then
      1
     when r.gender = 'female' then
      1
     else
      NULL
   end GENDER,
   r.gender GENDER_ORI,
   r.status_code STATUS_CODE,
   replace(replace(s.email_validate,CHR(10),''),CHR(13),'') EMAIL,
   substr(replace(replace(s.realname,CHR(10),''),CHR(13),''),1,20) NICKNAME,
   replace(replace(r.remark,CHR(10),''),CHR(13),'') REMARK,
   r.application_source_code FROM_SYSTEM,
   decode(r.created_by, NULL, 'Admin', replace(replace(r.created_by,CHR(10),''),CHR(13),'')) CREATED_BY,
   r.created_by created_by_ORI,
   to_char(decode(r.created_timestamp, NULL, sysdate, r.created_timestamp),'yyyy-mm-dd hh24:mi:ss') CREATED_TIME,
   r.created_timestamp created_timestamp_ORI,
   decode(r.updated_by, NULL, 'Admin', replace(replace(r.updated_by,CHR(10),''),CHR(13),'')) UPDATED_BY,
   r.updated_by updated_by_ORI,
   to_char(decode(r.updated_timestamp, NULL, sysdate, r.updated_timestamp),'yyyy-mm-dd hh24:mi:ss') UPDATED_TIME,
   r.updated_timestamp updated_timestamp_ORI
    from crm_customer r, hyt_user_asso o, bbs_members s
   where o.biostime_id = to_char(r.id)
     and o.mama100_username = s.username);
begin
  file_path := 'MY_DIR';
  file_name := 'cust_customer_2.csv';
  log_name  := 'cust_customer_2.log';
  file_out  := Utl_File.fopen(file_path, file_name, 'W');
  log_out   := Utl_File.fopen(file_path, log_name, 'W');
  count_sum := 0;
  date_start := sysdate;

  Utl_File.put_line(log_out,'=====================CUST_CUSTOMER_2开始，导出与BBS_MEMBERS相关联的记录=======================');
  for my_c in c loop
    Utl_File.put_line(file_out,my_c.ID||'|||'||my_c.TX_PASSWORD||'|||'||my_c.LOGIN_PASSWORD||'|||'||my_c.SYSTEM_NAME||'|||'||my_c.NAME||'|||'||my_c.GENDER||'|||'||my_c.STATUS_CODE||'|||'||my_c.EMAIL||'|||'||my_c.NICKNAME||'|||'||my_c.REMARK||'|||'||my_c.FROM_SYSTEM||'|||'||my_c.CREATED_BY||'|||'||my_c.CREATED_TIME||'|||'||my_c.UPDATED_BY||'|||'||my_c.UPDATED_TIME);
    count_sum := count_sum + 1;
  end loop;
  date_end := sysdate;
  Utl_File.put_line(log_out,'========CUST_CUSTOMER_2结束=========导出数据：'||count_sum||'==========耗时：'||(date_end-date_start)*24*60*60||'秒');
  Utl_File.fclose(file_out);
  Utl_File.fclose(log_out);
end;

 

sqlldr所需要的ctl文件内容

LOAD DATA
INFILE '/home/oracle/data/cust_customer_2.csv'
APPEND INTO TABLE cust_customer
FIELDS TERMINATED BY '|||'
TRAILING NULLCOLS 
(ID,TX_PASSWORD,LOGIN_PASSWORD,SYSTEM_NAME,NAME,GENDER,STATUS_CODE,EMAIL,NICKNAME,REMARK char(2000),FROM_SYSTEM,CREATED_BY,CREATED_TIME "TO_DATE(:created_time,'yyyy-mm-dd hh24:mm:ss')",UPDATED_BY,UPDATED_TIME "TO_DATE(:updated_time,'yyyy-mm-dd hh24:mm:ss')")

最后切换到oracle的bin目录，执行sqlldr命令

sqlldr new_username/new_password /home/oracle/data/cust_customer_2.ctl silent=feedback,header direct=true

 

经测试，2000w数据，写出来需要20分钟，导进去仅需要3~5分钟

 

总结就是，sqlldr在进行数据导入是非常迅速的，但很多时候问题会变成，如何得到数据文件，一般都是自己写出来，如果是通过工具生成，就得考虑我上面说的那几个坑了，我把生产环境的所有问题，尽可能的给大家罗列出来，还是那句话，HelloWorld是很重要的，但如果你要是觉得你会HelloWorld，其他的就七七八八差不多，那就大错特错了。记录自己，与君共勉，加油！！！