How to Migrate from Oracle to MariaDB

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

Transfer Oracle schemas, data, and code to MariaDB using export, conversion, and import tools.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why choose MariaDB over Oracle?

MariaDB is open-source, license-friendly, and cloud-ready, making it a budget-savvy replacement for Oracle while keeping high availability and performance.

What are the high-level migration steps?

1) Export Oracle metadata & data. 2) Convert Oracle objects to MariaDB-compatible SQL. 3) Create target schema in MariaDB. 4) Load data. 5) Refactor application code. 6) Validate & optimize.

How do I export Oracle objects?

Use Data Pump: expdp user/password DIRECTORY=expdir SCHEMAS=ECOMMERCE DUMPFILE=ecom.dmp LOGFILE=ecom.log. This captures tables, indexes, constraints, and data.

How do I convert Oracle DDL to MariaDB?

Run ora2mariadb or MySQL Workbench Migration. They transform data types (NUMBER→DECIMAL, VARCHAR2→VARCHAR) and translate sequences to AUTO_INCREMENT.

How do I load the converted dump?

Copy the generated .sql file to the MariaDB host, then execute mysql -u root -p ecommerce < ecom_converted.sql. Use SET FOREIGN_KEY_CHECKS=0 during bulk load for speed.

What is the exact syntax for key commands?

See the dedicated Syntax section below for expdp, ora2mariadb, and mysql import commands.

Example migration for an ecommerce database

The Example Query section shows exporting Customers, converting, and loading into MariaDB in a single script.

Best practices for a smooth migration

Enable binlog format = ROW in MariaDB for future CDC, migrate non-nullable columns first, and validate row counts with CHECKSUM TABLE.

Common mistakes and fixes

See the Common Mistake section for details on understated data types and unconverted PL/SQL.

FAQ

Can I migrate Oracle sequences to MariaDB?

Yes. Map each sequence to an AUTO_INCREMENT column or create a separate table that simulates sequence behavior.

Does MariaDB support Oracle packages?

No. Rewrite PL/SQL packages as stored procedures or move logic to the application layer.

How long does a 100 GB database migration take?

With parallel Data Pump (PARALLEL=4) and fast-load, expect 30-60 min export, 10-15 min convert, and 30-45 min import, subject to I/O.

Why How to Migrate from Oracle to MariaDB is important

How to Migrate from Oracle to MariaDB Example Usage


-- 1. Export only ecommerce tables from Oracle
expdp ecommerce/password TABLES=Customers,Orders,Products,OrderItems \
      DIRECTORY=expdir DUMPFILE=ecom_tables.dmp PARALLEL=4

-- 2. Convert dump to MariaDB SQL
ora2mariadb --source-dump ecom_tables.dmp --target-sql ecom.sql

-- 3. Load into MariaDB and verify row counts
mysql -u root -p ecommerce < ecom.sql
SELECT COUNT(*) FROM Customers;
SELECT COUNT(*) FROM Orders;

How to Migrate from Oracle to MariaDB Syntax


-- 1. Export Oracle schema and data
expdp ecommerce/password DIRECTORY=expdir \
      SCHEMAS=ECOMMERCE PARALLEL=4 \
      DUMPFILE=ecom.dmp LOGFILE=ecom.log

-- 2. Convert Oracle dump to MariaDB-compatible SQL
ora2mariadb \
  --source-dump ecom.dmp \
  --target-sql  ecom_converted.sql \
  --map-number-as decimal(10,2) \
  --map-date-as  datetime

-- 3. Create target database in MariaDB
CREATE DATABASE ecommerce CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 4. Import into MariaDB
mysql -u root -p ecommerce < ecom_converted.sql

-- 5. Post-import index rebuild (optional)
ALTER TABLE Orders ADD INDEX idx_customer_id (customer_id);

-- Sample converted DDL (Orders table)
CREATE TABLE Orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME,
    total_amount DECIMAL(10,2),
    INDEX idx_customer_id (customer_id)
);

Common Mistakes

Using NUMBER(38) columns as INT in MariaDB. Fix: map to DECIMAL or BIGINT depending on actual range to avoid overflow.
Ignoring PL/SQL features like packages and triggers. Fix: inventory all PL/SQL objects and rewrite as MariaDB stored procedures or application logic.