Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Tools and technique to move SQL Server data to Oracle

Once you have the necessary tools to create a Data Source Name (DSN) which provides the Oracle connectivity details, you can specify Oracle as the destination in your DTS package and start moving.

I want to move 100G SQL Server 6.5 data to Oracle 9i r2. Data Transformation Services (DTS) looks like it is as fast as we thought. Moving 1M rows data for a 13 column table will take hours. What is the best tool to do this? I want to move 100 GB of SQL Server 6.5 data to Oracle 9i R2. Data Transformation Services (DTS) looks like it is as fast as we thought: Moving 1 MB of row data for a 13-column table will take hours. What is the best tool to do this?
First, I'm going to assume that you are able to use Data Transformation Service (DTS) under 7.0 or SQL Server 2000 even though your source data resides on SQL Server 6.5. (of course DTS doesn't exist in 6.5 instead we relied on BCP). Also, I'm assuming that you have the required Oracle ODBC driver so you can create a Data Source Name (DSN) which provides the Oracle connectivity details. Once you have this you can specify Oracle as the destination in your DTS package. Here are my recommendations for your DTS package.

You may consider partitioning the data. Instead of having a single data pump in your DTS package, have 5, 6 or...

7 data pumps that can run in parallel. Look at the source data column and see if there is a way to horizontally partition the data based on a source column. If your source data includes a date time column, this may be an ideal candidate. See if you can partition the data by the year quarter, months, etc. I've also seen data partitioned based on geographical regions.

Design your package so it executes these multiple transfers (data pumps) in parallel. When you execute the package while in design mode the tasks that are currently executing will be identified by a green execute arrow and a row count will be displayed. Your goal should be to have all data pumps run in parallel.

Experiment with moving subsets of data, in which you transfer the data from SQL Server to an intermediary text file then to Oracle. You may see that rows can be transferred much faster using a file that sits between the SQL source and Oracle destination.

This was last published in May 2006

Dig Deeper on SQL Server Interoperability

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close