How do I fix ORA-03076?

Put DEFAULT immediately after the datatype, before NOT NULL. The same order applies to ALTER TABLE ... MODIFY, and to DEFAULT ON NULL columns.

Oracle · error reference

ORA-03076: unexpected item DEFAULT in a CREATE TABLE

ORA-03076: unexpected item DEFAULT

Oracle wants DEFAULT before NOT NULL. MySQL accepts either order, so converted DDL fails on clause order alone — with an error that never mentions order.

What the database is telling you

Oracle's column syntax fixes the order of clauses: type, then DEFAULT, then the inline constraint. Write NOT NULL DEFAULT 0 and the parser reaches DEFAULT in a position where no clause may appear, and says so in the least helpful way available.

Why it shows up in a migration

MySQL is relaxed about this, and a great deal of real-world MySQL DDL is written NOT NULL DEFAULT 0 — it is the order the MySQL manual itself uses in examples. Ported verbatim, every such column fails on Oracle. Because it is a DDL error the table is never created, and the data load then fails for a different reason entirely (ORA-00942, table does not exist), which sends people looking in completely the wrong place.

Common causes

DDL translated from MySQL or MariaDB with the clause order preserved.
A generated CREATE TABLE that appends clauses in declaration order rather than Oracle's required order.
DEFAULT placed after a column-level CHECK or REFERENCES clause.

How to fix it

Put DEFAULT immediately after the datatype, before NOT NULL.

-- Rejected
qty NUMBER NOT NULL DEFAULT 0

-- Accepted
qty NUMBER DEFAULT 0 NOT NULL

2
The same order applies to ALTER TABLE ... MODIFY, and to DEFAULT ON NULL columns.
```
ALTER TABLE items MODIFY (qty NUMBER DEFAULT 0 NOT NULL);
```

What DBShifts does about it

The Oracle DDL generator builds each column definition in Oracle's required order rather than the source's, so a MySQL column written NOT NULL DEFAULT 0 comes out as DEFAULT 0 NOT NULL. The ordering is enforced where the column is built, not patched up afterwards.

Oracle DDL generator

Migrating between engines?

DBShifts converts the schema, moves the data, and then proves the two sides match with a type-aware checksum per table — not just a row count. Errors like this one are handled on the way, and the ones that cannot be handled are reported rather than logged and forgotten.

Start free See the migration paths

Related errors

Oracle

ORA-32795: cannot insert into a generated always as identity column

Oracle refuses explicit values for a GENERATED ALWAYS AS IDENTITY column. Why it breaks every data load that carries its own primary keys, and the one-word change that fixes it.

Oracle

ORA-00942: table or view does not exist

Oracle's most-searched error, and usually not about a missing table at all — it is about privileges, schema qualification, or a DROP that has no IF EXISTS.

← All database errors