1

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

Improve this question
2
  • 1
    I am fairly certain this is totally impossible.
    – Nick.Mc
    Commented Apr 16, 2013 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.
    – Nick.Mc
    Commented Apr 16, 2013 at 3:37

6 Answers 6

Reset to default
4

"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.

Improve this answer
1

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.

Improve this answer
1

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.

Improve this answer
1
  • 1
    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.
    – Hannah Vernon ♦
    Commented Oct 11, 2013 at 1:57
1

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. Link 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!

Improve this answer
0

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 ...

Improve this answer
-1

I realize this post is over a year old, but there is a tool called OraDump-to-MSSQL by Intelligent Converters that will at least take an Oracle dump file and import it in to an MSSQL database. You do have to have the MSSQL server up and running but it will import in to a SQL Server Express database which is free to download and install. You can get the tool here http://www.convert-in.com/ord2mss.htm. It's $79 which is cheap.

Improve this answer
1
  • Gary, Ahmad has already suggested OraDump on Oct. 13
    – LowlyDBA - John M
    Commented Jan 6, 2015 at 20:05