Solved

Copy data from MYSQL to Oracle

Posted on 2014-12-16
6
263 Views
Last Modified: 2014-12-22
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
0
Comment
Question by:angel7170
  • 2
  • 2
  • 2
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40503219
3 questions:
* 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"
0
 

Author Comment

by:angel7170
ID: 40503252
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
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40504012
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40504281
0
 

Author Comment

by:angel7170
ID: 40507880
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.
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 40507946
This does not indicate a problem in SQL Developer.  This indicates an "out-of-space" problem in the database.  You didn't tell us which tablespace this error happens in.  It could be:
1. the data tablespace where this table is
2. the undo (or rollback) tablespace
3. an index tablespace (assuming the table has indexes in place as the data is loaded)
4. a temp tablespace

It looks like you need to add space in one or more of these tablespaces.  Yo ucan use SQL Developer or another tool for this.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now