Do I Need to Migrate to MySQL?
Database migration from Oracle to MySQL has a lot of advantages: lower cost of ownership, switch to open-source system, easy integration with web and much more. On the other hand, if the database is huge, complicated and contains a lot of business logic in form of stored functions, procedures and triggers, it is reasonable to stay with Oracle since this DBMS provides ultimate range of powerful features and tools that may be missed in MySQL.
Oracle to MySQL Migration
The process of migrating Oracle database to MySQL is usually implemented according to extract-transform-load approach through the steps below:
- Definitions of Oracle tables are extracted in form of “CREATE TABLE” statements
- Those statements are made compatible with MySQL format and loaded to the target database
- Oracle data is exported into SQL insert-statements
- Those SQL statements are modified according to the target format and imported into MySQL database
- Oracle views, triggers, stored procedures and functions are exported into SQL statements and source code in plain text format
- All of these materials are transformed according to MySQL syntax and loaded to the target database
It does not matter if all these steps are running manually or automated via migration tool, the person in charge should know how to validate the results.
Oracle vs MySQL
Schemas. Oracle server has the single database with multiple schemas, while in MySQL schema is synonymous with a database.
Transactions. By default, MySQL has auto-commit parameter ON which means that you have to explicitly handle transactional statements to take advantage of ROLLBACK for ignoring changes or taking advantage of using SAVEPOINT.
Synonyms. Unlike Oracle, MySQL does not support SYNONYM. The workaround is to convert all synonyms into MySQL views.
Empty String and NULL. In Oracle empty strings and NULL are equal, MySQL distinguishes those cases.
Table Definitions
Oracle allows to explore table definitions in form of CREATE TABLE statements that should be translated according to MySQL syntax then. First step is to list all Oracle tables in the database:
SQL> select table_name from user_tables;
Next step is to extract definition of every Oracle table using the query 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 pre-processed before loading to MySQL:
- remove Oracle specific keywords from CREATE TABLE statements starting from pattern “USING INDEX PCTFREE…”
- replace Oracle identifier quote character (“) by MySQL equivalent (`)
- convert all data types into MySQL equivalents
Data
One of the simplest methods of exporting data from Oracle is Oracle SQL Developer that allows to export into SQL insert statements. Those statements require minor pre-processing before loading to MySQL:
- remove Oracle special keywords like REM and SET
- expressions TO_DATE(‘$date_string’, ‘YYYY-MM-DD HH24:MI:SS’) are replaced by date string representation ‘$date_string’
The resulting SQL script can be imported to MySQL server directly.
Indexes and Constraints
Oracle can extract all indexes that belong to table “mytable” through the following query:
SQL> select * from all_indexes where table_name = ‘<TABLE NAME>’;
To get definition of the index by name, run this query:
select VIEW_NAME, TEXT from SYS.USER_VIEWS SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;
