When you migrate Oracle database into an open-source DBMS, this comes with a lot of benefits. This is because Oracle is more useful for the construction of complex projects in companies that handle heavy database systems. Here are the key benefits of Oracle to MySQL migration:

• Open-source ability
• Low cost of ownership
• Tight integration with web
• A bigger circle of MySQL administrators

The process of Oracle to MySQLmigration, like other DBMS, follows a number of steps which are:

• Export Oracle table definitions into DDL statements (CREATE TABLE)
• Convert SQL-statements into MySQL format a d load into target server
• Export Oracle data into corresponding CSV files
• According to target database, modify CSV files and import into MySQL database
• Export views, stored procedures, and triggers into SQL statements
• According to MySQL syntax, load statements and code into the target database

The database administrator in charge of Oracle to MySQL migration must be fully aware of the intricacies of the process especially the best method to automate the process of migration. An example of an Oracle client application that is used to test all queries and statements is SQL*Plus. But other client applications can be used as much. The command line for connecting SQL*Plus to database is:

sqlplus username/password@database

Table definitions

First step of migrating table definitions from Oracle to MySQL is extracting them as DDL statements. Run this statement to get full list of Oracle tablesavailable for the current user:

SQL> select table_name from user_tables;

Each Oracle table’s definition is then extracted thus:

SQL> set long 1000

SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,'<TABLE NAME>'[,’SCHEMA’]) from DUAL

Before the resulting script is loaded into MySQL, it must be transformedaccording to the target format as follows:

• Oracle specific keywords at the end of CREATE TABLE statements must be removed (starting from “USING INDEX PCTFREE…”)
• Replace Oracle identifier quote character (“) by MySQL equivalent (`)
• According to a certain table that can be found online, the data types are converted into equivalents of MySQL.

Data

Oracle to MySQL migration of data uses Comma Separated Values (CSV) files as intermediate storage to extract data from the source tables. There is a sequence of command which guides the import of Oracle data into CSV format:

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 LOAD DATA statement is then used to load the CSV files into the MySQL database:

LOAD DATA LOCAL INFILE ‘a_table.csv’

INTO TABLE a_table

FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘

LINES TERMINATED BY ‘\r\n’;

Indexes and constraints

This query is used in this step:

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

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

select VIEW_NAME, TEXT from SYS.USER_VIEWS SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;

Views

Use the following query to get list of all views from Oracle database in form of CREATE VIEW statements:

select VIEW_NAME, TEXT from SYS.USER_VIEWS;

In creating views, both Oracle and MySQL have similar syntax of statements but not physically identical. This is the reason it is always necessary to process the statement before it reaches the destination DBMS.

Since Oracle has several features which can be sued to create views but are unsupported by MySQL, these features should be expelled from the conversion process – default, force/no force, with check option, with object identifier, with read only, under, and XMLType views

Learn more about Oracle to MySQL migration at the official site of Intelligent Converters.