Mastering Oracle‑to‑MySQL Migration: Key Differences, Risks, and Proven Strategies

Migration Types

Oracle‑to‑MySQL migration can be classified into three technical categories:

Data‑structure migration : Adjust Oracle data types, virtual columns, indexes, and partitioning to MySQL equivalents.

Data migration : Export data (CSV, SQL inserts, or CDC streams) and load it into MySQL, handling LOBs, NULL values, and empty strings.

Business‑logic migration : Rewrite stored procedures, functions, triggers, sequences, materialized views, and parallel execution constructs that MySQL does not support.

Migration Process

Define migration scope – List the objects (tables, views, procedures) and the historical data that truly need to be moved. Exclude temporary tables, old backups, or rarely used objects to reduce risk.

Migration assessment – Analyse the source schema and application code to identify incompatibilities (data‑type mismatches, missing features, partitioning differences) and estimate effort.

Select migration method – Choose between a one‑time bulk load or a real‑time change‑data‑capture (CDC) replication based on downtime tolerance and data‑volume.

Verification testing – After loading, compare row counts, aggregates (SUM, AVG, MIN, MAX) and run representative queries against both databases to ensure functional parity.

Scope Definition

Avoid migrating unnecessary tables, staging data, or archived partitions. Focus on the current production schema and the data required for business continuity.

Assessment

Typical incompatibilities include:

Oracle parallel execution and materialized views have no direct MySQL counterpart.

Function‑based indexes, advanced partitioning, and global temporary tables need redesign.

Sequences must be mapped to AUTO_INCREMENT or handled in application logic.

Migration Methods

One‑time load : Export Oracle tables to CSV or SQL INSERT files using tools such as SQL Developer, SQL*Plus, or UTL_FILE. Load the files into MySQL with LOAD DATA INFILE or the parallel mysqlsh util import-table utility. This method is inexpensive but requires a maintenance window.

Real‑time replication : Use CDC products (e.g., Oracle GoldenGate, Striim, Informatica) to capture DML on Oracle and apply it to MySQL. Suitable for production migrations where downtime must be minimal.

Guiding Principles

Align character sets between source and target (e.g., Oracle AL32UTF8 ↔ MySQL utf8mb4).

Temporarily disable foreign‑key checks ( SET FOREIGN_KEY_CHECKS=0) and drop non‑essential indexes before bulk load.

Turn off AUTO_INCREMENT on primary‑key columns during the initial load, then re‑enable it.

Verify network connectivity and latency for CDC tools before starting replication.

Key Differences and Pitfalls

Data Types & Generated Columns

Oracle virtual columns are computed and not stored; MySQL generated columns must declare an explicit data type and use the GENERATED ALWAYS AS clause.

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_type VARCHAR(100) NOT NULL,
  product_price NUMERIC(6,2) NOT NULL,
  price_with_tax NUMERIC(6,2) GENERATED ALWAYS AS (ROUND(product_price * 1.01, 2))
);

Index Types

MySQL supports B‑tree, hash, and full‑text indexes, while Oracle provides bitmap, function‑based, and domain indexes. Map Oracle indexes to the closest MySQL type or redesign the query.

Partitioning

Oracle’s range, list, and hash partitioning have no direct MySQL equivalent; MySQL 8.0 supports only RANGE, LIST, and HASH partitioning on InnoDB tables, and the CSV storage engine cannot be partitioned.

Temporary Tables

Oracle distinguishes global and session temporary tables; MySQL temporary tables exist only for the duration of the session and are automatically dropped at disconnect.

UNUSED Columns

Oracle’s UNUSED column flag has no MySQL counterpart. To remove large columns efficiently, recreate the table without the column and rename it.

Character Sets

Oracle’s character set is defined at database creation (e.g., AL32UTF8) and is difficult to change. MySQL allows per‑database, per‑table, and per‑column character sets. Verify and align character_set_client, character_set_connection, and character_set_results settings before import.

NULL vs. Empty String

Oracle may export NULLs as empty strings, which MySQL interprets as ''. Use \N to represent NULL in LOAD DATA files or preprocess the export to replace NULLs with a sentinel value.

Date & Time Formats

Set Oracle’s NLS_DATE_FORMAT to a MySQL‑compatible pattern (e.g., 'YYYY-MM-DD HH24:MI:SS') before exporting date columns.

LOB Migration

Export CLOB data with UTL_FILE to CSV, then import into MySQL TEXT or BLOB columns. BLOBs can be streamed directly using the MySQL

LOAD DATA

LOCAL

option.

Case Sensitivity

Oracle identifiers are case‑insensitive by default; MySQL identifiers are case‑sensitive on Unix platforms. Adjust lower_case_table_names and use appropriate collations to achieve consistent behavior.

External Tables

MySQL’s CSV storage engine can emulate Oracle external tables but does not support indexes or partitioning. Create a CSV table, load data, then FLUSH TABLE to make the external file visible.

SQL Mode

MySQL 8.0 defaults to a strict sql_mode. Temporarily disable strict mode (e.g., SET GLOBAL sql_mode='') during bulk import, then re‑enable it after the load.

Performance Considerations

Export Phase

SQL Developer export on Windows may hang for very large tables; consider using SQL*Plus with parallel spool or UTL_FILE for smaller tables.

Parallelise SQL*Plus spooling with the PARALLEL hint to speed up extraction.

Import Phase

Tune InnoDB for bulk loading:

innodb_flush_log_at_trx_commit=0

sync_binlog=0

max_allowed_packet

increased (e.g., 64M) to accommodate large rows. innodb_log_file_size, innodb_log_buffer_size, innodb_buffer_pool_size, and innodb_buffer_pool_instances set according to data volume.

Post‑Migration Validation

Validate by:

Comparing row counts and aggregate values (SUM, AVG, MIN, MAX) between Oracle and MySQL tables.

Running a suite of representative queries and checking result sets for equality.

Performing application‑level tests to ensure business logic produces identical outcomes.

Migration Summary

Thoroughly understand structural and business‑logic differences before starting the migration.

Select the migration method (one‑time bulk load vs. CDC) after proof‑of‑concept testing.

Prefer mysqlsh util import-table for parallel CSV import when handling large data sets.

Execute comprehensive data‑integrity checks (row counts, aggregates, query results) after migration to confirm accuracy.