DTS transfer database from SQL SERVER to ORACLE
-
Can any one tell us what is the best process to transfer the database from SQL 2000 to Oracle. The size of database is about 22GB(34 tables , 14 tables has appx. 5 million records)
venkatesh
venkatesh -
I'd say DTS, script a Transform Data Task for each table.
BCP might work, but not sure how hard this is to setup the export from Oracle.
Steve Jones
-
What you mean Oracle doesn't provide easy to use tools to migrate from SQL Server to their DBMS? I'm surprised. Seriously. I figured it would be a two way street.
Once again, go MS!
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
22g is not bad, I'd say DTS. Just do some testing to figure out the throughput - you'll probably have to adjust batch size to get best results. We'd really appreciate it if you'd post comments about whatever technique you do use, why, and how it turned out!
Andy
-
Hi Andy,
I got you, but i am new to this SQL environment, what is batch size, let me know how to set the batch size and if you have any other parameters to set in DTS to convert the database to oracle
venkatesh
venkatesh -
It is a property you can set in DTS - basically it'll be faster to move 10000 rows per batch than 1000 in most cases, keep in mind that you have to log all those transactions, so don't set your batch size higher than 10k without checking free disk space and usage during a test run.
Andy
-
hi Andy, Thanks for your prompt replay, But i have a problem. I am trying to convert the database from SQL SERVER to ORACLE, I think there was some confusion here. According to my understanding the soluion is from text file to sqlserver. Can you please send me any process you have to convert SQL SERVER database to ORACLE. Please understand i am new to the this environment.
venkatesh
venkatesh -
You'll need to spend some time experimenting with DTS - it supports any ODBC or OLEDB data source, so you should have no problem connecting to Oracle (well, in theory anyway!). Try it out to start with just moving data between 2 SQL db's. Once you understand how it works, saving packages, etc, then start testing the move to Oracle. If the connection works (and I think it will), setting it up is a 10-15 job, then just a matter of running it which may take a couple hours depending on your network speed. Dont expect it to go perfectly in one try - usually you'll get constraint violations, items to too long for target columns, etc, that you'll have to clean up.
Andy
-
I don't know if you guys are intentionally teasing this poor gentlemen but, he wants to go FROM SQL SERVER TO ORACLE.
Bad enough that he is going the wrong way and now he is having a hard time getting answers. If I had to guess, I would say he is on the wrong site.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Agreed. If you want to go to SQL Server we can help. If you are moving to Oracle, I suggest you check out their site.
Steve Jones
-
I'll have to show my ignorance here, having not yet gotten around to Oracle for a spin - you "cant" DTS data and/or structures from SQL to Oracle? Why not? All MS spin aside, if you have an ODBC or OLEDB driver for Oracle, at the very least you can move the data, right?
Andy
-
You can do anything as long as teh driver allows it. As far as I know, you can move this data using DTS, just not sure if there are any gotchas on data conversions.
Steve Jones
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply