Oracle has long been known for its powerful database management system that can handle any scale of corporate data warehousing. However, the high price of its licenses has forced many organizations to seek out less expensive or free alternatives. Migrating from Oracle to MySQL can offer several benefits, such as lower cost of ownership, access to an open-source database engine, and a large community of MySQL professionals. However, it is crucial to consider the complexity and scale of the database being constructed before deciding on which platform to use.

One of the most significant advantage of MySQL over Oracle is the price of licenses. Oracle is the most expensive database management system, while MySQL is an open-source engine with wide range of licenses available on free basic. This means that companies can save a considerable amount of money by switching to MySQL. Additionally, MySQL and its forks such as MariaDB and Percona provide excellent integration with web, that makes DBMS an attractive choice for web developers.

Another important advantage of migrating to MySQL is the large community of database professionals. Due to open-source availability of MySQL, a lot of professionals having deep expertise in the technology can provide support when needed. This is not the case with Oracle, which can be more difficult and expensive to find experienced professionals in database administration or development fields.

On the other hand, if someone is constructing a complex corporate-scale database, Oracle may still be the better option due to the ultimate functionality and flexibility.

The process of migrating from Oracle to MySQL, MariaDB or Percona involves several steps. First, all the table structures are exported into data definition language (DDL) statements, which are then made compatible with MySQL format and imported into the target database:

SQL> select table_name from user_tables;

SQL> set long 1000

SQL> set pagesize 0

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

Next step is to export all data into intermediate CSV warehouse, which is updated to comply with MySQL syntax and loaded into the database.

Oracle primary keys, indexes and relationships between tables are extracted as CREATE-statements. Just as for DDL statement above, these entries are migrated into MySQL format and imported into the target database:

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

SQL> set long 1000

SQL> set pagesize 0

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

Additionally, because MySQL does not support sequences, Oracle sequences must be emulated by AUTO_INCREMENT property of the corresponding column when it is suitable or by using temporary tables for more complicated requirements.

Oracle views, triggers, stored procedures, and functions are extracted as SQL statements and the source code. These statements and code must be rewritten according to MySQL dialect of SQL and loaded into the target database. This step required deep programming knowledge in both Oracle and MySQL dialects of SQL.

While it is possible to migrate from Oracle to MySQL manually, this approach can be time-consuming and prone to errors. To prevent issues such as data loss or corruption, it is recommended to use specialized software tools that automate the Oracle to MySQL database migration process. One example of such a solution is the Oracle to MySQL converter provided by Intelligent Converters, a company with over 20 years of experience in database migration and synchronization.

Key features of Oracle to MySQL converter:

• Support for all modern versions of Oralce and MySQL including forks (MariaDB, Percona) and cloud variations (Amazon RDS, Azure for MySQL, Google cloud, etc)
• Schemas, data, primary keys, indexes and relations between tables are migrated with respect to differences between Oracle and MySQL and all required properties
• Data to migrate can be pre-processed (filtering, merging multiple tables in one, etc) via SELECT-queries
• Command line support allows to schedule and script Oracle to MySQL database migration

To summarize, transitioning from Oracle to MySQL can provide a number of benefits, such as a reduced cost of ownership and access to an open-source database engine. However, it is important to carefully consider the complexity and size of the database being created, as Oracle may still be the superior choice for large corporations that require advanced tools and functionality. Additionally, utilizing specialized software tools to streamline the migration process is recommended to prevent data loss or corruption.