MySQL to Oracle migration guide

IntoSQL reads a MySQL or MariaDB dump and writes one Oracle script with the same tables, columns, keys, indexes, foreign keys, defaults, identities, sequences, comments and rows. It reads the dump itself, without a database server, and lists in the report every value Oracle can't hold as it is and every object it doesn't carry. The script loads on Oracle Database 19c and later.

Convert in the browser: MySQL to Oracle opens the converter set up for this pair.

Dump for the best result#

Any dump from mysqldump, mariadb-dump, mysqlpump, phpMyAdmin or Adminer works, as .sql or .sql.gz. These options keep more of your data:

mysqldump --default-character-set=utf8mb4 --hex-blob \
  --routines --triggers --events --single-transaction \
  shop | gzip > shop.sql.gz

• --default-character-set=utf8mb4 keeps emoji and other 4-byte characters. A dump made with utf8 has already lost them, and the report says so (dump_lost_four_byte_chars).
• --hex-blob writes binary columns as hexadecimal, which survives copying between systems.
• --routines --triggers --events add those objects to the dump, so the report can list them by name.
• --single-transaction takes a consistent snapshot of InnoDB tables without locking them.

A dump made with --no-create-info still converts: the types are read from the values, and the report says the schema was missing (no_schema).

Convert#

In the browser, open MySQL to Oracle or the converter on the home page, drop the dump and choose Oracle. It reads the file from your disk in pieces, twice, and writes the script as it goes, so the dump is never uploaded and its size isn't limited by memory. The desktop app does the same with the file on disk.

With the command line:

intosql inspect shop.sql.gz
intosql convert shop.sql.gz --to oracle --output shop.oracle.sql --report shop.report.json

inspect lists the tables, their rows and what won't be converted, without writing anything. convert writes the script and, with --report, the report as JSON.

Through the API, a dump converts like any other file: open a conversion with its name ending in .sql or .sql.gz and "output": {"dialect": "oracle"}.

Load into Oracle#

NLS_LANG=.AL32UTF8 sqlplus -S -L shop@//localhost/FREEPDB1 @shop.oracle.sql

NLS_LANG makes SQL*Plus read the script as UTF-8. The user needs CREATE TABLE, CREATE SEQUENCE, CREATE TRIGGER and CREATE PROCEDURE, and quota on its tablespace. The script stops at the first error, creates the tables, copies the rows in blocks of 100 with a commit after each, then adds keys, indexes, foreign keys, identities, sequences, triggers and comments. Foreign keys are added ENABLE NOVALIDATE, so new rows are checked at once, and then validated: an old row that points to a missing row is named by its constraint instead of failing the load half way.

Oracle stores an empty string as NULL, so values that were '' read back as NULL; the report counts them per column (empty_string_null). Plain names become uppercase (customers is CUSTOMERS), so queries can leave the quotes out.

What isn't converted#

Views, triggers, stored procedures, functions and events are written in each database's own language. They are listed in the report by kind and name, never dropped silently. ON UPDATE CURRENT_TIMESTAMP is the exception: it becomes an Oracle trigger.

Data types#

MySQL to Oracle data types lists each type that changes in the script: what it becomes, why, and the report line.

Every other rule#

Generated from the engine, where each rule is decided.