如何从SQLSERVER迁移大批量数据到ORACLE
工具：Microsoft DTS
Oracle Sqlload
背景：
目前随着客服数据量的增大，大量的局从SQLSERVER迁移到oracle数据库，目前常用的工具是微软SQLSERVER的DTS，对于服务器配置比较高的局（小型机）可以用此方法。但是对于某些配置不太高的，大表用此方法就太慢。因此，本文就数据迁移提出一个快速的方法。
正文：
首先针对同一配置的两种方法进行对比：
配置：
SQLSERVER 服务器:IBM5600 733Mhz/512M/36G Raid5
Oracle for NT 服务器:IBM5600 867Mhz/512M/36G Raid5
使用DTS直接连接两种数据库传输258万数据历时7.5小时，运行DTS工具机器CPU平均占用率99%
使用本文介绍的方法传输258万数据表共6个表，历时1小时，运行机器CPU平均占用率60%
分析：
微软DTS在异种数据库之间传送数据时，采用ODBC开放数据连接方式，而ODBC的速度慢是众所周知的缺点，导致异种数据库间传送数据慢。而SQLSERVER是微软自己的数据库系统，采用DTS导SQLSERVER的时候能很快，这是一个优点；oracle使用工具sqlldr能够很快的将文本数据导入oracle自己的数据库也是一个优点。结合两个工具，可以提供快速的对大量数据进行迁移。
sqlldr介绍：
sqlldr 可以将文本文件形式的数据导入oracle数据库，语法如下：
sqlldr username/password control=controlfile {log=logfile bad=badfile}
control=controlfile
controlfile描述了文本文件的格式，包括字段描述并且指定要导入数据的文件名；
log=logfile
logfile将sqlldr执行过程情况做记录，如失败则有失败原因等；
bad=badfile
badfile 将入库失败的记录记录下来
三种file都可以按照实际情况自己命名。
如：sqllldr dbo/dbo control= log=bill1.log bad=bill1.ba
现在以一个实例来详细说明使用，在实例中讲解controlfile注意事项：
以客服系统中的话单表:tbilllog1为例
create table tbilllog1(
callid char(25),
callidnum numer(5),
callerno varchar(26),
calleeno varchar(26),
waitbegin datetime,
waitend datetime,
ackbegin datetime,
ackend datetime ,
...
)
第一步：从SQLSERVER中导出数据
在SQLSERVER数据库上使用Import and Export Data工具
1.在choose a Data source页
选取：
source= Microsoft OLE DB Porvider for Sql Server
server=local
database=icd_db_bill
{use Sql Server authentication}
user=sa
选择下一步
2.在choose destination 页中
选取destination为Text File
file name ="d:\"
选择下一步
3. 选择use a query to specify the data to transfer
选择下一步
注：由于SQLSERVER导出时会将table_id作为一个域导出，而oracle没有（oracle 为rowid），所以必须选择此项为use a query to specify the data to transfer而不是copy table(s) from source database
4.在Query Builder选项进入
选取tbilllo
g1，添加然后将table_id从列表中去掉
一直确认直到select Destination File Format页面
5.选择
file type =ansi （缺省）
row={cr}{lf} （缺省）
column=comma （缺省）
text= double quote{"} （缺省）
在comumn和text选项会影响到oracle的controlfile的设置
第二步：
oracle方面的设置
首先将 拷贝到l的目录
提示：如果NT平台可以先压缩再传送可以节省时间，到目的目录再解开
controlfile example: l
LOAD DATA
INITFILE ''
insert into table tbilllog1
fields terminated by ',' optionally enclosed by '"'
(
callid,
callidnum,
callerno,
calleeno,
waitbegin date "yyyy--mm-dd hh24:mi:ss",
waitend date "yyyy-mm-dd hh24:mi:ss",
ackbegin date "yyyy-mm-dd hh24:mi:ss",
ackend date "yyyy-mm-dd hh24:mi:ss",
...
)
*fields terminated by ','指明域由，分割，对应到上面 第5步的column=comma
*optionally enclose by '"'指明字符型数据有" "括起来，对应到text=double quote{"}如果选择text=none 不加此语句。
上例子中可以看见char,number型数据无需在l中专门设置，而对于date型需要针对进行设置：
RECORD example1:
" 980956750 331 7 0 ",-1,"5579030","170",2001-01-31 23:59:10,2001-01-31 23:59:10,....
Record example2:
980956750 331 7 0,-1,5579030,170,2001013,20010131,....
在例一中字符串由 "标志，所以需要加optionally enclosed by '"'，在例二中不需
在例一中日期格式需要在域名后加 'date "yyyy-mm-dd hh24:mi:ss",
即
LOAD DATA
INITFILE ''
insert into table tbilllog1
fields terminated by ',' optionally enclosed by '"'
(
如何连接oracle数据库callid,
callidnum,
callerno,
calleeno,
waitbegin date "yyyy--mm-dd hh24:mi:ss",
waitend date "yyyy-mm-dd hh24:mi:ss",
ackbegin date "yyyy-mm-dd hh24:mi:ss",
ackend date "yyyy-mm-dd hh24:mi:ss",
...
)
在例二中日期格式需要在域名后加' date "yyyymmdd" '
即LOAD DATA
INITFILE ''
insert into table tbilllog1
fields terminated by ',' optionally enclosed by '"'
(
callid,
callidnum,
callerno,
calleeno,
waitbegin date "yyyymmdd",
waitend date "yyyymmdd",
ackbegin date "yyyymmdd",
ackend date "yyyymmdd",
...
)
具体日期格式问题可以参见oracle文档关于to_char转换日期到字符数据时格式。
最后，在oracle所在操作系统上执行
sqlldr dbo/dbo l
总结：在数据量小的情况下，可以使用DTS进行传送比较方便快捷。而大数据量的表传送时易操作性应该让位于速度，尽量采用各自带的工具。而此文同样可以适用于Sybase迁移到oracle ，不同点是Sybase使用bcp比较方便。当然也可以使用DTS在Sybase和oracle之间传送小数据量表。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论