How to Migrate from MySQL to Oracle

Galaxy Glossary

How do I migrate an existing MySQL database to Oracle without data loss?

Move schema, data, and code from a MySQL database to Oracle safely and efficiently.

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

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

Why migrate from MySQL to Oracle?

Oracle offers advanced scalability, partitioning, and enterprise-grade security. Teams switch when workloads outgrow MySQL’s feature set.

What are the key migration phases?

Successful projects follow four phases: assessment, schema conversion, data transfer, and validation.

How do I assess compatibility quickly?

Run MySQL’s SHOW CREATE TABLE and feed output to SQL Developer Migration Workbench. The tool highlights unsupported types like TINYINT(1) and ENUM.

Which Oracle tools accelerate migration?

Oracle SQL Developer, GoldenGate, and Data Pump automate schema conversion and bulk data loads while tracking errors.

How to create a database link from Oracle to MySQL?

Add MySQL ODBC driver, create a heterogeneous services (HS) init file, then:

CREATE DATABASE LINK mysql_link CONNECT TO "root" IDENTIFIED BY "secret" USING 'MYSQL_PDB';

How to copy ecommerce tables through the link?

Use CREATE TABLE AS SELECT (CTAS) for one-time loads:

CREATE TABLE customers AS SELECT * FROM customers@mysql_link; CREATE TABLE orders AS SELECT * FROM orders@mysql_link;

How to convert data types automatically?

In SQL Developer, open the Migration wizard → Capture MySQL Source → Convert Model → Generate Oracle Output.

How to migrate large datasets efficiently?

Export MySQL tables with mysqldump --tab to CSV, then load into Oracle with SQL*Loader using parallel workers.

How to preserve auto-increment behavior?

Replace MySQL’s AUTO_INCREMENT with Oracle sequences and triggers:

CREATE SEQUENCE customers_seq START WITH 1001; CREATE OR REPLACE TRIGGER customers_bir BEFORE INSERT ON customers FOR EACH ROW WHEN (NEW.id IS NULL) BEGIN SELECT customers_seq.NEXTVAL INTO :NEW.id FROM dual; END; /

How to validate migrated data?

Run row counts and checksum queries on both systems. Oracle example:

SELECT COUNT(*) FROM customers; SELECT STANDARD_HASH(LISTAGG(id||name||email, ',') WITHIN GROUP (ORDER BY id)) hash FROM customers;

What performance tweaks are required post-migration?

Create indexes, gather statistics with DBMS_STATS.GATHER_SCHEMA_STATS, and enable Advanced Compression if licensed.

Why How to Migrate from MySQL to Oracle is important

How to Migrate from MySQL to Oracle Example Usage


-- Move OrderItems in batches to avoid undo exhaustion
BEGIN
  FOR r IN (SELECT /*+ PARALLEL(4) */ * FROM OrderItems@mysql_link WHERE order_id BETWEEN 1 AND 10000) LOOP
    INSERT /*+ APPEND */ INTO OrderItems VALUES r.*;
    COMMIT;
  END LOOP;
END;
/

How to Migrate from MySQL to Oracle Syntax


-- 1. Create database link to MySQL via Oracle Heterogeneous Services
CREATE DATABASE LINK mysql_link
  CONNECT TO "<mysql_user>" IDENTIFIED BY "<mysql_pwd>"
  USING '<tns_alias>';

-- 2. Copy table structure & data (one-off)
CREATE TABLE Customers AS
SELECT id,
       name,
       email,
       created_at
FROM   Customers@mysql_link;

-- 3. Continuous replication with GoldenGate (simplified)
ADD SCHEMATRANDATA mysql.*;
ADD TABLE mysql.Customers;
ADD MAP mysql.Customers, TARGET oracle.Customers;

-- 4. Replace AUTO_INCREMENT
CREATE SEQUENCE customers_seq START WITH 1 NOCACHE;
CREATE OR REPLACE TRIGGER customers_bir
BEFORE INSERT ON Customers
FOR EACH ROW WHEN (NEW.id IS NULL)
BEGIN
  SELECT customers_seq.NEXTVAL INTO :NEW.id FROM dual;
END;
/

-- 5. Rebuild foreign keys
ALTER TABLE Orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES Customers(id);

Common Mistakes

Relying solely on mysqldump and direct imports. This ignores data-type differences (e.g., VARCHAR vs. VARCHAR2) and breaks AUTO_INCREMENT. Use SQL Developer’s converter and create sequences.
Skipping constraint recreation. Foreign keys and unique constraints aren’t auto-copied by CTAS. Capture them during conversion or reapply with ALTER TABLE after data load.

Frequently Asked Questions (FAQs)

Can I keep MySQL online during migration?

Yes. Use Oracle GoldenGate to replicate changes in real time while initial bulk loads complete.

How long does schema conversion take?

Small schemas convert in minutes with SQL Developer. Complex schemas with hundreds of tables may take a few hours including manual fixes.

Is downtime required?

With GoldenGate or Dbvisit Replicate, cutover downtime can be reduced to seconds. Without replication, expect downtime equal to time for final incremental dump and restore.

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.