Migrating database from Oracle to MySQL has several advantages, including lower ownership costs, a free database engine, tight integration with the web, and a vast community of MySQL database professionals. However, if someone is setting up a large-scale corporate database, it may be reasonable to stick with Oracle as it provides the ultimate range of features and tools for this specific purpose. This article outlines the most significant differences between Oracle and MySQL.
The process of converting an Oracle database to MySQL involves several steps. First, Oracle table definitions are exported as “CREATE TABLE” statements, which are then transformed into MySQL format and loaded into the target server. Next, Oracle data is exported into CSV files, which are modified as necessary to match the target format and then imported into MySQL. Finally, Oracle views, triggers, stored procedures, and operations are exported as SQL statements and plain text source code, which are then converted into MySQL syntax and loaded into the target database.
Whether these steps are carried out manually or using commercial tools, the individual in charge must understand all possible bottlenecks and techniques for validating the results. Queries and statements can be tested using SQLPlus or any other Oracle client application. To connect to the database via SQLPlus, use the following command line:
sqlplus username/password@database
To extract the definition of all tables in an Oracle database, use the following query:
SQL> select table_name from user_tables;
To extract the definition of a specific Oracle table, use the following commands:
SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,'<TABLE NAME>'[,’SCHEMA’]) from DUAL
Before loading the resulting script into MySQL, some modifications are required, such as removing Oracle-specific keywords at the end of CREATE TABLE statements, replacing Oracle identifier quote characters with MySQL equivalents, and converting all data types into their MySQL equivalents.
Oracle data has to be migrated into MySQL through comma-separated values (CSV) files as an intermediate storage. Oracle data can be imported into CSV format using the following 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;
Then MySQL will be able to import data from those CSV files using LOAD DATA statement run from any client tool:
LOAD DATA LOCAL INFILE ‘a_table.csv’
INTO TABLE a_table
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\r\n’;
To extract the list of all indexes belonging to a table named “mytable”, use the following query:
SQL> select * from all_indexes where table_name = ‘<TABLE NAME>’;
And to get the definition of a particular index, use the appropriate statement:
SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;
If you find all the specified steps too complicated to run it manually, you can use special software to automate and simplify Oracle to MySQL database migration with just a few clicks of mouse button. One of such tools is Oracle to MySQL converter developed by Intelligent Converters software company.
