Oracle to MySQL

Description

This page basically describes my Oracle to MySQL conversion process.  I took about a handful of tables from my Oracle 8.0.5, and inserted into an Ubuntu 5.04 that I have running MySQL 4.1.

Process/Implementation

While I'm a Unix/Linux bigot, and usually try to do all my efforts from there, occasionally there are times when I have no choice but to use windows.  This is not one of those times, but sure is a hell-uv-a lot easier to do so.  MySQL makes a migration toolkit, that can log into Oracle for you, login to MySQL for you, and just migrate schema's over.  You can selectively choose which tables, views, etc - that you want to migrate.  For me, in this mini database, I needed all, so thats what I chose. 

So the first thing you need is the ability to connect to Oracle.   I had assumed that having the oracle client was enough, but its not - there is a need for the JDBC drivers - version 14 actually.   The file is called ojdbc14.jar - and I have that in the same directory on my pc as the mysql-gui-tools-5.0r12-win32.msi package (obtained from link above).  So I loaded the mysql gui tools, and in the first step where it asks for location of the oracle jdbc info, I point it to that directory.  Then it requires a restart of the gui app - no worries, and after that, it can connect to your oracle instance (assuming you have the connect string paramets - SID, host, port, username/password).  Then the connection to mysql is easy - just host, port, and username/password.   After that, its all wizard driven.

I used these options for my conversion though - I mention it because I wanted to use the faster (non foreign-key constraint, non transactional, non rollback"able") MySQL native table format, and not the innodb format :

Its very straightforward once the tools are installed.  I chose not to use innodb tables, so I went with basic MySQL statistical table design.  These take up the least amount of space, and are the fastest, performance wise.  They do NOT have referential integrity, rollback ability, or transaction style inputs - no row level locking.  So there is definitely a trade off - but since Im a very small user population that leverage these tables in update fashion, this was a simple choice for me.