Migrate MySQL to Oracle
Certified — live-tested pathThe converter maps 20 MySQL column types to Oracle. 6 of them change kind on the way (TINYINT(1) becomes NUMBER(1), TIME becomes VARCHAR2(18), YEAR becomes NUMBER(4), and more); the table below lists every one. DBShifts converts the schema, copies the rows in parallel batches and checks the result with row counts and checksums.
Free tier · no credit card
Not ready to connect a database? Paste your CREATE TABLE statements and see the Oracle schema now
MySQL vs Oracle
MySQL
the world's most deployed open-source relational database.
Oracle
Oracle Database, the long-standing enterprise standard.
MySQL to Oracle data type mapping
Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.
| MySQL | Oracle |
|---|---|
| INT | NUMBER(10) |
| BIGINT | NUMBER(19) |
| SMALLINT | NUMBER(5) |
| TINYINT(1) | NUMBER(1) |
| DECIMAL(10,2) | NUMBER(10,2) |
| FLOAT | BINARY_FLOAT |
| DOUBLE | BINARY_DOUBLE |
| VARCHAR(255) | VARCHAR2(255 CHAR) |
| TEXT | CLOB |
| LONGTEXT | CLOB |
| CHAR(10) | CHAR(10 CHAR) |
| DATE | DATE |
| DATETIME | TIMESTAMP |
| TIMESTAMP | TIMESTAMP WITH TIME ZONE |
| TIME | VARCHAR2(18) |
| YEAR | NUMBER(4) |
| BLOB | BLOB |
| JSON | CLOB |
| ENUM('a','b') | VARCHAR2(255) |
| SET('a','b') | VARCHAR2(4000) |
Example: MySQL to Oracle schema conversion
MySQL
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
status ENUM('new','paid','shipped') NOT NULL DEFAULT 'new',
total DECIMAL(10,2) NOT NULL,
is_gift TINYINT(1) NOT NULL DEFAULT 0,
notes TEXT,
metadata JSON,
placed_at DATETIME NOT NULL
);Oracle
CREATE TABLE "orders" (
"id" NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
"customer_id" NUMBER(10) NOT NULL,
"status" VARCHAR2(255) DEFAULT 'new' NOT NULL,
"total" NUMBER(10,2) NOT NULL,
"is_gift" NUMBER(1) DEFAULT 0 NOT NULL,
"notes" CLOB,
"metadata" CLOB,
"placed_at" TIMESTAMP NOT NULL,
CONSTRAINT pk_orders PRIMARY KEY ("id")
);ENUM and SET lose their value lists
MySQL enforces an ENUM's values; Oracle has no ENUM. The converter makes the column VARCHAR2(255) and keeps its default, 'new' in the example above, but adds no CHECK, so after the move Oracle accepts any string at all. SET columns become VARCHAR2(4000), with the same lack of enforcement.
If the list matters, add CHECK (status IN ('new', 'paid', 'shipped')) after the load. Adding it also verifies the data: if any migrated value is outside the list, the ALTER TABLE fails and tells you so.
Empty strings and zero dates have no Oracle form
Oracle cannot store an empty string, so a MySQL '' becomes NULL, and a NOT NULL column holding any empty values rejects those rows with ORA-01400. A zero date, 0000-00-00, is not a valid Oracle DATE at all. DBShifts normalises zero dates and reports them rather than dropping the row, but the replacement is a value your application never wrote, so read the report.
Both are easiest to find on the MySQL side: SELECT count(*) FROM t WHERE col = '' for text columns, and WHERE d = '0000-00-00' for date columns.
TIME becomes text
MySQL's TIME is a duration that runs from -838:59:59 to 838:59:59. Oracle has no TIME type, and its INTERVAL DAY TO SECOND uses a different literal format, so the converter stores TIME as VARCHAR2(18). Negative and over-24-hour values fit, because nothing is parsing them, and nothing is doing arithmetic on them either.
TIMESTAMP becomes TIMESTAMP WITH TIME ZONE while DATETIME becomes a plain TIMESTAMP. That mirrors MySQL, where TIMESTAMP is stored as UTC and DATETIME is stored as written; keep the distinction rather than flattening both, or the instants of one of the two will shift.
MySQL → Oracle conversion notes
Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.
- TINYINT(1) is treated as a boolean; plain TINYINT stays numeric — converting both blindly corrupts ratings and counters.
- UNSIGNED integers are widened on targets without unsigned types, so values above the signed range survive.
- ENUM and SET columns are converted with their value lists preserved (native enums, CHECK constraints, or arrays depending on the target).
- TIME columns are durations (-838h..838h) at the driver level — DBShifts converts them per target instead of failing the batch.
- Zero dates (0000-00-00) are normalized deterministically and reported, never silently dropped.
- CHAR columns use character semantics, so multi-byte UTF-8 doesn't overflow byte-sized columns.
- Booleans land as NUMBER(1) with values bound as integers (Oracle drivers can't bind Python booleans to NUMBER).
- DROP TABLE … CASCADE CONSTRAINTS wrappers handle re-runs without manual cleanup.
Frequently asked questions
Is MySQL to Oracle migration production-ready in DBShifts?
Yes. MySQL to Oracle is a certified pair: it passes the live certification suite on real MySQL and Oracle servers, which checks schema fidelity, row counts, type-aware checksums and paging across batches.
What happens to MySQL types that Oracle doesn't have?
Each gets the nearest Oracle type the converter treats as safe: TINYINT(1) becomes NUMBER(1), TIME becomes VARCHAR2(18), YEAR becomes NUMBER(4), JSON becomes CLOB, ENUM('a','b') becomes VARCHAR2(255). Every such decision is in the migration plan before anything runs, and any mapping can be overridden per column.
Can I keep MySQL and Oracle in sync after the initial migration?
Yes, until you cut over. DBShifts reads the row-based binary log (binlog_format = ROW). With zero-downtime mode on, the log position is captured before the first row is copied, so writes made during the copy are replayed afterwards.
Which MySQL data types need a second look when moving to Oracle?
The table above carries a note on 9 of them: TINYINT(1) (NUMBER(1)), FLOAT (BINARY_FLOAT), DOUBLE (BINARY_DOUBLE), TIMESTAMP (TIMESTAMP WITH TIME ZONE) and more. Analysis flags the same columns on your own schema before any data moves.
Go deeper
How We Live-Certified All 49 Migration Paths
The certification harness behind this page's claims.
Big-Bang, Trickle, or CDC: Choosing Your Migration Strategy
Pick by two numbers: the downtime you can afford, and how fast the source changes.
Reference
- Getting started
- Running a migration, step by step
- Full type-mapping reference
- All supported pairs and their tiers
- Known limitations
- Understanding data-loss warnings
- Going live with cutover
- Watching for drift after cutover
- Run this migration from CI/CD
- Rewrite MySQL queries for Oracle
- Convert your MySQL CREATE TABLE statements to Oracle
Compared with other tools
Errors you may hit
Ready to move MySQL to Oracle?
Set up in under two minutes. Validation and rollback included.
Start Free Migration