How to Migrate a Microsoft SQL Server Database to Oracle Database 11g

Overview

What Is SQL Developer?

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using Oracle SQL Developer, you can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created.

Microsoft SQL Server Migration Overview

Using Oracle SQL Developer Migration Workbench, you can quickly migrate your third-party database to Oracle.

There are four main steps in the database migration process:

 

Capture the Source Database	The first step is to capture a “snapshot” of the Microsoft SQL Server database. This can be done in two ways. Online Capture: This requires creating a connection in SQL Developer to a live Microsoft SQL Server database. Using JDBC, the Microsoft SQL Server database metadata can be accessed and the Capture Model created. Offline Capture: This involves BCP scripts generated by SQL Developer to extract the Microsoft SQL Server database metadata to files. These files can then be “captured” by SQL Developer to create the Captured Model. The second method is what you will perform in this tutorial. Using SQL Developers Offline Capture feature, the Microsoft SQL Server Northwind sample database has been extracted into offline data files. The SQLServer2005.ocp file generated by the Capture tool contains the database schema information for the Microsoft SQL Server Northwind Traders database. Oracle SQL Developer Migration Workbench uses this file as the basis for building a representation of the structure of the source Microsoft SQL Server database. This structure is called the Captured Model.
Convert the Captured Database	Oracle SQL Developer Migration Workbench uses the Captured Model to convert the captured objects to Oracle-format objects, building up a representation of the structure of the destination database. This structure is called the Converted Model.
Generate the Oracle Database	Oracle SQL Developer Migration Workbench generates DDL statements to create the new Oracle database, based upon the Converted Model objects. Running the DDL statements will result in the creation of the objects in the Oracle database.
Migrate the Data	The last step in the process is to migrate the data. You can do this in one of two ways. Online Data Move: You can create a connection from within Oracle SQL Developer to your Microsoft SQL Server source database and migrate the data. Offline Data Move: You can export the data from Microsoft SQL Server. SQL Developer</font> will create a series of BCP and sqlldr files that you can run from a batch file. The second method is what you will perform in this tutorial.

In this tutorial, the required scripts for the offline migration have already been generated and modified. If you do not have time to perform this tutorial, you can also view the offline method, click here.

To view the steps for the online method, click here.

See link below

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/hol08/sqldev_migration/mssqlserver/migrate_microsoft_sqlserver_otn.htm

Enjoy

 

---