Recently I had a task to convert a database from MySQL to Oracle. I am not sure how many records a database needs to be considered big, but this one has 15 tables; few of them have 2-5 million rows, and one has over 20 million. It was a fun time for me figuring out the “best” method to deal with the situation. It is definitely not the best method for everyone, but I think it would be useful for some.

My situation:

  • I DO NOT have admin access to the Oracle database server and use SQL Developer to connect to the database
  • I DO have admin access to the MySQL server
  • I DO NOT have access to premium tools for this job, so just free stuff

Let’s do it!

Convert tables structures

This step can be done in various methods, I suppose. However, I found an application called RazorSQL that can deal with various types of database, including MySQL and Oracle. When I open connections to both databases, I can actually use the Convert Table(s) feature in RazorSQL to convert both table structure and data between types of database.

Here I use it to convert only the table structures (the reason not to migrate the data will be explained shortly). All you need to do is to choose the MySQL database, right-click the database then “Tables” on the left sidebar and choose “Database Conversion > Convert [Multiple] Table” with the configuration similar to this screenshot below.

Convert Table Structures with RazorSQL

Export the data

As mentioned above, RazorSQL does provide the data conversion. However, the method was not so good, in my opinion. Everytime I tried, it failed after a few thousand of records. I guess it is not stable enough, or it is not for million-record tables.

Thus, now I can only import the data to the Oracle database using CSV files. In fact, I do not know if Oracle provide a command-line interface for administrator to quickly import data, like MySQL LOAD DATA INFILE statement, or not. Fortunately, SQL Developer supports importing data from CSV files.

Definitely I could use mysqldump to take data from the MySQL server, which is the fastest method I have known of. However, for some reason, I could not get the field delimiter and special character escaping in the CSV exported with mysqldump to be imported properly into the Oracle database, so I ended up using PhpMyAdmin to export the data. With millions of row to be exported, I needed to extend the time for the PHP script to execute, and mentioned it on my previous post.

Having that execution time resolved, this export step is no hassle. Just go to PhpMyAdmin and choose “Export” with Quick method and format of ouput file is “CSV”, and you will have data files ready to be imported by SQL Developer.

Choose the import wizard

It should be under the “Actions” menu when you open a table. It will ask you to point to the exported CSV file. You can also use some other file format as xls of Microsoft Excel.

SQL Developer Actions Menu

Specify import options for the CSV file pointed (step 1 of the wizard)

Basically, what you should care about might be only two things: the header option (to tell if you have the first row as the fields’ headers or not), and the encoding of the file (where I chose UTF-8 for best handling of special, foreign characters).

First step Import Wizard

Choose the correct mapping to the table fields being imported into (step 4 of the wizard)

Step 2 & 3 & 5 of the wizard have nothing special, so just click “Next”. In step 4, you need to correctly map the fields in the CSV file to the fields in the table. I had CSV files with no header row, so I specified the mapping method to be “Position”.

Map the column

Start the import!

I noticed that import speed greatly depended on the speed of the network I was using to connect to the Oracle database. When I was on a “slow” 10Mbps network, it took me about 30 minutes to finish importing of 2.7 million records. With a similar amount of time, when I was on a high speed network (~100Mbps, which also closes to the database, actually), SQL Developer could import over 20 million rows.

I recommend doing this import task when you have plenty of time, say, overnight.

Thank you for reading this! I hope it will help database newbies like me to get an overview of converting data from MySQL to Oracle database.