How to import a .dmp file (Oracle) into MySql DB?

Question

The .dmp is a dump of a table built in Oracle 10g (Express Edition) and one of the fields is of CLOB type.

I was trying to simply export the table to xml/csv files then import it to the MySql, but the export simply ignored the CLOB field... (I was using sqldeveloper for that).

I noticed this post explaining how to extract the CLOB to text file but it seems to miss the handling of the other fields or at least the primary key fields. can it be adopted to create a csv of the complete table? (I am not familiar with plsql at all)

As the brute force approach, I can use my python interface to simply query for all the records and spool it to a flat file but I'm afraid it will take a LOOOONG time (query for all records replace all native commas with the ascii... )

Thanks guys!

Solution 1

After about 2 hours of installing and uninstalling the MySql on the same machine (mylaptop) in order to use the migration tool kit as suggested by longneck, I decided to simply implement the dump and here it is for the likes of me that have minimal admin experience and get hard time to make both DBs work together (errors 1130, 1045 and more).

Surprisingly, it is not as slow as I expected: OraDump

Any comments and improvements are welcomed.

Solution 2

if you can get the mysql server and the oracle server on the same network, you might want to look at the mysql administrator tools, which includes the migration toolkit. you can connect to the oracle server with the migration toolkit and it will automatically create tables and move data for you.

Solution 3

Here is a documentation explaining the migration process: http://www.mysql.com/why-mysql/white-papers/mysql_wp_oracle2mysql.php

and you can use Data Wizard for MySQL . Trial version is fully usable for 30 days.

Comments

[Mr. Lance E Sloan]

The link for the white paper at mysql.com is broken. Data Wizard for MySQL is for Windows only.

[Mat]

The link to OraDump.py is dead.

[ScienceFriction]

thanks. they closed my account on January since I'm not there for quite sometime. I'll try to fix it when I have the time.

[Dennis Ruiter]

Hello @ScienceFriction, could you put your "oradump.py" script in a Gist please ?

[halil]

hi, any chance to get script online ?

[Ehsan Mohammadi]

Data Wizard does not support dump files.

[Bernard Ojengwa]

@ScienceFriction Any chance we can get the script...?! :)

[Buggy]

@ScienceFriction I d like to glance at the script as well.