export from Oracle into SQL Server or mySQL

Question

Hi,

Is there a standard or recommended method of exporting the data from an Oracle DB into a SQL Server or mySQL database? Was thinking exporting the Oracle data into XML then importing the XML into SQL Server...or this recommmened?

Thanks,

Answer 1

If you were moving house from the USA to Canada would you go via Japan?

While the differences between Oracle and other SQL rDBMS are even more pronounced that the differences between other SQL rDBMS, the shortest route seems to be from one database to database. I'd go with exporting the schema in SQL DDL to a text file so it can be manually tinkered with, then use a program to move the actual data across - e.g. using odbc links. I reckon the latter could be written in about 100 lines of code assuming that its not available off the shelf.

Alternatively, since you don't seem to have decided on a DBMS yet, you might want to have a look at Enterprise DB (based on PostgreSQL) which comes with tools for migrating from Oracle.

C.

Answer 2

My advice:

1. always make an export so you can re-import (with IMP) into oracle again. EXP documentation: http://wiki.oracle.com/page/Oracle+export+and+import+
2. use a tool like Data Architect to migrate the schema (http://www.sqlpower.ca/page/architect). This tool allows you to import the structure of the Oracle DB, and then make a comparison with a target schema. It can then genereate and/or execute a script to reconcile structural changes. Although this will help you define the table DDL and indexes (and unique constraints and primary key constraints) you will most certainly need to manually modify the target model. Choosing the right data type is always something you need to consider - i disrecommend automated data type mapping. Foreign keys can be a challenge too when going to MySQL. For that particular case you need to ensure you are moving the data to InnoDB tables.
3. use a tool like kettle aka pentaho data integration (http://kettle.pentaho.org/ download here http://sourceforge.net/projects/pentaho/files/) to actually move the data. kettle includes a wizard to copy all table contents. I believe data architect can do migration too, but I prefer using kettle

These EXP/IMP tools are part of oracle. They are in the bin dir of your oracle server install. The other tools are FOSS - (free software)

Another tool that might help you is Oracle's own SQL developer