How to Migrate from Oracle to MySQL

Galaxy Glossary

How do I migrate an Oracle database to MySQL?

Step-by-step process to convert Oracle schemas, data, and code to MySQL with minimal downtime.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why move from Oracle to MySQL?

Lower licensing costs, open-source flexibility, and easier cloud hosting often push teams to migrate from Oracle to MySQL.

What should I audit before starting?

List object counts (tables, views, procedures), Oracle-specific data types (NUMBER, DATE, CLOB), and PL/SQL code you rely on. Assess size and allowable downtime.

Which tools speed up migration?

Oracle SQL Developer (free), MySQL Workbench Migration Wizard, and AWS SCT handle schema conversion and data copy.For simple loads, use CSV + LOAD DATA INFILE.

How do I convert the schema?

Export DDL from Oracle with DBMS_METADATA, feed it into MySQL Workbench, and let the wizard map data types.Manually tweak outliers like NUMBER(*,*) to DECIMAL or AUTO_INCREMENT.

How do I migrate data reliably?

For each table, export to CSV: SET COLSEP ','; SPOOL customers.csv; SELECT * FROM customers; SPOOL OFF; then bulk-load into MySQL: LOAD DATA INFILE 'customers.csv' INTO TABLE Customers FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

How can I keep systems in sync during cut-over?

Enable Oracle GoldenGate or AWS DMS for change data capture.Replicate until your MySQL replica is current, then switch application connections.

How do I validate the migration?

Run row counts, checksums, and business queries in both systems. Example: SELECT COUNT(*) FROM Customers WHERE created_at >= '2024-01-01';.

Best practices for a smooth migration?

Iterate: schema first, small data slice, full data, code port, dual-write, final cut-over.Automate conversions with scripts and keep rollback plans.

What common pitfalls should I avoid?

Ignoring case-sensitive identifiers, forgetting to convert sequences to AUTO_INCREMENT, and assuming PL/SQL packages will run unchanged in MySQL.

.

Why How to Migrate from Oracle to MySQL is important

How to Migrate from Oracle to MySQL Example Usage


-- Verify Orders joined with OrderItems after migration
SELECT o.id, c.name, SUM(oi.quantity * p.price) AS order_total
FROM Orders o
JOIN Customers c ON c.id = o.customer_id
JOIN OrderItems oi ON oi.order_id = o.id
JOIN Products p ON p.id = oi.product_id
GROUP BY o.id, c.name
ORDER BY order_total DESC
LIMIT 10;

How to Migrate from Oracle to MySQL Syntax


-- 1. Extract DDL from Oracle
SELECT DBMS_METADATA.GET_DDL('TABLE','CUSTOMERS') FROM DUAL;

-- 2. Example converted MySQL DDL
CREATE TABLE Customers (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 3. Bulk import data into MySQL
LOAD DATA INFILE '/path/customers.csv'
INTO TABLE Customers
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(id, name, email, @created_at)
SET created_at = STR_TO_DATE(@created_at,'%d-%b-%Y %H:%i:%s');

-- 4. Post-migration verification
SELECT COUNT(*) AS rows_migrated FROM Customers;

Common Mistakes

Assuming Oracle sequences map automatically—MySQL uses AUTO_INCREMENT. Create AUTO_INCREMENT columns or separate sequence tables to preserve IDs.
Leaving DATE fields as VARCHAR. This breaks sorting and functions. Use STR_TO_DATE during LOAD DATA or ALTER TABLE to convert to proper DATETIME.

Frequently Asked Questions (FAQs)

Can I migrate PL/SQL procedures?

PL/SQL isn’t compatible with MySQL. Rewrite logic in MySQL stored procedures or move it into application code.

What about triggers and constraints?

Workbench converts basic triggers and foreign keys, but complex triggers with Oracle-specific syntax need manual rewrites.

How long does a typical migration take?

Small databases (<10 GB) migrate in hours; larger systems depend on data volume, network speed, and validation time. Plan buffer time for fixes.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie