从Sql server 2000往Oracle 10g数据库迁移遇到的问题

从Sql server 2000往Oracle 10g数据库迁移遇到的问题

• 博客分类：
• eclipse rcp

SQL Server SQL Oracle Hibernate Struts

真没劲，又干这种没技术含量的活了！把客户一个用了N年的数据库（Sql Server 2000）要迁移到我们系统的数据库中来（选用的是Oracle 10g）。比较犹豫怎么导的问题，要么写个小JDBC程序？要么用DTS？想了想，还是不要再发明个轮子算了（其实我类似的轮子都做了N个了，只是懒得再动手改）

在DTS导入的时候会遇到几个问题：
1、导过来的表名比较古怪，都是加上双引号的，写的SQL老是说找不到表名，晕死我，对象浏览器里明明就是那个表名。一阵犯嘀咕，觉得自己没有老花眼呀。唉，所以DTS导过来的，要先改名，如：
alter table "Base" rename to base;

2、DTS在导一些表时老是会出错，后来检查发现，这个笨蛋把人家很多应该是number型的给设置了varchar2，或是date型给整成了varchar2，诸如此类了，所以在不同数据库中导数据时，要注意检查数据类型的对应关系。

3、DTS在导几个大表时，老是提示一些莫名其妙的错误。诸如：ROW-00060: Internal error: [dainsert,16]，想破脑袋你也想不出来，是内存给DTS用完了（我晕倒，我是4G的内存呀！），没办法你分几次导入吧（用SQL语句来导入）

通过此次导数据，我深刻的总结出，最没技术含量的活有时也能让你晕菜！

本人原创文章，欢迎转载，转载请注明出处！

 

 

当一个表非常大时，会报错:

在目的的行号为3021679处出错。到目前为止，在该任务中遇到的错误数：1。

ROW-00060: Internal error: [dainsert,16]

在google上查了一下：

Ok So I see a lot of people having problems with Oracle and SQL Server. You
are not alone. What a lot of people do (mainly due to speed) is they export
to text files and import those files on the other side.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


"StephenP" <StephenP@discussions.microsoft.com> wrote in message
news:D1AE990D-2379-4CAB-A5A8-637041A1D770@microsoft.com...
> What I have been noticing is when you move a single table (around 250000
> records), it works without any errors. If you move that same table with a
> few other tables (around 250000 records), the same error occurs. It seems
> to
> prefer tables no larger than 250000 records. Any multiple table exports
> or a
> single table larger than 250000, it will cause an error.
>
> "Alex" wrote:
>
  >> Oracle does not null values very much
  >> check the value in (key) columns in row # 212101
  >> "StephenP" <StephenP@discussions.microsoft.com> wrote in message
  >> news:98669A8D-605E-437C-8409-81D2E63F6151@microsoft.com...
   >> >I have been having problems copying very large table (500,000 records or
   >> > more) from a SQL Server 2000 database to a Oracle 9i database. I have
   >> > been
   >> > using DTS wizard to design a very simple data export. The DTS starts
   >> > out
   >> > working fine but usually after 200,000 records, I will receive this
   >> > error:
   >> >
   >> > error at destination for row number 212101. errors encountered so far
   >> > in
   >> > this task: 1.
   >> > row-00060: internal error: [dainsert,16]
   >> >
   >> > I am not sure what is causing the error. Does anyone know what the
   >> > problem
   >> > is and how to resolve it? If there is a better method to export the
   >> > data,
   >> > please let me know. Thanks.
   >> >
   >> > Stephen
  >>
  >>
  >>