We are migrating an SQL server 2005 DB to Oracle 10 g 64 bit 10.2.0.5. The source sql server DB has few tables (<10) and only few stored procedure(<5). However one table of source is very huge, it has 70 million records in it. I started the migration process; it was running till 18 hours passed away then I cancelled it. I am using SQL Developer 184.108.40.206 with online migration.
Kindly let me know if I choose offline migration will it reduce the data move time. Is there some other tool by oracle that can migrate huge data (records in millions) quickly. Is there some configuration in SQL Developer that can catalyses the process?
an online data move with 70 million records is really not advisable. As the SQL Developer documentation says:
"Online data moves are suitable for small data sets, whereas offline data moves are useful for moving large volumes of data." I would not say that 70 million records is a small data set.
Online data move is just a COPY via JDBC, whereas the offline data move is an export using BCP and an import using SQL*Loader. That explains why the offline data move is faster.
I tried using SQL Loader to move data. The file exported by SQL server for the transaction table is of size around 17 GB. The sql loader stopped giving error on file size limit. The error is given below:
SQL*Loader: Release 10.2.0.5.0 - Production on Thu Mar 22 15:15:28 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL*Loader-510: Physical record in data file (E:/mssqldata/transaction.txt) is longer than the maximum(1048576)
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
Please help me out how do I move such huge data to oracle from sql server.
Make sure you are using the right delimeters in the control file for columns as well as newline. Sometimes the default delimeters used in the SQL*Loader control file may not match with what you have in flat files created from the source.
Did you already try to increase the READSIZE for SQL*Loader? 1048576 is the default. Please read the following note in My Oracle Support:
SQLLDR NOT LOADING ALL DATA IN DAT FILE : SQL*Loader-510/SQL*Loader-2026 (Doc ID 741100.1)