Tools for Migrating from Oracle to MySQL

Question

I want to migrate schema from Oracle to MySQl, so are there any free tools that would be useful for this task?

I have "Create table" statements in Oracle SQL Script, but it contains unique constraints and a foreign key. MySQL has MyISAM storage engine, and so foreign key is not supported.

How to solve this issue?

Sample Oracle create statements:

   CREATE TABLE channels 
(
  obt_id            NUMBER(19) PRIMARY KEY,
  discriminator     VARCHAR2(64) NOT NULL
                    CONSTRAINT check_channel_discriminator CHECK (discriminator IN ('CHANNEL','SALES_CHANNEL')),
  chan_id           VARCHAR2(255),
  description       VARCHAR2(255),
  name              VARCHAR2(255) NOT NULL,
  obt_version       VARCHAR2(255),
  fk2_channel       NUMBER(19)
                    CONSTRAINT fk_channel_channel REFERENCES channels(obt_id)
);

CREATE TABLE object_types 
(
  obt_id                    NUMBER(19) PRIMARY KEY,
  enum_value                VARCHAR2(64) NOT NULL,
  external_name             VARCHAR2(64) NOT NULL,
  description               VARCHAR2(255),
  business_validation       NUMBER(1) DEFAULT 0,
  start_date_time           DATE DEFAULT to_date('01011900','DDMMYYYY'),
  end_date_time             DATE DEFAULT to_date('01014712','DDMMYYYY'),
  mut_date_time             DATE DEFAULT SYSDATE,
  mut_user                  VARCHAR2(32) DEFAULT USER,
  CONSTRAINT                object_types UNIQUE (external_name,start_date_time,end_date_time)
);

How can I check for Foreign Key constraints, Unique Constraints ? — Rachel
– Rachel, Commented Oct 30, 2009 at 18:34

northpole · Accepted Answer · 2009-10-30 18:11:56Z

2

I have not heard of a single tool that can assist in what you are asking for. This does not mean one does not exist, however, it is probably easier and less error prone to take your existing Oracle scripts and manually create the appropriate MySQL scripts. On every project I have been on the DBAs were responsible for data migration of this type and they always did it manually.

Edit:

That being said I did a quick google search and there are a few programs that claim to do this (at a cost). For example:

Oracle to MySQL

Data loader

DBConvert

I would obviously caution against using a third party tool and make sure you back up everything before starting.

edited Oct 30, 2009 at 18:11

answered Oct 30, 2009 at 18:06

northpole

10.3k7 gold badges38 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rachel Over a year ago

How can I check for Foreign Key constraints, Unique Constraints ?

northpole Over a year ago

that's why I recommend doing it manually. You can manage the relationships yourself. When you modify the scripts you can remove constraints and fkeys as necessary.

txyoji · Accepted Answer · 2009-10-30 18:15:43Z

1

The mysql gui tool kit includes a migration tool. http://dev.mysql.com/downloads/gui-tools/5.0.html

You'll need to have the jdbc driver for Oracle installed on the machine where your running the tool kit.

answered Oct 30, 2009 at 18:15

txyoji

6,8881 gold badge48 silver badges47 bronze badges

Collectives™ on Stack Overflow

Tools for Migrating from Oracle to MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related