SQL Server → Oracle Database

📄 Overview

Migrating from SQL Server to Oracle Database is a deep cross-engine migration. Both are enterprise relational databases, but they differ significantly in dialect, object model, and runtime behavior. MigrationBridge automates the most critical conversion tasks while surfacing incompatibilities that require manual review.

Key Engine Differences

🔒 Prerequisites — Source (SQL Server)

The account used by MigrationBridge to connect to SQL Server requires the following minimum permissions. Never use sa or sysadmin unless no other option exists.

Required Permissions

• db_datareader on the source database (read all table data)
• VIEW SERVER STATE (required for DMV queries used in assessment)
• VIEW DATABASE STATE on the source database
• Network access: TCP port 1433 open from the MigrationBridge host to SQL Server
• ODBC Driver 17 (or 18) for SQL Server installed on the MigrationBridge host

Create Migration Login (SQL Server)

-- Run on SQL Server as sysadmin
USE [master];

CREATE LOGIN [mb_reader]
  WITH PASSWORD = N'Str0ng!PassMB2024',
       DEFAULT_DATABASE = [AdventureWorks2019],
       CHECK_EXPIRATION = OFF,
       CHECK_POLICY = ON;

USE [AdventureWorks2019];

CREATE USER [mb_reader] FOR LOGIN [mb_reader];
ALTER ROLE [db_datareader] ADD MEMBER [mb_reader];

USE [master];
GRANT VIEW SERVER STATE TO [mb_reader];
GRANT VIEW ANY DATABASE TO [mb_reader];

Verify Source Connectivity

-- Confirm login and access
SELECT SUSER_NAME() AS current_login,
       DB_NAME()    AS current_database,
       GETDATE()    AS server_time;

-- Confirm table read access
SELECT TOP 1 * FROM [Person].[Person];

-- Confirm VIEW SERVER STATE
SELECT session_id, status, login_name
FROM   sys.dm_exec_sessions
WHERE  is_user_process = 1;

Check for Unsupported Column Types

-- Identify hierarchyid, geography, geometry columns before migration
SELECT
    t.name           AS table_name,
    c.name           AS column_name,
    tp.name          AS data_type,
    c.column_id
FROM   sys.columns         c
JOIN   sys.tables          t  ON t.object_id = c.object_id
JOIN   sys.types           tp ON tp.user_type_id = c.user_type_id
WHERE  tp.name IN ('hierarchyid', 'geography', 'geometry', 'xml', 'sql_variant')
  AND  t.is_ms_shipped = 0
ORDER BY t.name, c.column_id;

ODBC Driver Check (Windows)

Get-OdbcDriver -Name "ODBC Driver*SQL Server*" | Select-Object Name, Platform

If no driver is listed, download and install ODBC Driver 17 for SQL Server from Microsoft's official download page before launching MigrationBridge.

🔧 Prerequisites — Target (Oracle Database)

Oracle Instant Client

MigrationBridge uses python-oracledb (formerly cx_Oracle) in Thick mode, which requires Oracle Instant Client libraries. Install Instant Client before running MigrationBridge.

# Download Oracle Instant Client Basic Package from:
# https://www.oracle.com/database/technologies/instant-client/downloads.html
# Extract to a stable path, e.g.:
#   Windows:  C:\oracle\instantclient_21_12
#   Linux:    /opt/oracle/instantclient_21_12

# Linux: add to LD_LIBRARY_PATH
export LD_LIBRARY_PATH=/opt/oracle/instantclient_21_12:$LD_LIBRARY_PATH

# Windows: add the folder to PATH via System Properties > Environment Variables

Install Python Driver

# Recommended: python-oracledb (cx_Oracle successor, Oracle-maintained)
pip install python-oracledb

# Legacy alternative (still supported by MigrationBridge):
pip install cx_Oracle

Test Oracle Connection via SQL*Plus

# SID-based connection
sqlplus mb_migrate/MigratePass2024!@//ora-host.example.com:1521/ORCL

# Service Name-based connection
sqlplus mb_migrate/MigratePass2024!@//ora-host.example.com:1521/ORCLPDB1

# Oracle Cloud (TLS / wallet)
sqlplus mb_migrate/MigratePass2024!@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=adb.us-ashburn-1.oraclecloud.com)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=abc123_high.adb.oraclecloud.com)))"

Create Migration User (Oracle)

-- Connect as DBA (SYSDBA or privileged user)
CREATE USER mb_migrate IDENTIFIED BY "MigratePass2024!"
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  QUOTA UNLIMITED ON USERS;

-- Minimum required privileges
GRANT CREATE SESSION   TO mb_migrate;
GRANT CREATE TABLE     TO mb_migrate;
GRANT CREATE SEQUENCE  TO mb_migrate;
GRANT CREATE TRIGGER   TO mb_migrate;
GRANT INSERT ANY TABLE TO mb_migrate;
GRANT DROP ANY TABLE   TO mb_migrate;

-- Allow gathering stats post-migration
GRANT EXECUTE ON DBMS_STATS TO mb_migrate;

Verify Oracle Network Connectivity

• TCP port 1521 (default listener) must be open from MigrationBridge host to Oracle host
• For Oracle Cloud ATP/ADW, port 1522 (TLS) and a wallet file are required
• For RDS for Oracle, the security group inbound rule must allow the MigrationBridge host IP on port 1521
• Test with tnsping or sqlplus before configuring MigrationBridge

# Test port reachability (Windows PowerShell)
Test-NetConnection -ComputerName ora-host.example.com -Port 1521

# Linux
nc -zv ora-host.example.com 1521

# tnsping (requires Oracle client)
tnsping //ora-host.example.com:1521/ORCL

Open MigrationBridge and navigate to the Connections tab. Configure both the source (SQL Server) and the target (Oracle) connection profiles.

Connection String Formats

Click Run Assessment in MigrationBridge. The tool scans all tables in the source database and reports compatibility warnings. No data is moved during this phase.

Auto-Handled Conversions

MigrationBridge silently converts the following without user intervention:

Columns That Are Skipped (WARNING)

Assessment Output Sample

Navigate to the Migration tab. Select the tables to migrate and configure the sync mode.

Click Start Migration. MigrationBridge processes tables in dependency order (parents before children), creates sequences for IDENTITY columns, loads all data, then applies foreign key constraints.

What Happens Internally

1. Schema introspection: reads all column definitions, constraints, indexes from SQL Server system catalogs
2. DDL generation: produces CREATE TABLE statements in Oracle syntax with mapped types
3. SEQUENCE generation: one CREATE SEQUENCE per IDENTITY column, with matching seed and increment
4. Trigger generation: BEFORE INSERT trigger to populate IDENTITY column from sequence (Oracle 12.1 compatibility mode)
5. Data transfer: bulk INSERT in batches of 1,000 rows; LOB columns streamed separately
6. FK creation: all foreign keys applied after all tables are loaded (avoids ordering issues)
7. Row count validation: compares source and target row counts per table

Migration Log — AdventureWorks2019 Sample

SEQUENCE Creation SQL (Oracle Syntax)

-- Example: auto-generated sequence for HumanResources.Department.DepartmentID (IDENTITY(1,1))
CREATE SEQUENCE MB_SEQ_DEPARTMENT_DEPARTMENTID
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  NOCACHE
  NOCYCLE;

-- Companion BEFORE INSERT trigger (Oracle 12.1 / 12.2 compat)
CREATE OR REPLACE TRIGGER TRG_DEPARTMENT_DEPARTMENTID_BI
  BEFORE INSERT ON "DEPARTMENT"
  FOR EACH ROW
  WHEN (NEW."DepartmentID" IS NULL)
BEGIN
  :NEW."DepartmentID" := MB_SEQ_DEPARTMENT_DEPARTMENTID.NEXTVAL;
END;
/

After migration completes, run the following verification queries directly against the Oracle target database to confirm row counts, foreign keys, and data integrity.

Step 5a: Gather Fresh Statistics (Required for Accurate Row Counts)

-- Gather stats so USER_TABLES.NUM_ROWS is populated accurately
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname          => 'MB_MIGRATE',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    cascade          => TRUE,
    degree           => 4
  );
END;
/

Step 5b: Row Counts per Table

-- Query USER_TABLES for row counts (after stats gather)
SELECT
    table_name,
    num_rows,
    last_analyzed
FROM   user_tables
ORDER BY table_name;

-- More accurate live count (slower on large tables):
SELECT 'DEPARTMENT'        AS table_name, COUNT(*) AS row_count FROM "DEPARTMENT"        UNION ALL
SELECT 'EMPLOYEE'          AS table_name, COUNT(*) AS row_count FROM "EMPLOYEE"          UNION ALL
SELECT 'SALESORDERDETAIL'  AS table_name, COUNT(*) AS row_count FROM "SALESORDERDETAIL"  UNION ALL
SELECT 'PRODUCT'           AS table_name, COUNT(*) AS row_count FROM "PRODUCT"
ORDER BY 1;

Step 5c: Foreign Key Validation

-- List all FKs in the migrated schema
SELECT
    c.constraint_name,
    c.table_name,
    cc.column_name,
    c.r_constraint_name,
    c.status
FROM   user_constraints  c
JOIN   user_cons_columns cc ON cc.constraint_name = c.constraint_name
WHERE  c.constraint_type = 'R'
ORDER BY c.table_name, c.constraint_name;

-- Count: should equal 90 for AdventureWorks2019
SELECT COUNT(*) AS fk_count
FROM   user_constraints
WHERE  constraint_type = 'R';

-- Check for DISABLED constraints (should be 0)
SELECT constraint_name, table_name, status
FROM   user_constraints
WHERE  constraint_type = 'R'
  AND  status = 'DISABLED';

Step 5d: Spot-Check Data Samples

-- Verify a representative sample of data
SELECT * FROM "PRODUCT"     WHERE ROWNUM <= 5;
SELECT * FROM "EMPLOYEE"    WHERE ROWNUM <= 5;
SELECT * FROM "SALESORDER"  WHERE ROWNUM <= 5;

-- Confirm no NULLs in non-nullable columns (sample check)
SELECT COUNT(*) AS null_business_entity_id
FROM   "EMPLOYEE"
WHERE  "BusinessEntityID" IS NULL;

After verifying row counts and FK integrity, complete the following Oracle-specific post-migration tasks before directing application traffic to the new target.

6a: Gather Schema Statistics

-- Full schema stats with histogram generation (run as mb_migrate or DBA)
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname          => 'MB_MIGRATE',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,
    degree           => 4,
    no_invalidate    => FALSE
  );
END;
/

6b: Rebuild Indexes

-- List indexes in the migrated schema
SELECT index_name, table_name, status, blevel, leaf_blocks
FROM   user_indexes
ORDER BY table_name, index_name;

-- Rebuild any indexes with status = 'UNUSABLE'
BEGIN
  FOR idx IN (
    SELECT index_name FROM user_indexes WHERE status = 'UNUSABLE'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.index_name || ' REBUILD ONLINE';
  END LOOP;
END;
/

6c: Validate Sequences

-- Check all sequences and their current LAST_NUMBER
SELECT
    sequence_name,
    min_value,
    max_value,
    increment_by,
    last_number,
    cache_size,
    cycle_flag
FROM   user_sequences
ORDER BY sequence_name;

-- Advance each sequence to match the current MAX identity value in the target table
-- Example: DepartmentID max = 16 after migration; set sequence past 16
DECLARE
  v_max NUMBER;
  v_cur NUMBER;
  v_diff NUMBER;
BEGIN
  SELECT MAX("DepartmentID") INTO v_max FROM "DEPARTMENT";
  SELECT last_number INTO v_cur
  FROM   user_sequences
  WHERE  sequence_name = 'MB_SEQ_DEPARTMENT_DEPARTMENTID';

  v_diff := v_max - v_cur + 1;

  IF v_diff > 0 THEN
    EXECUTE IMMEDIATE
      'ALTER SEQUENCE MB_SEQ_DEPARTMENT_DEPARTMENTID INCREMENT BY ' || v_diff;
    SELECT MB_SEQ_DEPARTMENT_DEPARTMENTID.NEXTVAL INTO v_cur FROM DUAL;
    EXECUTE IMMEDIATE
      'ALTER SEQUENCE MB_SEQ_DEPARTMENT_DEPARTMENTID INCREMENT BY 1';
  END IF;
END;
/

6d: Create Synonyms (if Schema Name Differs)

If the application connects as a different Oracle user than mb_migrate, create public or private synonyms so that the application user can access the migrated tables without qualifying them with the schema name.

-- Connect as DBA
-- Create public synonyms for all tables in mb_migrate schema
BEGIN
  FOR t IN (
    SELECT table_name FROM all_tables WHERE owner = 'MB_MIGRATE'
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'CREATE OR REPLACE PUBLIC SYNONYM ' || t.table_name ||
        ' FOR MB_MIGRATE.' || t.table_name;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Failed: ' || t.table_name || ' — ' || SQLERRM);
    END;
  END LOOP;
END;
/

6e: Grant Application User Access

-- Grant select/insert/update/delete to the application schema user
BEGIN
  FOR t IN (
    SELECT table_name FROM all_tables WHERE owner = 'MB_MIGRATE'
  ) LOOP
    EXECUTE IMMEDIATE
      'GRANT SELECT, INSERT, UPDATE, DELETE ON MB_MIGRATE.' ||
      t.table_name || ' TO APP_USER';
  END LOOP;
END;
/

6f: Final Checklist

🔄 Type Conversion Map

Complete mapping of SQL Server data types to Oracle equivalents as applied by MigrationBridge v1.3.0.

⚠ Known Issues & Compatibility Notes

1. Oracle Reserved Words as Column Names

Oracle reserves words like DATE, LEVEL, ROWNUM, COMMENT, SIZE, OPTION, RESOURCE, SELECT. If a SQL Server column uses any of these names, MigrationBridge automatically wraps the identifier in double-quotes in all generated DDL and DML. The column is usable in Oracle but must always be quoted in queries.

2. Oracle Identifier Case Sensitivity

Oracle stores unquoted identifiers in UPPERCASE. MigrationBridge preserves original SQL Server column/table names using double-quoted identifiers. This means:

• All generated DDL uses double-quoted identifiers: "ProductID", not PRODUCTID
• Application queries must use the exact case and always quote identifiers
• ORM frameworks (Hibernate, SQLAlchemy) must be configured for case-sensitive identifiers
• Alternative: set nls_comp=LINGUISTIC and nls_sort=BINARY_CI for session-level case-insensitivity

3. Maximum Identifier Length (Oracle 12.1)

Oracle 12.1 and earlier enforce a 30-character maximum on object names (tables, columns, indexes, constraints, sequences). Oracle 12.2+ raised this to 128 characters.

• MigrationBridge checks all identifier lengths at assessment time
• For Oracle 12.1 targets: names over 30 chars are truncated to 26 chars and suffixed with a 4-char hash (e.g., SomeLongColumnNameHere → SomeLongColumnNameHe_a3f2)
• A WARNING is logged for each truncated name; the mapping is recorded in the migration report
• Verify the Oracle compatibility version before migration: SELECT * FROM v$compatibility;

4. Sequence Gaps After Reseed

If sequences were advanced during migration (due to retries or partial runs), the sequence LAST_NUMBER may be higher than the actual data maximum. This results in gaps in IDENTITY values after the migration. Gaps are generally harmless for surrogate keys but may affect applications that assume contiguous IDs.

5. MERGE Statement Behavior Differences

SQL Server's MERGE and Oracle's MERGE have syntax and behavioral differences:

• SQL Server allows MERGE ... OUTPUT; Oracle does not support OUTPUT in MERGE
• Oracle MERGE requires a semicolon terminator after the WHEN clauses
• SQL Server MERGE locks can differ from Oracle — review transaction isolation levels
• MigrationBridge does not convert stored procedures; all MERGE logic must be manually rewritten

6. NULL Handling Differences

Oracle treats an empty string ('') as NULL. SQL Server distinguishes between NULL and ''. If your SQL Server data contains empty strings in VARCHAR columns, they will become NULL in Oracle.

7. NOLOCK / READ UNCOMMITTED Hints

SQL Server's WITH (NOLOCK) and READ UNCOMMITTED have no Oracle equivalent. Oracle's multi-version read consistency provides consistent reads by default without dirty reads. All T-SQL query hints must be removed from application queries when ported to Oracle.

8. TOP vs ROWNUM / FETCH FIRST

SQL Server's SELECT TOP n must be rewritten as Oracle's WHERE ROWNUM <= n (12.1) or FETCH FIRST n ROWS ONLY (12.2+). MigrationBridge does not rewrite application queries; this is a manual task.

9. RDS for Oracle Limitations

• No SYSDBA connections from external clients — use master user for DBA operations
• RMAN backup/restore controlled by AWS; cannot run RMAN directly
• Oracle Multitenant (PDB/CDB) architecture used; always connect to the PDB, not the CDB root
• Some DBMS_ packages restricted; check RDS documentation for supported packages

📺 UI Behavior Notes

During an active migration, MigrationBridge displays a progress panel in the Migration tab. The following behaviors are expected and should not be interrupted:

Expected UI Behaviors During Migration

• UI may become briefly unresponsive during LOB column transfers (CLOB/BLOB). This is normal — the Python process is streaming large objects. Wait for the progress indicator to resume.
• Log panel auto-scrolls to the latest entry. You can pause scrolling by clicking in the log area.
• The Cancel button stops the migration after the current table batch completes. Partially migrated tables are dropped from the target. A clean retry can be performed from the beginning.
• Do not close the application window during an active migration. Closing the window terminates the Python process and leaves the target schema in a partially migrated state.
• Window title updates to show the current table being processed. Use this to confirm the migration is still progressing if the log panel appears to stall.
• FK creation phase (after all tables are loaded) shows no per-row progress — this is expected. The log panel will show each FK being created. For 90 FKs this takes approximately 5–15 seconds on a local Oracle instance.