Home > Documentation > Oracle to MySQL

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 PostgreSQL consists of the following steps:

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:

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;