Unanswered: Transfer large data from Sybase to Oracle
I need to transfer data from 12 tables in Sybase database to 12 tables in Oracle 9i database.
The volume of data is huge (millions).
Also, the data transfer should be controlled based on some SQL queries i.e., I do not want to transfer entire data from the tables, I'll issue some SQL queries, and the data obtained through those queries should only be transferred.
And in case there is a break in between, I should be able to report the point of break, and be able to continue from the last stopped point.
What is the best way to achieve this?
I'm currently writing a Java + JDBC program to do this, but I'm worried about the performance, reliability etc.
How often will you export data? Do you need full automated process?
1) create temp table on Sybase which will be populated using Select statement (here you can limit records which shouldn't be exported)
2) export this table to CVS file. I'm not familiar with Sybase, but I believe there is some tool which allowed do that. If the tool is powerfull you can skip step 1 and limit records here.
3) import CVS file into Oracle using SQL Loader
If there's a tool in Sybase which can be called from OS command line you'll be able create this as a full automated process (some shell script or batch file). I think it's better solution than Java coding.
1. I can't create any temp tables on Sybase as I have only read permissions on that db.
2. The process is to be done daily. Also, the user is non-technical and we need to provide a GUI to the user to perform this job. That is the reason why I've started off with Java + JDBC.
So, in this scenario, what would be the better option?
I think that GUI shouldn't be primary goal - it can be done, as Madafaka told you, using a batch file or something like that, and the user doesn't have to know anything about the process. 'Click here' should be enough.
But behind 'Click here' button are two or three steps; just to mention them again, as I completely agree with Madafaka's suggestion:
- create an ASCII text file using SELECT statements in Sybase. Although you are not allowed to write into this database, you should be able to write to the hard disk.
- if Oracle database is elsewhere, FTP this file to another loaction. If it is on the same machine, you won't have to do that.
- use SQL*Loader and import data into the Oracle database
P.S. What is the meaning of the "CVS" file? I searched the Acronym finder, but didn't see anything suitable. I'd just say that CVS - in "our" context - isn't "Cyclic Vomiting Syndrome" (although sometimes it might be)