angel7170
asked on
Copy data from MYSQL to Oracle
Hello,
What is the best way to copy data from MYSQL tables with 8 million records copied over to ORACLE database without performance issue?
Please shed some light.
thank you
What is the best way to copy data from MYSQL tables with 8 million records copied over to ORACLE database without performance issue?
Please shed some light.
thank you
ASKER
do you have already some way(s) to copy data over?
We tried to use Oracle SQL developer to copy data but bring 8 millions records seems to take lot of time and even it doesn't return results.
* what are your expectations in this job: one-time, daily, or something like near-real-time or even real-time?
on daily
* what exactly you do consider "performance issue"
It just doesn't return results
We tried to use Oracle SQL developer to copy data but bring 8 millions records seems to take lot of time and even it doesn't return results.
* what are your expectations in this job: one-time, daily, or something like near-real-time or even real-time?
on daily
* what exactly you do consider "performance issue"
It just doesn't return results
I don't know MySQL, so I can't address your options for getting the data out of MySQL. And, you didn't tell us anything about your network between the two systems, or what kind of storage systems either or both databases use, so I can't address those either. For Oracle, the fastest way to load data into Oracle tables is with Oracle's SQL*Loader utility. This can process ASCII text files in either fixed-length or delimitted formats at speeds up to 20x faster than via SQL insert statements. This assumes though that you can create consistently-formatted ASCII text files and get them to a disk device that is accessible from your Oracle server. Also, this requires that you write a control file for SQL*Loader that tells Oracle how to interpret the ASCII data.
If this data will be going into new tables in Oracle, there should be no indexes on the table as the data is loaded. Create the index(es) after the data is loaded for the fastest total time, and to get indexes that are as compact as they can be. If you will be loading this data into existing tables and you need to support access to the existing data while new data is being loaded, you will have to leave the existing indexes in place while you load the data. That will slow the process a bit.
If this data will be going into new tables in Oracle, there should be no indexes on the table as the data is loaded. Create the index(es) after the data is loaded for the fastest total time, and to get indexes that are as compact as they can be. If you will be loading this data into existing tables and you need to support access to the existing data while new data is being loaded, you will have to leave the existing indexes in place while you load the data. That will slow the process a bit.
so, had you tried these steps from this techique:http://www.oracle.com/technetwork/developer-tools/sql-developer/omwb-getstarted-093461.html
ASKER
I tried SQL developer but it errors out with table space. Since the table has about 8-12 million records it is not able to do it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
* do you have already some way(s) to copy data over?
* what are your expectations in this job: one-time, daily, or something like near-real-time or even real-time?
* what exactly you do consider "performance issue"