Migrate Oracle to SQLite
Beta — live-tested pathMigrating from Oracle (Oracle Database, the long-standing enterprise standard) to SQLite (the embedded database that ships inside everything) means every table, column type, key, index and row has to survive two engines' different opinions about data. This is a cross-model migration — rows and relations are preserved as structured documents — and the conversion rules are explicit, not guessed. DBShifts converts the schema with deterministic rules, transfers data in parallel batches, and validates the result with per-table row counts and type-aware checksums.
Free tier · no credit card
Oracle vs SQLite
Oracle
Oracle Database, the long-standing enterprise standard.
SQLite
the embedded database that ships inside everything.
How the migration works
Connect
Point DBShifts at your source and target. Credentials stay encrypted; SSH tunnels supported for private databases.
Analyze
Automatic schema introspection produces a migration plan: what converts automatically, what migrates with warnings, what needs human review.
Migrate
Schema is created on the target, data transfers in parallel batches with constraints deferred, indexes rebuilt after load.
Validate
Per-table row counts, type-aware checksums on both sides, FK integrity — plus a fidelity report listing anything lossy.
Oracle to SQLite data type mapping
Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.
| Oracle | SQLite |
|---|---|
| NUMBER(10) | INTEGER |
| NUMBER(19) | TEXT |
| NUMBER(10,2) | NUMERIC |
| VARCHAR2(255) | TEXT |
| NVARCHAR2(255) | TEXT |
| CHAR(10) | TEXT |
| CLOB | TEXT |
| NCLOB | TEXT |
| BLOB | BLOB |
| DATE | TEXT |
| TIMESTAMP | TEXT |
| BINARY_FLOAT | REAL |
| BINARY_DOUBLE | REAL |
| RAW | BLOB |
| LONG | TEXT |
Example: Oracle to SQLite schema conversion
Oracle
CREATE TABLE orders ( id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, customer_id NUMBER(10) NOT NULL, total NUMBER(10,2) NOT NULL, notes CLOB, reference VARCHAR2(255), placed_at TIMESTAMP NOT NULL );
SQLite
CREATE TABLE IF NOT EXISTS "orders" (
"id" INTEGER,
"customer_id" INTEGER NOT NULL,
"total" NUMERIC NOT NULL,
"notes" TEXT,
"reference" TEXT,
"placed_at" TEXT NOT NULL,
PRIMARY KEY ("id")
);A very large engine to a very small one
Oracle's feature set has no SQLite counterpart in almost every dimension that matters operationally: no PL/SQL, no packages, no partitioning, no materialised views, no roles or fine-grained access control, no parallel query. This is not a like-for-like migration and should not be planned as one.
It makes sense for a specific goal: an offline or embedded copy, a portable test fixture, or a single-file extract someone can inspect without Oracle access. For those, the right scope is usually a subset of tables with a subset of rows, not the whole database.
NUMBER, and precision you cannot keep
Oracle NUMBER supports 38 significant digits. SQLite stores numbers as 64-bit integers or 8-bit-exponent floats, which gives about 15-17 significant digits for anything non-integer. Values beyond that lose precision silently, because SQLite does not raise on precision loss.
For financial data the practical answer is to store the value as an integer count of minor units, or as text, and convert on read. Either preserves the exact value; a float does not.
Dates, and Oracle's DATE carrying a time
An Oracle DATE includes hours, minutes and seconds, unlike the DATE type in most other engines. Converting it to a date-only representation drops the time component without any error, which is one of the easier ways to lose data on this route.
SQLite has no temporal type, so the value becomes ISO-8601 text. Keeping the full timestamp in that text, rather than truncating to a day, is what preserves the original value.
Oracle → SQLite conversion notes
Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.
- NUMBER columns are fetched as exact decimals (38 digits), not lossy floats.
- Identifier case differences (Oracle uppercases unquoted names) are reconciled automatically during transfer.
- Empty string = NULL semantics are accounted for in validation so comparisons stay honest.
- High-precision decimals land in TEXT columns on purpose — SQLite's NUMERIC affinity would silently convert them to lossy REAL.
- Unsigned BIGINT values beyond the signed 64-bit range are stored as exact text rather than corrupted floats.
- Dates and times are stored as ISO-8601 text, the SQLite convention.
Frequently asked questions
Is Oracle to SQLite migration production-ready in DBShifts?
Oracle to SQLite is a live-verified beta pair: it passed the same certification suite as every other pair — a real migration with edge-case data validated by row counts and checksums — and ships with a post-migration fidelity report you can audit before cutover.
How does DBShifts verify the Oracle to SQLite migration was correct?
Three layers: per-table row counts on both sides, an order-independent type-aware checksum of row data (canonicalized so engine representation differences don't false-alarm), and FK integrity checks on the target. Rows that fail to insert are quarantined with the exact error — never silently dropped — and can be fixed and retried from the UI.
What happens to Oracle types that SQLite doesn't have?
They convert by deterministic rules with a recorded decision: a native equivalent where one exists, a portable fallback (TEXT/JSON/DECIMAL) where one doesn't. Every lossy conversion appears in the migration plan before you run and in the fidelity report after. You can override any mapping per column.
Can I keep Oracle and SQLite in sync after the initial migration?
Yes. DBShifts supports incremental sync (UPSERT-based delta transfers on a sync key) and, for supported sources, real-time change data capture so the target stays current until you cut over.
Do I need to write any SQL or scripts for Oracle to SQLite?
No. Connect both databases, review the generated migration plan (what's automatic, what migrates with warnings, what needs manual review — typically triggers and stored procedures), and run. Procedural code is transpiled best-effort and queued for human review rather than auto-applied.
What's the hardest part of moving Oracle to SQLite?
The data model itself differs (sql → embedded), so rows and relations are restructured into documents with explicit type rules. DBShifts surfaces every inference and lossy conversion in the migration plan before you run, so nothing is guessed silently.
Which Oracle data types change when moving to SQLite?
4 of the mapped types convert to something that cannot represent exactly the same range or constraint. The clearest cases are NUMBER(10) to INTEGER, CLOB to TEXT, TIMESTAMP to TEXT. The full table above lists every mapping the converter performs, and analysis flags each one on your actual schema before any data moves.
Go deeper
Related migration paths
Ready to move Oracle to SQLite?
Set up in under two minutes. Validation and rollback included.
Start Free Migration