ITPub博客

首页 > 数据库 > Oracle > sqlldr 完成mysql到oracle的数据迁移

sqlldr 完成mysql到oracle的数据迁移

原创 Oracle 作者:贺子_DBA时代 时间:2015-07-19 16:13:22 0 删除 编辑
sql loader可以把文本文件倒进oracle数据库中,是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具,但是速度比较慢,另外对blob等类型的数据就有点麻烦了
下面演示利用sqlldr从mysql迁移某个表到oracle数据库。
一:在mysql把表search_test_zh_cn.Industry导出成.txt文件。
mysql> select * from search_test_zh_cn.Industry;
+------------+-----------------+----------+
| industryId | industryName    | langType |
+------------+-----------------+----------+
|          1 | 建材与冶金      | zh_CN    |
|          2 | 化工与石化      | zh_CN    |
|          3 | 矿产与能源      | zh_CN    |
|          4 | 橡胶与塑料      | zh_CN    |
|          5 | 机械与设备      | zh_CN    |
|          6 | 照明与安防      | zh_CN    |
|          7 | 电器与电子      | zh_CN    |
|          8 | 包装与文教      | zh_CN    |
|          9 | 纺织与皮革      | zh_CN    |
|         10 | 服装与饰品      | zh_CN    |
|         11 | 食品与农业      | zh_CN    |
|         12 | 家居与百货      | zh_CN    |
|         13 | 美妆与美容      | zh_CN    |
|         14 | 车船与交通      | zh_CN    |
|         15 | 医药与保健      | zh_CN    |
|         16 | 商务与服务      | zh_CN    |
+------------+-----------------+----------+
16 rows in set (0.00 sec)
导出语句:
mysql> select * from search_test_zh_cn.Industry into outfile '/tmp/liuwenhe.txt' fields terminated  by ",";
Query OK, 16 rows affected (0.00 sec)
####题外话:如果要倒进mysql的某张表语句:mysql> load data infile '/tmp/liuwenhe.txt' into table search_test_zh_cn.Industryaa fields terminated  by ",";
Query OK, 16 rows affected (0.00 sec)
Records: 16  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select * from search_test_zh_cn.Industryaa;
+------------+-----------------+----------+
| industryId | industryName    | langType |
+------------+-----------------+----------+
|          1 | 建材与冶金      | zh_CN    |
|          2 | 化工与石化      | zh_CN    |
|          3 | 矿产与能源      | zh_CN    |
|          4 | 橡胶与塑料      | zh_CN    |
|          5 | 机械与设备      | zh_CN    |
|          6 | 照明与安防      | zh_CN    |
|          7 | 电器与电子      | zh_CN    |
|          8 | 包装与文教      | zh_CN    |
|          9 | 纺织与皮革      | zh_CN    |
|         10 | 服装与饰品      | zh_CN    |
|         11 | 食品与农业      | zh_CN    |
|         12 | 家居与百货      | zh_CN    |
|         13 | 美妆与美容      | zh_CN    |
|         14 | 车船与交通      | zh_CN    |
|         15 | 医药与保健      | zh_CN    |
|         16 | 商务与服务      | zh_CN    |
+------------+-----------------+----------+
16 rows in set (0.00 sec)     ####
在相应目录下查看导出的内容,确定导出成功。
[root@localhost tmp]# cat liuwenhe.txt
1,建材与冶金,zh_CN
2,化工与石化,zh_CN
3,矿产与能源,zh_CN
4,橡胶与塑料,zh_CN
5,机械与设备,zh_CN
6,照明与安防,zh_CN
7,电器与电子,zh_CN
8,包装与文教,zh_CN
9,纺织与皮革,zh_CN
10,服装与饰品,zh_CN
11,食品与农业,zh_CN
12,家居与百货,zh_CN
13,美妆与美容,zh_CN
14,车船与交通,zh_CN
15,医药与保健,zh_CN
16,商务与服务,zh_CN
二:把相应文件scp到目的服务器上,
[root@localhost tmp]# scp /tmp/liuwenhe.txt   oracle@192.168.4.225:/backup
oracle@192.168.4.225's password: 
liuwenhe.txt                                                                                                                                  100%  391     0.4KB/s   00:00    
You have new mail in /var/spool/mail/root
三:在目标服务器上利用sql lorder 进行数据导入。
1,在目标端创建好要目的表,字段和mysql的search_test_zh_cn.Industry保持一致。
SQL> create table INDUSTRY
  2  (
  3    industryid   INT,
  4    industryname VARCHAR2(50),
  5    langtype     VARCHAR2(20)
  6  );    
 table  created    
2,创建控制文件:
[oracle@hu225 backup]$ vi liuwenhe.ctl     
load data
infile '/backup/liuwenhe.txt'
badfile '/backup/liu.bad'      ###插不进去的数据会进入bad文件里。如果都插进去了,就不会生成bad文件,
into table liuwenhe.Industry
fields terminated by ','
TRAILING NULLCOLS       ##最好有这个参数,保证有空的也能插入进去,    指记录中没有内容(空格、空白、或null)的列被当作null 列
(INDUSTRYID,INDUSTRYNAME,LANGTYPE)  
3,执行命令:
[oracle@hu225 backup]$ sqlldr liuwenhe/liuwenhe control=/backup/liuwenhe.ctl      log=/backup/liuwenhe.log 

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 19 14:34:52 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 16
4,看执行的log文件。
[oracle@hu225 backup]$ cat liuwenhe.log 

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 19 14:34:52 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   /backup/liuwenhe.ctl
Data File:      /backup/liuwenhe.txt
  Bad File:     /backup/liu.bad

  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table LIUWENHE.INDUSTRY, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
INDUSTRYID                          FIRST     *   ,       CHARACTER            
INDUSTRYNAME                         NEXT     *   ,       CHARACTER            
LANGTYPE                             NEXT     *   ,       CHARACTER            


Table LIUWENHE.INDUSTRY:
  16 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.


Space allocated for bind array:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            16
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Sun Jul 19 14:34:52 2015
Run ended on Sun Jul 19 14:34:52 2015

Elapsed time was:     00:00:00.06
CPU time was:         00:00:00.02
5.检查结果:  
SQL> select count(*) from liuwenhe.Industry;

  COUNT(*)
----------
        16
迁移成功!!!
小节:[oracle@hu225 backup]$ sqlldr liuwenhe/liuwenhe control=/backup/liuwenhe.ctl      log=/backup/liuwenhe.log 这个步骤中如果丢掉了  log=/backup/liuwenhe.log 那么他会在你执行命令的当前目录下,这里就是/backup下生成名字和相应的控制文件对应的.log文件,如果你的控制文件是pp.ctl  那么自动生成pp.log 文件。badfile '/backup/liu.bad'  这个参数,插不进去的数据会进入bad文件里。如果都插进去了,就不会生成bad文件,因为数据的问题,导致进不去,那么会生成相应的.bad文件。默认64行数据就commit; 如果你要想跳过某些列,可以用参数filler ,例如:你想加载的数据格式:1,liuwenhe,man,25 
                                               2,dashuai,man,28 
                                               3,helei,man,21
但是你不想要第三列,你可以这样写控制文件:
load data
infile '/backup/liuwenhe.txt'
badfile '/backup/liu.bad'      
into table liuwenhe.Industry
fields terminated by ','
TRAILING NULLCOLS       
(INDUSTRYID,INDUSTRYNAME,
dummy1 filler ,LANGTYPE)     这样允许你映射一个输入记录中的一列,但不把他放在数据库中。也就是说虽然数据库中只有三个字段,但是真正的数据有四个字段,你可以通过dummy1 filler , dummy2 filler.。。。这样有选择性的跳过一些不想要的列值。

insert数据加载方式(默认)

加载方式有如下四种:

append:原先的表有数据就加在后面

insert:(默认值)装载空表,如果原先的表有数据SQLLOADER会停止

replace:原先的表有数据原先的数据会全部删除

truncate:指定的内容和REPLACE的相同会用TRUNCATE语句删除现存数据

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29654823/viewspace-1739789/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
记录工作中遇到的问题,积少成多,坚持就是胜利,工作经历:曾就职于国美、中国采购与招标网、目前就职于一家正规消费金融公司负责Oracle和MySQL、mongodb以及sqlserver和hadoop相关运维和优化的工作

注册时间:2014-05-12

  • 博文量
    242
  • 访问量
    1676398