Migration is the process of copying the schema objects and data from a non-Oracle database, such as MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, or IBM DB2, to an Oracle database.
To migrate a third-party database to Oracle easily, you can choose the following options using SQL Developer:
– Migrating using the Migration Wizard
– Copying tables to Oracle
Migrating Using the Migration Wizard
The Migration wizard provides a screen to manage all the steps needed for the migration to Oracle database. These steps are as follows:
– Capturing the source database (MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, IBM DB2),
– Converting it to Oracle format,
– Generating DDL,
– Performing the conversion.
After a brief explanation about the process of migration, let’s do a simple migration from SQL Server database to Oracle database.
1- I created sample SQL Server database named TALIPTEST.
2- The migration repository is a collection of schema objects that SQL Developer uses to manage metadata for migrations. For a migration repository create a database connection to convenient Oracle database and give following grants.
CREATE USER MIGRATIONS IDENTIFIED BY “migration”
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
grant create session to migrations;
grant resource to migrations;
grant create view to migrations;
For multischema migrations, you must grant the privileges with the ADMIN option as follows.
grant resource to migrations with admin option;
grant create role to migrations with admin option;
grant alter any trigger to migrations with admin option;
grant create user to migrations with admin option;
3- Download SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
4- Unzip the file named sqldeveloper-3.2.20.09.87.zip and extract it. Open sqldeveloper.exe file to open SQL Developer.
5- Right click to the “Connections” and click the “New Connections”.
6- Create a database connection named Migration_Repository that connects to the MIGRATIONS user.
7- Right-click the Migration_Repository connection, and select “Migration Repository” menu , then “Associate Migration Repository” to create the repository.
8- Schema objects needed for migration will be created in MIGRATIONS schema.
9- To connect to third-party database (MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, IBM DB2) using SQL Developer, we need jTDS driver. You can download needed jTDS driver from following link. http://sourceforge.net/projects/jtds/files/jtds/1.2/jtds-1.2-dist.zip/download . Extract the dowloaded zip file named jtds-1.2-dist.zip
10- Click “Tools” and then “Prefenrences”.
11- Select “Third Party JDBC Drivers” and click “Add Entry” button to add jTDS driver for SQL Server.
12- Select jar file located in jTDS driver folder.
13- Click “OK” button.
14- Now you can connect to SQL Server or Sybase database using SQL Developer. Connect to the SQL Server database as follows.
15- And finally, let us create the database connection which we will migrate to.
16- The migration wizard is invoked in a variety of contexts. You can right-click a third-party database connection and select “Migrate to Oracle” or you can click “Tools>Migration>Migrate…”.
17- Click “Next” button.
18- Select migration repository database connection.
19- Give a name for migration project and select a directory to write output.
20– Select third party database connection. You can migrate database on online or offline mode. If you choose online migration then Migration Wizard will perform all needed operations. If you choose offline migration then Migration Wizard will generate all needed DDL scripts.
21- Select SQL Server database which we want to migrate to Oracle.
22- Specify the conversion options. And click “Advanced Options” link to ensure “Microsoft SQL Server : Is quoted identifier on” option is selected.
23- Select target database connection.
24- If you select offline migration then offline migration script will be generated in the project output directory.
25- Select the connections to be used for online data move.
26- Click “Finish” button so start migration.
27- Migration and conversion process will be perfomed as follows.
Now, Our SQL Server database in Oracle 🙂
Copying tables to Oracle
Right click the table located in third party database and click “Copy To Oracle”
Select destination (Oracle) database connection and click “Apply” button. If you choose “Include Data” check box it will create table structure and move data.
Copy to Oracle database is finished.
Note: This method doesn’t move indexes, triggers, etc. It only moves table structure and data.
Talip Hakan Öztürk
20 thoughts on “Migration From SQL Server To Oracle Using SQL Developer”
To migrate tables and the data in tables it’s ok but it doesn’t migrate the indexes and the foreign keys.
And how can we migrate views as well?
Thanks once again,
Hi, follwoed evry step one sfter another but at “capture” step sql developer throws error . “java.lang.Exception: The plugin used to capture this model is not available. Please re-install the plugin and try again before convert.
Please help…I am banging my head for last 3 days….Please sir…help me..
Did you installed jtds driver? Please folow Jay’s steps above.
Good post.. thank you.
Where can i get/see jtds-1.2.jar? is only this Jar file enough to get the SQL server DB over to Oracle?
To me, It worked only when I did this:
1 – Download and open the JTDS folder.
2 – The ntlmauth.dll you will find inside the x86/SSO folder
3 – The JtdsXA.dll you will find inside the x86/XA folder
4 – Put the ntlmauth.ddl and JtdsXA.dll inside de Sql Developer/sqldeveloper/bin
5 – Copy the jtds-1.2.4.jar to Sql Developer/sqldeveloper/lib
This worked for me.
Hi, is there anyway to move indexes, triggers.. etc..?
“Copy To Oracle” method doesn’t move indexes, triggers, etc. It only moves table structure and data. For Triggers you must migrate complete database.
Thanks a lot !!!!!!! 🙂 🙂 🙂
Thank you very much , superb post
when i did copy to oracle with include data check box checked also it only create the structure not the data any suggestion??
Plase check the “Append” box and try again. Is there any pk/fk constraint?
Kept getting this error…
SQL Error on Script Execution
Try deleting repository before Creating Repository
Finally found I also had to “grant create materialized view” to create the repository.
Thx dude, you saved my day 🙂
thx a lot *
It is too complex! I suggest use ESF Database Migration Toolkit, you can migrate SQL Server table data/indexes/foreign keys to Oracle or back in 3 steps without any SQL script.
it helped me lot..Thank you
Thank you they forgot the last step in the oracle tutorial for migration it helped me a lot
Many thanks for posting out the steps with all the details.
Thanks! Very helpful.
Alternatively, you can try migration tools from DBConvert for such kind of tasks.
DBConvert initially converts database schemas, tables, views, and relations from Oracle to SQL Server.
DBSync application keeps databases in Sync with Update, Insert, and Drop synchronization.
Also, you can configure DBSync to perform bidirectional replication. In this case, Oracle and SQL Server simultaneously replicate changes from each other.