How to Achieve SQL Server to Oracle Database Migration [FREE]

• Press F5 to execute the script. A message indicating “User MWREP created” confirms completion.

Step 2: Create Migration Repository

• Right-click Connections→ Select New Connection to configure a connection for the MWREP user:
  • Connection Name: mwrep_orcl(customizable)
  • Username/Password: mwrep / mwrep(check “Save Password”)
  • Other configurations are the same as system_orcl(consistent hostname/port/SID).
• Click “Connect” after the connection test is successful.
• Right-click the mwrep_orcl connection → Select Migration Repository > Associate Migration Repository → Click “OK” after the setup is complete (automatically creates migration tables and PL/SQL packages).

Step 3: Capture SQL Server Exported Files (Offline Mode, Suitable for Large Files)

• First, export SQL Server data via SSMS (refer to the essence of the first article):
  • Open SSMS → Right-click the target database → Select Tasks > Export Data.
  • Data Source: Select “SQL Server Native Client”; Destination: Select “Flat File Destination”.
  • Check the tables/views to be exported → Complete the wizard to generate a flat file, ultimately obtaining ocp(xxxx is the version, e.g., 2005).
• Load the file in Oracle SQL Developer:
  • Path: Migration > Third-Party Database Offline Capture > Load Database Capture Script Output.
  • Browse to the extracted mssqlservermigrationdirectory and select ocp.
  • Expand Captured Models > SQLServerxxxx > dboto confirm that tables, views, and other objects are captured.

Step 4: Configure Migration Preferences (Avoid Syntax/Identifier Conflicts)

• Path: Tools > Preferences > Migration > Identifier Options.
• Mandatorily check “Is Quoted Identifier On” (resolves differences in quote rules between SQL Server and Oracle).
• Switch to the Migration > Capture/Convert/Generatetab, keep the default configurations (or fine-tune according to business needs), and click “OK”.

Step 5: Convert to Oracle-Compatible Model (Automatic Data Type Mapping)

• Right-click the captured SQLServerxxxxunder Captured Models → Select Convert to Oracle Model.

• In the Set Data Map window, review the data type mapping (e.g., SQL Server’s int → Oracle’s NUMBER), and click “Apply” after confirmation.
• Expand Converted Models> Converted:SQLServerxxxx > dbo Northwind to verify the converted tables, stored procedures, and other objects (mark complex objects for manual repair).

Step 6: Generate and Execute Oracle Database Object Scripts

• Right-click Converted:SQLServerxxxx → Select Generate to automatically create DDL scripts (for creating tables, indexes, constraints, etc.).
• In the script panel, select the system_orcl connection from the drop-down menu.
• Press F5 to execute the script. After a successful execution, create a new business user connection:
  • Right-click Connections → Select New Connection; set the connection name to dbo Northwind-migrated orcl.
  • Username/Password: dbo Northwind / [corresponding password]; other configurations are the same as before.
  • After the connection test is successful, expand Tables to confirm that the table structure is created (no data yet).

Step 7: Configure Offline Data Movement Preferences

• Path: Tools > Preferences > Migration > Data Move Options.
• Configure the following fixed formats (essence of the third article to ensure date/time compatibility):
  • Date Mask: Mon dd yyyy HH:mi:ssAM (e.g., “Jan 01 2025 12:00:00AM”)
  • Timestamp Mask: Mon dd yyyy HH:mi:ss:ff3AM (accurate to milliseconds)
• Click “OK” to save the configuration.

Step 8: Analysis and Estimation

• Generate Migration Report: Go to Migration Reports → View Migration Summary (success/failure statistics), Migration Details (specific object status), and Automatic Name Changes (name change records) respectively.
• Estimate Manual Tasks:
  • Repair complex objects that failed automatic conversion (e.g., nested stored procedures, custom functions).
  • Verify the impact of name changes on applications (e.g., field name truncation).
  • Reserve 1–2 working days (estimated based on 100 complex objects).

Step 9: Import Data

• Open Command Prompt → Navigate to the data file directory: cd <mssqlservermigration extraction path>/Data.
• Execute the batch file: oracle_ctl(internally uses Oracle’s sqlldr tool to load data).
• Return to Oracle SQL Developer → Refresh the dbo Northwind-migrated orcl

Step 10: Comprehensive Testing and Deployment

• Structure Verification: Confirm that tables, indexes, foreign keys, views, and other objects are complete and consistent with the source database.
• Data Verification: Sampling and comparing key fields (e.g., order amount, user ID) to ensure no loss or corruption.
• Security Verification: Verify that the dbo Northwind user has normal permissions (read/write/execution) and that access control (e.g., IP whitelist) is functioning.
• Application Testing: Launch business applications and test CRUD operations (e.g., order submission, data query) for compatibility with Oracle.
• Performance Testing: Identify query delays and concurrency bottlenecks (refer to Article 1) and optimize Oracle parameters (e.g., memory allocation).
• Deployment and Launch: After passing all tests, switch business traffic to the new Oracle database and monitor stability for 24 hours.

Method 2: Migrate SQL Server to Oracle with i2Stream [Hot]

Oracle SQL Developer is suitable for small-scale, offline migrations with simple structures. It is free but has limited functionality.

If you need a smooth and seamless migration from SQL Server to Oracle, I recommend trying i2Stream from information2 Software.

Designed for enterprise-level production environments, it addresses core challenges such as zero downtime, real-time processing, heterogeneous compatibility, and high-volume/low-bandwidth scenarios. It provides end-to-end migration and continuous data protection capabilities, making it ideal for scenarios demanding high business continuity and data consistency.

• Seamless Heterogeneous Compatibility

Covers full-stack cross-platform deployment (Windows/Linux, x86/ARM) and supports all versions of SQL Server & Oracle (10g-19c, including CDB/PDB). Enables hybrid cloud bidirectional sync, eliminating OS/database version barriers.

• Non-Disruptive Migration & Fast Sync

Combines full initial sync + real-time incremental replication—no downtime, table locking, or business interruption. Multi-threaded concurrency and 5+ levels of data compression cut through large datasets and bandwidth constraints.

• Complete Data Integrity

Syncs DDL/DML operations (table creation, column modification, data CRUD) without limitations. Built-in real-time data consistency checks prevent loss or discrepancies between SQL Server and Oracle.

• Intuitive Monitoring & Alerts

Web-based dashboard tracks sync progress, latency, CPU/memory usage, and network traffic. Customizable second-level delay alerts and multi-channel notifications (email/SMS) ensure migration visibility.

• Lightweight & Flexible Deployment

Agent-free setup with low resource consumption on the SQL Server source. Supports multi-task concurrency on a single node, adapting to on-premises, cloud, or cross-region migration scenarios.

You can watch this demo to learn the detailed steps for migrating databases with i2Stream. If you’d like more information, please contact us.

Oracle SQL Developer vs. i2Stream: Which Should You Choose?

Having detailed both migration methods, it’s important to compare them to determine which aligns best with your business scenario. The table below summarizes the core differences:

Factor	Oracle SQL Developer	i2Stream
Best For	Small/mid-sized businesses, simple migrations, limited budgets	Enterprises, large-scale migrations, minimal downtime, mission-critical data
Automation	Partial (manual scripting for complex objects)	Full (zero-code, end-to-end automation)
Downtime	Moderate (requires planned downtime)	Minimal (CDC support for real-time sync)
Support	Community forums, limited Oracle support	24/7 enterprise support from Information2 Software
Complexity	Steeper learning curve (requires Oracle/SQL expertise)	User-friendly (intuitive UI, no coding required)

Conclusion

Choosing the right migration method is the key to export data from SQL Server to Oracle Database. For small to mid-sized businesses with limited budgets, Oracle SQL Developer offers a free, reliable solution—provided you have the technical expertise to handle manual adjustments. For enterprises prioritizing minimal downtime, automation, and enterprise support, Information2 Software is the optimal choice, delivering end-to-end migration with zero code and 100% data integrity.

Ready to start your migration? Request a free trial of i2Stream to experience automated SQL Server to Oracle migration, or download Oracle SQL Developer to begin with the free method. Whichever tool you choose, following the pre-migration checklist and post-migration testing steps will ensure a smooth and successful transition.