We need to transfer data from a SQL Server 7 database to an Oracle database on a daily basis. The solution I can think about is to generate text files from SQL Server database, and then load the text file into Oracle using SQL*Loader. I wonder can Oracle query the SQL Server database directly, or are there other ways of doing this?

We also need to take some data from Oracle database and update the SQL Server database. My plan again is to do it through text files. Any other better ways?

It was eye opening. I'll be very much pleased if it works in our environment.

Tom,

If suppose any one wants to migrate the entire stuff from Oracle to SQLserver, how this can be achieved? Do we have any such utilities exist. My concern is in SQLSERVER no packages functionalities exist, if in such case of migrations, how those utilities would be doing this kind of stuff without fail?

Thanks
Kumar

oracle to sqlserver? why would oracle provide that?
why would anyone want to do that? why would tom even consider looking into that for you?

Yes go askbill.com [have to pay $$ thought?].. maybe buy a askbill support licence from microsoft- :)

Hi Folks,

I agree your point, why Oracle do that -- correct? In reality Oracle do a lot like whenver a new windows OS then Oracle releases happens on them. So this is the world of TECH. I am curious to ask this, what if a big Oracle shop ( like the one I have) might need some small other databases interfaces with SQLSERVER. In this kind of world there is no end boundaries for conversions, migrations etc. Hence I just posted if any information exist with any clue like things. However no way to think nor make the buisness down to Oracle.

Thnaks for suggestions and I am just investigating if any such gateways exist. I am also sure Bill.com did not setup any gate ways till date however some useful info published. I know this causes $$ to do this untill Bill.com emerges one day with some key.

You need to look at the tools that come with sqlserver. Database system software would generally provide "inbound" migrations (i.e. mirgate *to* my rdbms), but not "outbount" migrations (i.e. migrate *from* my rdbms). Therefore you should really explore what sqlserver (the target database you want to migrate to) has to offer.

I have been asked to migrate SQL server database to oracle 9i. I am thinking about using the migration workbench tool. I am posting in this thread to see if anyone had used this utility to do the migration before and if there are any issues I may face and should be considered in performing the migration, it would be appreciated if the readers or you could share. Thanks.

Tom,

My Database serevr version is 10.1.0.3.0.

Is it possible to import data from sql server to oracle database?

Also other way around.

I mean import data from Oracle to sql server.

Regards,
Dawar

Dear Mr. Kyte

I am having a Database which was being run and managed on MS SQL server, but now we are required to migrate the same to Oracle 9i Server.

Can you pls render your valuable advice and instructions, as if, how this task can be achieved easily in the most effective manner? I want to migrate all my applications, triggers, stored procedures etc, along with my Database.

Pls suggest me which is the right way and tool (Import/Export, Sql *Loader, Workbench)to achieve this task using ODBC specifications.

How can i minimise the in-compatibilities that will arise due to Datatype mismatch.

What care should i take while handling the Null and Blank records in the various feilds of my SQL Database while migrating the it to Oracle 9i Database.

Thanx with kindest regards,
Vikas

Dear Mr. Kyte,

Thanks for the above information.

Certainly, i would like to use OMWB. But, now the question arises while capturing the database, OMWB creates two Models of the database that is to be migrated, one the source model(MS SQL) and another is the target model(oracle 9i).

What i would like to know here is, Will it be better to make changes (incompatibilities/error corrections)to the source model or target model before migrating the Database or, it is better to migrate the Database first and then make the neccessary changes.

Are there any tools that can achieve, or help in achieving this (Code correction)task.

Would Jdeveloper work to achieve this purpose or is there any other tool that can be used for the same task.

Thanx with regards...
Vikas.

Dear Mr. Kyte

Thanx for the prompt reply.

I'll certainly keep your valuable suggestions as top priorities in mind, and make them handy when the need arises and circuimstances allows me to do so. At the moment we are carrying out things safely on trial basis using dummy Database, so as to minimise problems in near future at the time when things get realize.

This is just to get an idea of how it is actually done in Oracle and who else is better than you, to to look up for when it comes to Oacle and Databases. I have recently completed my oracle education (9i DBA - although, learning is a never ending process) but that was very basic of what we actually do in real life.

So i'll keep bothering you and hope to get the valuable peice of knowlegde on my queries from you as u always do.

Take care, with regards....
Vikas.

Hi Tom

I would like to know whether migrating the
database(all objects and data as well) from Oracle 9i to Oracle 10g is possible or not? If so pl list the tools available.
Does Oracle own any GUI based tool like Microsoft-DTS to Extract & transform data from one Oracle DB to other Oracle DB ?

Thanks
Banumaran

Hi Tom,

I do agree with you in scenarious where there are no structural changes.

what is your advice if there are structural changes in targer db.
i.e) The source DB (oracle 9i) is not properly normalized , the data from source is moved to a Normalized 10g DB with new attributes added(based on new requirements)to these tables . Some tables from source are split into 2 or more tables in the target (oracle 10g) database.

i have scenarious like

1) A single row in SOURCE be migrated to multiple rows across one or more tables in TARGET.
2) A multiple rows in single column in SOURCE be migrated to single row and multiple columns in TARGET

For the above mentioned scenario sql*loader /external table/export & import or transportable tablespaces can be used,but considering the ease of use and the complexity what GUI tool does oracle have for data migration from oracle db to oracle db.

TOM, with Microsoft DTS we are able to migrate from one Oracle DB to other oracle DB, though it is not a recommended approach i had just tried a sample.

i am looking for an ORACLE tool which is similar to Microsoft-DTS which should be very easy to use.


your advice will be much help to me.

Hi tom,

Sorry to ask you this question here. I hope you can help me.

We've developed a package(pkg_1) on oracle 8.1.7.4.

We've another application developed in microsoft Access and wich connect to another oracle database 9i. Within this 9i session we want the connected user to connet to our oracle 8.1.7.4 and calls our package (pkg_1).

This means that this user is first connected to his 9i database and
1. will connect to our 8i database
2. call our package pkg_1 ===> which gives him output parameters

3. disconnet from our 8i database
4. use the output parameters in his oracle 9i application

Is this possible?

Kind Regards

Hi Tom
We are migrating SQL Sever Database to Oracle10g.
Can you tell me. What equivalent of SQL Server timestamp datatype in Oracle. Or how I can
implement similar behavior in 10g.

Who'd've guessed?! SQL Server 2000 "introduces a rowversion synonym for the timestamp data type"
http://tinyurl.com/ofh7tg (link to MSDN)

Thanks Tom..

Followup   June 3, 2009 - 12pm US/Eastern:
your choices are:
http://www.oracle.com/pls/db102/search?remark=quick_search&word=ora_rowscn&tab_id=&format=ranked

This link doesn't work for me... 

Ok... When i use rowdependencies it gave error ..

SQL> select ORA_ROWSCN,USERID,USERNAME from aduser;

ORA_ROWSCN     USERID USERNAME
---------- ---------- --------------------------------------------------
   3348654          1 sysman
   6089889          2 ANWER

SQL> 
SQL> 
SQL> SELECT scn_to_timestamp(ORA_ROWSCN),USERID,USERNAME from aduser where USERID = 2;

SCN_TO_TIMESTAMP(ORA_ROWSCN)                                                    USERID USERNAME
--------------------------------------------------------------------------- ---------- -------------------
02-JUN-09 02.54.23.000000000 PM                                                      2 ANWER

SQL> 
SQL> SELECT scn_to_timestamp(ORA_ROWSCN),USERID,USERNAME from aduser where USERID = 1;
SELECT scn_to_timestamp(ORA_ROWSCN),USERID,USERNAME from aduser where USERID = 1
       *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1