Oracle to MariaDB Migration:
Step-by-Step Guide

Why companies migrate from Oracle to MariaDB

Cost is the most common driver. Oracle's licensing model scales with cores, options, and support tiers, and many teams outgrow that budget faster than they outgrow the database itself. MariaDB, an open-source engine, removes per-core licensing fees and lowers long-term total cost of ownership.

Beyond cost, teams look for flexibility. Oracle bundles many capabilities into paid options, such as partitioning and advanced tuning packs, that MariaDB includes by default or handles through simpler configuration, making it easier to scale infrastructure without renegotiating licenses.

Modernization matters too. Organizations moving workloads to containers, cloud-native platforms, or CI/CD-driven deployments often find MariaDB's lightweight footprint and open tooling ecosystem a better fit than Oracle's enterprise-oriented stack, while also reducing dependence on a single vendor's roadmap and pricing changes.

What changes when you move from Oracle to MariaDB

Moving from Oracle to MariaDB means re-evaluating almost every layer of the schema and codebase, not just the data. Object definitions, identifiers, data types, built-in functions, and PL/SQL constructs all behave differently between the two systems.

Some elements convert directly: basic tables, primary keys, and simple column types usually map with minor syntax edits. Others need remapping—such as Oracle-specific data types, sequence-based ID generation, and many built-in functions have no one-to-one MariaDB equivalent. A smaller set needs redesign: packages, hierarchical queries built with CONNECT BY, and Oracle-only features such as synonyms or materialized views require rethinking the logic rather than a straight syntax swap.

Identifiers are worth flagging early too: Oracle's default uppercase-folding behavior for unquoted identifiers differs from MariaDB's case-sensitivity rules on some platforms, which can quietly break scripts that assume Oracle's naming behavior.

Pre-migration assessment: what to check before you migrate

Assessment is the planning checkpoint before execution: inventory what exists in Oracle before you touch production data. Teams that skip this step typically discover incompatible syntax and missing objects mid-migration, when fixing them is far more expensive than catching them upfront.

Inventory your Oracle schema and codebase

List every table, view, index, constraint, sequence, trigger, procedure, package, and synonym in the schema before planning begins. Export the DDL and keep it as the baseline reference for the entire project.

Don't stop at schema objects—inventory application code and reports that query the database directly. Hardcoded Oracle functions, hints, or hierarchical queries in application SQL matter as much as anything inside the database. Also record row counts and which tables see the heaviest traffic, since this shapes later decisions about transfer method and validation depth.

Run a schema compatibility assessment

Run your Oracle DDL through a compatibility assessment before writing conversion scripts by hand. Assessment tools flag data types without direct MariaDB equivalents, PL/SQL constructs that won't parse under MariaDB's Oracle mode, and object dependencies that affect creation order.

The output should be a report, not just a pass/fail result—which objects convert automatically, which need edits, and which need a rewrite. This report becomes your migration backlog and lets you estimate effort before committing to a timeline, catching edge cases while they're still cheap to fix.

Decide what can be converted automatically and what needs manual work

Not every object deserves the same effort. Tables, straightforward views, and basic constraints usually convert automatically with a mapping tool, so reserve manual work for what actually needs it.

Procedures, packages, and triggers with Oracle-specific PL/SQL—cursors, exception handling, dynamic SQL—typically need manual rewriting, even with a compatibility mode in place. Hierarchical queries, synonyms, and materialized views almost always require redesign rather than translation, so budget dedicated developer time for procedural logic rather than forcing an automatic conversion.

The following table provides a high-level estimate of the migration effort required for common Oracle database objects. Actual complexity depends on the object's implementation and its use of Oracle-specific syntax.

Automatic vs. manual conversion

Should you migrate Oracle to MariaDB?

The right answer depends on how much of your Oracle environment relies on Oracle-specific features versus straightforward schema and data. Use the matrix below as a quick sanity check before committing to a full migration project.

Data type and SQL syntax differences to address early

Data type mapping and function replacement affect both DDL and runtime SQL, so handle them before, not during, data transfer. Getting this wrong means failed inserts, silent truncation, or subtly wrong query results after cutover. The three areas covered below cause the most migration issues.

Oracle-to-MariaDB data type mapping

Data type mapping should be completed before schema creation and data transfer. Some Oracle types map directly to MariaDB, while others require decisions about precision, storage, character encoding, or time zone handling.

Built-in function differences

Oracle and MariaDB often provide equivalent functionality under different function names or with different argument and return-value behavior. Review every replacement in its actual query context rather than applying a global text substitution.

Oracle-to-MariaDB function mapping

Build and maintain a project-specific mapping list because the correct replacement often depends on data types, null behavior, format masks, and query context.

Oracle features without direct MariaDB equivalents

Some Oracle features have no direct MariaDB equivalent. These features should be identified during assessment and treated as redesign tasks rather than simple syntax conversions.

Oracle features that require redesign

How MariaDB Oracle compatibility mode can reduce rewrite work

MariaDB reduces rewrite work through SQL_MODE=ORACLE, a compatibility mode that adapts SQL parsing and PL/SQL support to behave closer to Oracle. Enabling it allows many Oracle PL/SQL constructs to be migrated with fewer changes and reduces the amount of manual rewriting required.

Under this mode, MariaDB supports Oracle-style PL/SQL blocks, packages, sequences, and several Oracle-specific functions that don't exist under the default SQL mode. This meaningfully narrows the gap for procedural code, usually the most time-consuming part of a migration.

Compatibility mode doesn't remove migration effort entirely, though. It applies at the session or server level, so mixed workloads need careful configuration, and Oracle-only features such as synonyms, materialized views, or hierarchical queries still have no equivalent even with the mode enabled. Treat it as an accelerator for procedural code, not a substitute for the assessment and testing steps covered in this guide.

Step-by-step workflow to migrate data from Oracle to MariaDB

A reliable migration follows the same sequence regardless of tooling: connect source and target, create the target schema, transfer the data, and validate the result. One possible implementation is shown below, using MariaDB's CONNECT storage engine, an Oracle ODBC driver for connectivity, and a data comparison tool for loading and validation.

Prepare the target MariaDB environment

Install MariaDB and enable the CONNECT storage engine on the target server:

sudo apt-get install mariadb-server
sudo mysql_secure_installation
sudo apt-get install mariadb-plugin-connect

Configure Oracle connectivity

Install and configure Devart ODBC Driver for Oracle on the same machine, and register it as a system or user data source. Then verify the connection from MariaDB:

isql -v DEVART_ORACLE

Create CONNECT tables

Create CONNECT tables in MariaDB that reference the required Oracle tables, reading live data directly over ODBC without exporting a dump file first:

CREATE TABLE EMP ENGINE = CONNECT TABLE_TYPE = ODBC
BLOCK_SIZE = 10 TABNAME = 'SCOTT.EMP'
CONNECTION = 'DSN=DEVART_ORACLE;';

Review the automatically generated column definitions before relying on them, and add primary or foreign keys if necessary—these typically aren't inferred from the linked ODBC tables.

Create the target schema

Create native MariaDB tables using the appropriate storage engine (for example, InnoDB), applying the data type mapping covered earlier and adjusting data types, constraints, and indexes where required:

CREATE TABLE EMP (
    EMPNO SMALLINT NOT NULL PRIMARY KEY,
    ENAME VARCHAR(40),
    JOB VARCHAR(36),
    HIREDATE TIMESTAMP,
    SAL DECIMAL(9,2),
    DEPTNO SMALLINT
);

Compare source and target data

Use Data Compare in dbForge Studio for MySQL to compare the Oracle-linked CONNECT tables with the native MariaDB tables, instead of writing manual INSERT...SELECT statements for every table. Point the tool at the CONNECT-linked tables as the source and the native MariaDB tables as the target, then run a comparison.

Generate and execute the synchronization script

Review the generated synchronization script, then apply it to populate the target tables. Rerun it if additional changes are required—the same script works for incremental loads as well as the initial full transfer, but review it closely for large tables and adjust batching or filtering as needed.

Validate the migration

Run Data Compare again to verify that the target data matches the Oracle source before application testing and production cutover. A clean second comparison, with no remaining differences, is the practical signal that a table has migrated successfully.

For large or frequently changing tables, repeat this comparison close to cutover to catch any Oracle-side changes that happened during migration.

How to handle procedures, packages, triggers, and PL/SQL logic

Migrating logic is usually harder than moving tables, because procedural code depends on control-flow constructs and error handling that don't map one-to-one between Oracle PL/SQL and MariaDB's SQL, even under Oracle compatibility mode.

PL/SQL conversion examples

The following examples show common Oracle-to-MariaDB conversion patterns. Actual changes depend on the target MariaDB version and whether SQL_MODE=ORACLE is enabled.

Replace NVL() with IFNULL()

SELECT employee_id,
       NVL(commission_pct, 0) AS commission_pct
FROM employees;

SELECT employee_id,
       IFNULL(commission_pct, 0) AS commission_pct
FROM employees;

Replace DECODE() with CASE

SELECT employee_id,
       DECODE(status,
              'A', 'Active',
              'I', 'Inactive',
              'Unknown') AS status_name
FROM employees;

SELECT employee_id,
       CASE status
           WHEN 'A' THEN 'Active'
           WHEN 'I' THEN 'Inactive'
           ELSE 'Unknown'
       END AS status_name
FROM employees;

Replace a package with standalone routines

CREATE OR REPLACE PACKAGE employee_api AS
    PROCEDURE update_salary(
        p_employee_id IN NUMBER,
        p_salary IN NUMBER
    );
END employee_api;
/

CREATE OR REPLACE PACKAGE BODY employee_api AS
    PROCEDURE update_salary(
        p_employee_id IN NUMBER,
        p_salary IN NUMBER
    ) IS
    BEGIN
        UPDATE employees
        SET salary = p_salary
        WHERE employee_id = p_employee_id;
    END update_salary;
END employee_api;
/

DELIMITER //

CREATE PROCEDURE employee_api_update_salary(
    IN p_employee_id BIGINT,
    IN p_salary DECIMAL(12, 2)
)
BEGIN
    UPDATE employees
    SET salary = p_salary
    WHERE employee_id = p_employee_id;
END//

DELIMITER ;

Simple functions often require only syntax changes, while packages, exception handling, dynamic SQL, and complex triggers usually require manual redesign and testing.

Cursors generally convert with minor syntax changes, but exception handling is a common failure point: Oracle's exception blocks and pragma-based custom exceptions need rewriting using MariaDB's DECLARE HANDLER syntax, which behaves differently in scope and propagation. Variables and control structures such as loops and IF/CASE blocks usually convert with light editing.

Package-based logic is often the hardest part of a migration. Under MariaDB's default SQL mode, there's no direct equivalent to Oracle packages, so package-level variables, private procedures, and grouped functionality need to be reorganized into separate, appropriately scoped procedures and functions. Oracle compatibility mode (covered later in this guide) adds support for Oracle-style packages, but it doesn't automatically convert existing packages or fully replicate Oracle package behavior. To manage this efficiently, sort procedural code into three buckets during assessment—directly portable, partly portable, and rewrite-required—so the most complex objects don't stall the rest of the migration.

Testing, validation, and cutover planning

Migration isn't complete once data and schema exist in MariaDB—it's complete once you've confirmed the system behaves correctly under real conditions. Treat testing and cutover as a distinct phase with its own checklist.

Start with data validation: row counts, checksums, or full data comparison between source and target, plus spot checks on edge cases such as NULLs and large text or binary fields. Follow with functional testing—run the application's actual queries and stored procedure calls against MariaDB and confirm results match Oracle's, paying particular attention to the function and syntax differences covered earlier in this guide.

Run performance benchmarks under realistic load, since index behavior and query plans differ enough between Oracle and MariaDB that some queries will need new indexes. Replace Oracle-specific connectors in the application stack, review database privileges, and define rollback criteria and a cutover plan before going live.

Oracle-to-MariaDB migration tools: Manual vs. automated approaches

Tool choice depends on schema complexity, how much procedural code you have, downtime tolerance, and how much validation the migration needs. The three approaches below aren't mutually exclusive—most migrations combine at least two.

When a GUI-based migration workflow makes sense

A GUI-based workflow makes sense when you need visibility into every step—connecting to source and target, reviewing generated schema, and inspecting data differences before they're applied. This matters most for migrations with moderate complexity, where full automation risks silent errors but a fully manual process is too slow.

It's also the practical choice when the migration team includes developers or analysts who need control over data type mapping and validation without writing custom scripts for every table.

When automated conversion tools are worth it

Automated conversion tools are worth it once you're migrating many similar objects—tables with consistent structure, or PL/SQL objects that mostly follow the same patterns. The upfront setup pays off through repeatability, especially if you need to re-run the conversion across dev, staging, and production.

They're also valuable for the assessment step: scanning hundreds of objects manually isn't practical, but an automated compatibility report turns it into a same-day task. Budget manual review time for their output rather than trusting it blindly.

When manual migration is still necessary

Manual migration is still necessary for Oracle-only constructs with no tool-generated equivalent—packages, hierarchical queries, materialized views, and complex dynamic SQL usually need a developer to redesign the logic rather than convert it.

Even in largely automated or GUI-driven migrations, expect some manual work at the edges: fixing edge-case data type mismatches or rewriting a handful of stored procedures that rely on Oracle-specific behavior.

How dbForge Edge can help with Oracle-to-MariaDB migration work

Migration teams need more than a one-time transfer utility—they need a workspace for schema inspection, SQL development, data comparison, and validation across the whole project. Instead of switching between multiple standalone tools, dbForge Edge—a universal database tool—brings together dedicated IDEs for Oracle, MySQL and MariaDB, SQL Server, and PostgreSQL in a single bundle, allowing migration teams to perform the entire workflow from one integrated environment.

For the workflow described in this guide, dbForge Studio for MySQL—a MySQL GUI with its own MariaDB GUI client—provides the core migration capabilities. You can create and manage the target MariaDB schema, compare Oracle data exposed through CONNECT tables with native MariaDB tables, generate synchronization scripts, and rerun comparisons at any stage of the migration to verify the results before production cutover.

At the same time, dbForge Studio for Oracle—a comprehensive IDE for Oracle—helps you inspect the source schema, review database objects, and analyze SQL and PL/SQL code before conversion. Its SQL editor, code completion, and navigation features simplify understanding existing Oracle code and preparing it for migration.

Since all dbForge Edge components share a consistent interface and licensing model, DBAs, developers, architects, and analysts can work with Oracle and MariaDB using purpose-built tools without changing products throughout the migration lifecycle.

Try dbForge Edge free for 30 days to evaluate the workflow on your own Oracle and MariaDB environments.

dbForge Edge

A comprehensive solution that unifies development, management, and analysis across multiple databases within a single suite.

Download for free

Common Oracle-to-MariaDB migration mistakes to avoid

Most failed or rolled-back migrations trace back to a small set of avoidable mistakes, not fundamentally difficult technical problems.

Treating migration like a simple export/import task

Export/import moves rows, but it doesn't validate that the target schema, data types, and constraints preserve the source's behavior. Teams that treat migration as a data copy job often discover—only after go-live—that constraints weren't enforced or that Oracle-specific defaults silently didn't carry over.

Ignoring Oracle-specific SQL and PL/SQL

Assuming Oracle SQL and PL/SQL will run unchanged on MariaDB, even with compatibility mode enabled, is a frequent source of late-stage failures. Functions such as TO_DATE() or DECODE(), hierarchical queries, and package-based logic don't silently degrade—they fail outright or return subtly wrong results that pass a quick smoke test.

Skipping structured validation before cutover

Confirming that data "looks right" isn't the same as validating row counts, constraints, and application behavior systematically. Run the full validation sequence—data comparison, constraint checks, functional testing, and performance benchmarking—before every cutover, not just the first one.

Conclusion

A successful Oracle-to-MariaDB migration follows the same order every time: assess the schema and code first, map data types and functions carefully, migrate data through a controlled and repeatable workflow, rewrite SQL and PL/SQL differences early rather than at cutover, and validate thoroughly before going live. Skipping assessment or validation is the most common reason migrations turn into rollback events rather than successful cutovers.

If you're planning an Oracle-to-MariaDB migration, start by scoping your own schema and procedural code against the checklist in this guide, then evaluate whether a GUI-driven workflow like the one described here fits your team's complexity and validation needs. Try dbForge Edge free for 30 days to review your Oracle schema, compare data, and validate your MariaDB target before you commit to a production cutover.

FAQ