Oracle is very powerful and sophisticated database management system that is used by world’s largest companies for complex projects and corporate scale data warehouses. Nevertheless, database migration from Oracle to MySQL may lead to multiple advantages. Some of the key benefits of the migration include:
- Open-source ability
- Low cost of ownership
- Tight integration with web
- A bigger circle of MySQL administrators
The process of database migration from Oracle to MySQL usually occurred according to extract-transform-load method that includes several steps which are:
- Extract Oracle table definitions as ‘CREATE TABLE’ statements
- Transform those DDL-statements according to MySQL format with respect to the appropriate type mapping and load into target server
- Export Oracle data into temporary storage that consists of comma separate values (CSV) files build for every migrated table
- Modify data in those CSV files according to the destination format and import into MySQL database
- Extract Oracle views, stored procedures, and triggers in form of SQL statements and code
- Convert those statements and code into MySQL format with respect to built-in functions and import it into the destination database
The person who is responsible for database migration must be fully aware of all bottlenecks of the process as well as the best method to automate migration procedure. For example, the standard Oracle client application like SQL*Plus may be used to test all queries and statements. But other client applications can be used as much. SQL*Plus is connected to Oracle database through the following command line:
sqlplus username/password@database
Table definitions
There is need to convert Oracle table definitions into MySQL format after extracting them as DDL statements as:
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 loading the ‘CREATE TABLE’ statement into MySQL, it must be updated according to these rules:
- Remove all keywords that are specific to Oracle and do not have MySQL equivalent from the end of CREATE TABLE statements (starting from “USING INDEX PCTFREE…”)
- Double quote symbols that are used by Oracle to enclose database object names must be replaced by MySQL equivalent – apostrophe (`)
- Each Oracle data type is converted into the most appropriate MySQL equivalent in terms of acceptable values range
Data
The intermediate storage that consists of multiple comma separate values (CSV) files receives data from migration to MySQL from Oracle. Oracle data is imported into CSV format through 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 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 to list index and constraints for particular table in Oracle database:
SQL> select * from all_indexes where table_name = ‘<TABLE NAME>’;
And the statement below extracts the definition of specified Oracle index:
select VIEW_NAME, TEXT from SYS.USER_VIEWS SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;
Views
Oracle views are extracted from the database in form of CREATE VIEW statements via the following query:
select VIEW_NAME, TEXT from SYS.USER_VIEWS;
Syntax of statements to create views is similar for Oracle and MySQL yes is not identical, so it has be to converted according to MySQL format before loading into the target DBMS.
