I have a Microsoft SQL*Server 2000 and I want to transfer the structure and the data from SQL*Server over to my Oracle 9i DBMS. How can I accomplish this?
You are the second person who has had to perform a migration between SQL*Server and Oracle, you are moving the correct way at least from my perspective. I am a big fan of the Oracle platform.
First, I would suggest that you simply convert the database structures from SQL*Server to Oracle. There are many tools that can reverse engineer the database and then even convert the datatypes to SQL*Server datatypes. Then you can reproduce your database. If you don't have a tool, you will need to map datatypes like decimal to number and so on. Oracle provides some documentation to help you do this.
The next step would be to move the data. This is the most challenging portion of your migration depending on your referential integrity. This mainly affects the load order, so just plan carefully. To move data you can use the SQL*Server tool, DTS or another data movement tool like Warehouse Builder or Informatica. The DTS tools will help you to move data from SQL*Server to Oracle. At least you can use SQL*Server right up to its own demise. This is a good way to migrate and the tool is included with your SQL*Server license.
The application or stored object conversion will present the biggest challenge. Here you will need to convert any stored objects from SQL*Server to PL/SQL or Java code. As well, you will need to consider SQL*Server features such as IDENTIDY fields, which are converted to a number field with a trigger to populate it from a sequence. So look closely at what you need your database to perform and plan you migration carefully.
Good luck with your migration.