This content has been marked as final. Show 6 replies
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.1 person found this helpful
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)
It was a mismatch of sql server delimiter and what was specified in the control file.