Solved

How can I create an exact copy of an oracle database in MS SQL 2008 Standard

Posted on 2011-03-23
7
254 Views
Last Modified: 2012-05-11
Or what is the best way? I had an Idea to create linked servers and that once linked I could find a stored proceedure somewhere that would run and get all the table names and data and create them on my MS SQL 2008 Server.  I only want a snapshot to be taken I do not need the data to be updated at all.
0
Comment
Question by:LorneCash
7 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
What Oracle version?

There is an Oracle package: dbms_metadata and a function called get_ddl in the newer versions of Oracle.

This will extract the structures.  I've not heard of a utility that will port the code automatically.
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
What about CTAS?  I don't know SQL Server that well so I'm not sure it will work, but I would think it should.

You should be able to do something like:

create table <tab> ... as select * from <tab>@<link>;

You could wrap that in a procedure that did a select on user_tables@<link> to loop through all the tables.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
Comment Utility
if you are using oracle 9i or later can you not just use oracle publishing and snapshot replication to achieve a copy

http://msdn.microsoft.com/en-us/library/ms151243.aspx
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>How can I create an exact copy of an oracle database in MS SQL 2008 Standard<<
You will not be able to create an exact copy.  Something similar perhaps, but not exactly the same.
0
 
LVL 1

Author Comment

by:LorneCash
Comment Utility
I think I need to be more specific... All I need is the tables populated with the data no code, no users and no permissions just tables with the same number of columns and the same number of rows that contain the same data.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
If you have linked the databases then:  Then johnsone's post in http:#a35198971
or insert into table (select * from linked_table)

Do have DTS (Data Transformation Services) with sql server?

If they aren't linked you can create a delimited flat file.

0
 
LVL 1

Author Closing Comment

by:LorneCash
Comment Utility
SSMA is the way to go in this case
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now