sqlserver
oracle

Migrate SQL Server to Oracle

Certified — live-tested path

The converter maps 21 SQL Server column types to Oracle. 3 of them change kind on the way (BIT becomes NUMBER(1), TIME becomes VARCHAR2(18), UNIQUEIDENTIFIER becomes VARCHAR2(255 CHAR)); 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.

Start SQL Server → Oracle Migration

Free tier · no credit card

SQL Server vs Oracle

sqlserver

SQL Server

Microsoft's enterprise relational database.

oracle

Oracle

Oracle Database, the long-standing enterprise standard.

SQL Server to Oracle data type mapping

Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.

SQL ServerOracle
INTNUMBER(10)
BIGINTNUMBER(19)
SMALLINTNUMBER(5)
TINYINTNUMBER(3)
BITNUMBER(1)
DECIMAL(10,2)NUMBER(10,2)
MONEYNUMBER(18,6)
FLOATBINARY_DOUBLE
REALBINARY_FLOAT
VARCHAR(255)VARCHAR2(255 CHAR)
NVARCHAR(255)VARCHAR2(255 CHAR)
NVARCHAR(MAX)CLOB
TEXTCLOB
CHAR(10)CHAR(10 CHAR)
DATEDATE
DATETIMETIMESTAMP
DATETIME2TIMESTAMP
DATETIMEOFFSETTIMESTAMP WITH TIME ZONE
TIMEVARCHAR2(18)
VARBINARY(MAX)BLOB
UNIQUEIDENTIFIERVARCHAR2(255 CHAR)

Example: SQL Server to Oracle schema conversion

SQL Server

CREATE TABLE orders (
  id            INT IDENTITY(1,1) PRIMARY KEY,
  customer_id   INT NOT NULL,
  total         DECIMAL(10,2) NOT NULL,
  is_gift       BIT NOT NULL DEFAULT 0,
  notes         NVARCHAR(MAX),
  reference     UNIQUEIDENTIFIER,
  placed_at     DATETIME2 NOT NULL
);

Oracle

CREATE TABLE "orders" (
    "id" NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    "customer_id" NUMBER(10) NOT NULL,
    "total" NUMBER(10,2) NOT NULL,
    "is_gift" NUMBER(1) DEFAULT 0 NOT NULL,
    "notes" CLOB,
    "reference" VARCHAR2(255 CHAR),
    "placed_at" TIMESTAMP NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY ("id")
);

SQL Server keeps empty strings; Oracle cannot

In SQL Server, '' and NULL are different values, and LEN('') is 0. In Oracle there is no empty string: '' in a VARCHAR2 is NULL, and LENGTH('') is NULL too. Every empty value in a nullable column arrives as NULL, and every empty value in a NOT NULL column is rejected with ORA-01400 and parked in quarantine.

Count them per column before the load with SELECT count(*) FROM t WHERE col = ''. The usual decisions are to drop NOT NULL on columns where empty is legitimate, or to replace '' with a single space or a sentinel the application understands. Both are cheaper before the migration than after.

BIT and TIME have no Oracle type

BIT becomes NUMBER(1) and TIME becomes VARCHAR2(18), because Oracle has neither a boolean column (before 23ai) nor a time-of-day type; its DATE always carries a date. A NUMBER(1) will accept 7, so add CHECK (col IN (0, 1)) where the database should keep enforcing a flag. TIME values arrive as text: exact, but no longer something you can add an interval to.

If the application does arithmetic on those times, convert the column to INTERVAL DAY TO SECOND after the load. If it only displays them, text is the simpler and safer place to leave them.

IDENTITY becomes GENERATED BY DEFAULT

SQL Server's IDENTITY maps to NUMBER GENERATED BY DEFAULT AS IDENTITY. BY DEFAULT rather than ALWAYS, so the load can insert the ids the rows already have instead of Oracle renumbering them and breaking every foreign key.

The identity's counter does not know about those ids. If it starts at 1, the first new row collides with an imported one. Move it past the highest imported value before writes start: ALTER TABLE t MODIFY (id GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE)) sets it to the current maximum plus one.

SQL Server → Oracle conversion notes

Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.

  • TINYINT is unsigned (0–255) in SQL Server — DBShifts widens it on signed targets so 128–255 don't clip.
  • UNIQUEIDENTIFIER, DATETIMEOFFSET, MONEY and NVARCHAR(MAX) all have explicit conversion rules per target.
  • Wrapped defaults like ((1)) and (N'foo') are unwrapped to portable literals.
  • 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 SQL Server to Oracle migration production-ready in DBShifts?

Yes. SQL Server to Oracle is a certified pair: it passes the live certification suite on real SQL Server and Oracle servers, which checks schema fidelity, row counts, type-aware checksums and paging across batches.

What happens to SQL Server types that Oracle doesn't have?

Each gets the nearest Oracle type the converter treats as safe: BIT becomes NUMBER(1), TIME becomes VARCHAR2(18), UNIQUEIDENTIFIER becomes VARCHAR2(255 CHAR). Every such decision is in the migration plan before anything runs, and any mapping can be overridden per column.

Can I keep SQL Server and Oracle in sync after the initial migration?

Yes, until you cut over. DBShifts reads SQL Server Change Tracking, which has to be enabled on the database and on each table. With zero-downtime mode on, the tracking version is captured before the first row is copied.

Which SQL Server data types need a second look when moving to Oracle?

The table above carries a note on 4 of them: MONEY (NUMBER(18,6)), FLOAT (BINARY_DOUBLE), DATETIMEOFFSET (TIMESTAMP WITH TIME ZONE), UNIQUEIDENTIFIER (VARCHAR2(255 CHAR)). Analysis flags the same columns on your own schema before any data moves.

Go deeper

Ready to move SQL Server to Oracle?

Set up in under two minutes. Validation and rollback included.

Start Free Migration