At work we have an Oracle based system we’ll be retiring sometime next year. We want to keep the data around for reporting, but don’t have the manpower or funds to properly flatten the database into a true data warehouse schema. We are leaning then to copy the data into our SQL warehouse database in almost a direct copy of the legacy’s schema with just a few minor tweaks.
In looking around for an efficient way to handle this, I found a tool called the SQL Server Migration Assistant for Oracle. This handy tool will copy everything, tables, views, stored procedures, triggers, just about everything you’d want.
In my case, all I really want is the table layouts. I will be making a minor tweak to the table layouts so I can combine four databases into a single one. In just a few hours, I was able to use SSMA to generate create table scripts for several hundred tables. I then brought the script into my favorite text editor, UltraEdit, and used it’s regular expression capability to add a source database field as the first field in every create table script. Saved ’em, ran it and in short order have a complete schema ready to hold my data.
Even using the SQL Server Migration Assistant in such a limited fashion, I was still able to save myself weeks of coding and trying to figure out how to manually map Oracle datatypes to SQL Server. If you are looking at any kind of data conversion take a look at this tool. You can find more info on the SSMA site at:
http://www.microsoft.com/sql/solutions/migration/oracle/default.mspx
Arcane Code 
Hi,
This is a very informative article and I am thankful that you have shared this information!
Best Regards,
Phil