Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to ask about converting Oracle dump files (.dmp) into SQL Server files (.bak) where during conversion I don't have to be connected to any database server.

I've searched for related technologies, such as Oradump to SQL Server. Do you have another suggestion to solve this? Open source ones I mean.


Thanks for both of your response. I see how difficult it will, but is there any possibility to use another way in converting oracle dump file? because all of solution's converter tools always provide a connection database server. I'm so thankful what if you can suggest another tool. thanks anyway

share|improve this question
    
I am fairly certain this is totally impossible. –  ElectricLlama Apr 16 '13 at 3:37
5  
You should consider a different way of transferring this data as you will never find a tool that can do this. –  ElectricLlama Apr 16 '13 at 3:37
add comment

migrated from stackoverflow.com Apr 16 '13 at 6:13

This question came from our site for professional and enthusiast programmers.

5 Answers

"is there any possibility to use another way in converting oracle dump file?"

Import the dump file into Oracle. Transfer the data from Oracle to SQL Server using any number of tools (flat files, SSIS, linked servers, replication)

Forget about processing a dump file. The only thing that can process a dump file is Oracle. Or you can spend a year reverse engineering it then have to start over again when the format changes. If you want to convert Oracle data to SQL, you aren't going to be able to use a dump file unless you import into Oracle first.

share|improve this answer
add comment

How complex are your DB structures? Do you have basic tables with fields and primary keys? Do you have unique keys, foreign keys or composites, what about views? Do you have deeper logic objects like stored procedures and triggers? Oracle doesn't have that nice incremental parameter instead it has those nasty sequences. Apologies to Oracle fans.

In anycase more complex your database the more problematic your migration....I did find SQL Server Migration Assistant. http://blogs.msdn.com/b/ssma/ it might be something to look at.

Redgate has a webinar that might help you hit all the high points and provide some kind of solution: http://www.red-gate.com/products/oracle-development/deployment-suite-for-oracle/education/webinars/webinar-20-april-2011 The webinar covers SQLWays: http://www.ispirer.com/products.

I have not used these products personally and only skimmed the webinar, but I have been where you are and I know how touch and go this can be. Hopefully others will provide additional solutions for you...Good luck!

share|improve this answer
add comment

I consider this impossible, because:

  • Both formats have no public documentation available AFAIK.
  • The Oracle Dump is a logical format, all objects (Tables, Stores Procedures, Rows, etc.pp.) are contained seperated and can be imported individually by import options, e.g. only Tables, but no data.
  • The MS SQL BAK basically contains just the used blocks of the datafiles, not seperated at all into the different type of objects contained, and there you can restore a database file as a whole (including all tables, all data, all everything), not individual single elements.

So a tool capable of doing the requested would not only have to deal with the "usual stuff" like different datatypes and SQL features with all the subtle differences, it would as well have to transform an apple into an orange formatwise ...

share|improve this answer
add comment

Although Oracle doesn't publish the format of dump files, the more recent ones created by Data Pump utility (expdp versus the older exp command) are in XML format. But I agree with earlier responses: it's probably not worth the R&D effort. Depending on the size, you may be able to import into the free version of Oracle called Express Edition (XE). It has an 11GB maximum limit but is a free download. Once it's imported, you can use SQL Server SSIS to connect to Oracle and transfer the data.

share|improve this answer
add comment

You can simply use: OraDump to MSSQL, which is a powerful paid tool: OraDump This tool will simply read your dump file, create the database structure on SQLServer (Empty Database, or old existing with the same structure), then it will import all data to it. it was very helpful for me in fact.

share|improve this answer
    
Perhaps you could add some further details about the product you suggested. Perhaps how to perform the task in question, or why this tools works well, etc... something more than just the link. –  Max Vernon Oct 11 '13 at 1:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.