Migrate Oracle to MySQL

[Oracle to MySQL Converter] [Types Mapping] [Converting Queries]

Do I Need to Migrate to MySQL?

Migrating from Oracle to MySQL server brings multiple benefits: low cost of ownership, open source database engine, tight integration with web, wide circle of MySQL database professionals and much more. However, if someone is constructing a complex and large scale corporate database, it is reasonable to stay with Oracle since the DBMS provides ultimate range of features and tools for this purpose.

You can learn about the most important differences between Oracle and MySQL from this article.

Oracle to MySQL Migration

The process of converting Oracle database to MySQL consists of the following steps:

Oracle table definitions are exported into "CREATE TABLE" statements, those SQL-statements are made compatible with MySQL format and loaded to MySQL database.
Oracle data is exported into CSV files, those CSV files are modified according to the target format (if it is necessary) and imported into MySQL database.
Oracle indexes and constraints are extracted in form of CREATE-statements. Those statements are converted according to MySQL syntax and imported into the target database.
Since MySQL does not support sequences, Oracle sequences should be emulated in the destination database according to this guide.
Oracle views, triggers, stored procedures and functions are exported into SQL statements and plain text source code. These statements and code are transformed according to MySQL syntax and loaded to the target database.

Whether all these steps are implemented manually or commercial tool is being used to automate the entire migration process, the person in charge has to know all possible bottlenecks and methods of validating the results. All queries and statements has been tested from SQL*Plus, but any other Oracle client application can be used as well. This is the command line to connect to the database via SQL*Plus:

sqlplus username/password@database

Table Definitions

Oracle table definitions are extracted in form of DDL statements that must be converted into MySQL format then. This is how to list all tables in Oracle database:

SQL> select table_name from user_tables;

Then definition of each Oracle table can be extracted as follows:

SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL('TABLE','<TABLE NAME>'[,'SCHEMA']) from DUAL

The resulting script must be corrected before loading to MySQL as follows:

remove Oracle specific keywords at the end of CREATE TABLE statements (starting from "USING INDEX PCTFREE...")
replace Oracle identifier quote character (") by MySQL equivalent (`)
convert all data types into MySQL equivalents according to this table

Data

Data can be migrated from Oracle to MySQL via an intermediate storage like comma separated values (CSV) files. Oracle data can be imported into CSV format via the following sequence of commands:

SQL> set heading off
SQL> spool filename.csv
SQL> select column1 || ',' || column2 || ... from mytable;
SQL> set colsep ','
SQL> select * from my_table;
SQL> spool off;

The resulting CSV files can be loaded into MySQL database from any client application via LOAD DATA statement:

LOAD DATA LOCAL INFILE 'a_table.csv'
INTO TABLE a_table
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

Indexes and Constraints

List of all indexes that belong to table "mytable" cab be extracted using this query:

SQL> select * from all_indexes where table_name = '<TABLE NAME>';

And these statements allow to get the definition of particular index:

SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL('INDEX','<INDEX NAME>') from DUAL;

Have more questions? Contact us