February 2, 2009 at 11:04 am
Hello,
I am using the Workbench now to migrate the SQL database data to Oracle database (tables are the same).
Looking for the way to run automated process to do the same.
I have the old process : run commands to create views on my drive, then using Loader - insert all records into Oracle tables.
(I feel this process is old and not very quick...has a lot of steps, codes,and so on...I could be wrong)
Maybe some one knows the command to start exactly the same , as Workbench does, or maybe to start the Workbench migration process?
just guessing... 🙂
Thanks to everyone!
February 2, 2009 at 11:37 am
or is there another way exist? to copy the data from SQL 2000 to Oracle:hehe:
February 2, 2009 at 11:53 am
Hi
Why dont you use DTS or SSIS?? What my experience says Oracle Migration workbech gives several kinds of problems.
Thanks -- Vj
February 2, 2009 at 12:03 pm
for DTS - there should be some connection to Oracle server and database...using...? ODBC? don't know that part , I am not "very much" Oracle person
SSIS - it is in 2005 or 2008.... I have to migrate from 2000:w00t:
February 3, 2009 at 12:59 am
Vichka (2/2/2009)
or is there another way exist? to copy the data from SQL 2000 to Oracle:hehe:
I had the opportunity of migrating from SQL Server to Oracle several times.
In regards to the data, your best bet is Oracle Migration Workbench.
In regards to the code -here is where the fun comes - you would have to rewrite most of it from scratch.
The time consuming part of an SQL Server to Oracle migration is not related to migrating the data but related to put your code together on the Oracle side.
If you are not an Oracle person I would strongly suggest for you to hire a Senior Oracle guy/gal with strong PL/SQL skills; this person must also have SQL Server background because he/she has to be able to read and understand Transact-SQL.
Good luck!
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 3, 2009 at 1:05 pm
[font="Verdana"]I agree with the earlier comment about migrating the code. One word of warning: you will likely want to change the table names once they are in Oracle so that they are all in upper case. Otherwise, if the table name contains lower case letters, whenever you write a query, you have to put the name of the table in double quotes. That gets annoying after a while.
In terms of the best way of migrating the data, I'd suggest you use bcp or SSIS to pump the data out of SQL Server into text files. Then use SQL*Loader to load the data into Oracle. Depending on your version of Oracle, there are options to load data files from within Oracle SQL, so you probably don't have to deal with the full SQL*Loader syntax requirements.[/font]
February 4, 2009 at 9:56 am
All cool stuff! Thanks!
So far what I did : I got ODBC to oracle
I was able to truncate table in Oracle from SQL DTS
it worked!
but then I am trying to INSERT from my SQL table to Oracle
that is not working, asking for the...object?
the table names are the same, nothing changed,the same Upper case
When I created the data transfer task from SQL to Oracle, everything were in there : I had source, destination, transformation : all shows up and all works.
Then I ran it, got the message 'microsoft something...using object...' - then my Oracle connection stop working.
Now it is not recognized from DTS.
(the same happened already second time, then everything came back normal, I started over - same result)
If I will get the message again I will put it here.
Any idea about DTS and ODBC for Oracle?
I was able to truncate the table, so the connection worked!
I guess there is a trick with INSERT.
Thank you all!:)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply