dbSlice

← All guides

Guides · Migrate

Data migration from Oracle to SQL Server

Moving tables from Oracle to SQL Server? dbSlice copies the data from a Windows app or the command line, keeps SQL Server in sync while Oracle is still live, and compares both sides key by key before you switch.

· 5 min read

Try it free on the Microsoft Store ↗ See pricing

Migrating to SQL Server from Oracle: what changes in the target

If the SQL Server table does not exist, dbSlice stops and shows a CREATE TABLE translated for SQL Server, for your DBA to review and run. By default it creates nothing in your databases. These are the Oracle details to plan for:

  • DATE has a time. An Oracle DATE keeps the date and the time to the second, so it is suggested as DATETIME2, not DATE. Change it to DATE only for columns that never store a time.
  • NUMBER without precision. It is suggested as DECIMAL(38,10). Check the real values and set the precision and scale before running the CREATE TABLE. A declared NUMBER(p,s) keeps its precision and scale. Precision is capped at 38, which is also the Oracle limit.
  • Integer keys and flags. Oracle has no separate integer type, so NUMBER(10) becomes DECIMAL(10,0). Change it to INT or BIGINT in the DDL where you prefer. A NUMBER(1) or CHAR(1) flag ('Y'/'N') can become BIT through a SELECT on the source, for example CASE active WHEN 'Y' THEN 1 ELSE 0 END AS active.
  • RAW(16) ids from SYS_GUID(). They arrive as binary and are suggested as VARBINARY(MAX). Declare BINARY(16) so the column can be a key. Converting them to UNIQUEIDENTIFIER changes the byte order, so test it on a few rows first.
  • Empty strings. Oracle stores '' as NULL, so there are no empty strings to copy. A SQL Server column declared NOT NULL rejects those rows. Leave it nullable or fill a default in a SELECT, such as NVL(middle_name, ' ').
  • VARCHAR2, CLOB and LONG. Text becomes NVARCHAR(n) up to 4,000 characters, and NVARCHAR(MAX) above that. CLOB, NCLOB and the old LONG type also become NVARCHAR(MAX). NVARCHAR keeps every character of an AL32UTF8 database.
  • TIMESTAMP WITH TIME ZONE. It is suggested as DATETIME2, and the offset is not carried over. If the zone matters, normalize it on the source with a SELECT such as SYS_EXTRACT_UTC(created_at) AS created_at, and store the value in DATETIME2 as UTC.
  • XMLTYPE, INTERVAL, ROWID and spatial types. They have no direct equivalent and are suggested as NVARCHAR(MAX). Convert them in a SELECT on the source, for example XMLSERIALIZE(CONTENT doc AS CLOB), or leave them out.
  • Upper-case names. Oracle stores unquoted names in upper case. Choose the SQL Server table and column names in the CREATE TABLE; dbSlice maps columns by name, so keep them matching.
  • Sequences instead of identity. Oracle ids usually come from a sequence or a trigger. If the SQL Server key is an IDENTITY column, turn on Keep identity, which needs ALTER permission on the table and is confirmed on screen first. Without it, SQL Server generates new ids, so do not use the id as the sync key. A plain BIGINT key needs neither.
  • Upsert runs as MERGE and bulk loads use SqlBulkCopy with triggers and constraints checked.
dbSlice Synchronize tab

How dbSlice controls the load

  • Reads Oracle in small batches, ordered by the key, so the live database keeps serving its users.
  • Matches rows by a key you choose. Modes: Initial load, then Upsert, Insert new, Update only or incremental by column. An incremental run by a DATE column filters with TO_DATE, so Oracle keeps using the index on that column.
  • Checks before writing with Record Diff and on-screen checks, and proves the result with Compare and the full list of divergent keys.
  • Masks on the way for development and test copies, keeping keys and foreign keys intact.
  • Export instead of load: when you only need a file, the same app exports an Oracle table to CSV, JSON, XML or HTML.

Step by step: convert a table from Oracle to SQL Server

  1. Connect to Oracle with the user that owns the tables, and to SQL Server. dbSlice lists the tables of the connected user; for tables owned by another schema, connect as that owner or expose them through a view in your own schema.
  2. Pick the source table (or a SELECT, as in the flag and XMLTYPE examples) and the target table.
  3. Choose the key column, and turn on Keep identity if the target key is an IDENTITY column and the ids must stay the same.
  4. Choose the mode: Initial load, then Upsert or Insert new.
  5. Run Record Diff, then the copy, or save it as a job for the CLI and the Windows Task Scheduler.

Moving data between Oracle and SQL Server

dbSlice copies Oracle data into SQL Server tables. Microsoft SSMA converts the schema and the PL/SQL code; see how the two fit in SSMA alternative. For Oracle to PostgreSQL, see Oracle to PostgreSQL and the nightly routine in Sync Oracle to PostgreSQL. From Firebird? See Firebird to SQL Server.

FAQ

Does dbSlice convert PL/SQL packages, triggers or views?

No. It moves data; PL/SQL code has to be rewritten in T-SQL, by hand or with SSMA.

Do I need GoldenGate, LogMiner or supplemental logging on Oracle?

No. dbSlice reads the tables with ordinary queries.

Which Oracle versions?

Oracle 12c or later, because paging uses FETCH FIRST.

Are deletes copied by incremental runs?

No. Run Compare or a reload before the switch.

Is there a free version?

Yes, on the Microsoft Store: it connects, browses, compares and exports to CSV. Writing to a target needs a Desktop or Server license.

Try it on your own data: the free Microsoft Store version needs no license key.

Try free on the Microsoft Store ↗Buy dbSlice

Related guides