Migration made easy with Oracle SQL Developer

A couple of weeks ago I had to migrate lots of data from MySQL to PostgreSQL and it was a pain in the a**. Even though MySQL offers to use a compatibility-mode when exporting tables for Postgres those dump files could not be imported to the Postgres database at all. The DDL was so wrong from Postgres' point of view that I had to search & replace lots of stuff and take care of escaping strings in a few thousand rows...

While I was looking up some data in Oracle's SQL Developer I noticed the 'Migration' menu for the very first time.
I have to admit that I use the SQL developer for almost a year now but did not find the time to explore it in much more detail.

I was wondering if migrating data from the MySQL database to an Oracle database would work out much better/easier with this tool, so I gave it a try:


1. I created a user called MIGRATIONS and granted RESOURCE, CREATE SESSION and CREATE VIEW to him. I then created and connected to a database connection to that user in the SQL Developer and created (right-click) the migration repository there.

2. I then created and connected to a database connection to the destination database and made sure that user had enough rights to create users and things.

3. Now I connected to the MySQL database and captured (right-click) the schema of interest.

4. I got a list of captured objects in a new tab now and was able to convert those. They appeared in another new tab labled 'converted objects'.

5. I renamed the schema from those converted objects (right-click) and generated (right-click again) lots of statements that appeared in a new SQL worksheet.

6. From there I selected the target database from the drop-down in the upper right corner and hit 'run script ()'.

7. I then connected to the created schema in my destination database.

8. Finally I chose 'migrate data' from the migration menu and selected source, destination and the converted objects to get the data transferred.

Like a charm!
Okay, there have been some errors mentioned while the migration process, but it seemed to me that all have been handled somehow as there have been no errors in the Migration log and all tables had the same data as in the source database.

I hopefully have to migrate to Oracle for the next project.

Technorati Tags: database, migration, mysql, oracle, dba, sql developer