Sunday, September 25, 2016

Importing an Oracle Dump (.dmp) file for Oracle 11g R2

I will be the first one to admit that I am not an Oracle guru by any means since I primarily stick to the Microsoft stack (aka Microsoft SQL Server) so developing with Oracle for any project is always a bit of a struggle and a learning curve for me.

One of the common tasks that nearly every Oracle developer will have to face at some point in time is how to restore an Oracle Dump (.dmp) file!

Unfortunately, the documentation on just how to accomplish this is relatively sparse and doing this within Oracle is not as straightforward as restoring a backup into SQL Server.

After quite a bit of searching on the Internet and searching through various forums such as Stack Overflow, I was able to come up with a workable solution.

First of all, in order to get everything prepped for the Oracle import, you will have to set up a user for the import.  To save you a few headaches, it is best to try and open the .dmp file in Notepad++ or a similar editor to figure out which user the .dmp file was exported from and create it accordingly.

This set of commands can be run in SQLPlus, SQL Developer, Toad or any other Oracle IDE you prefer:

Once you have that user in place, you will have to finally perform the import.  One of the caveats to doing this, however, is that you MUST DO THIS ON THE SERVER!!  Attempting to perform this import on a client machine will fail miserably and leave you scratching your head as to why you cannot get the import commands to work!

Therefore, once you get the .dmp file uploaded to the server and establish an RDP session into your server, you can run the following command at the Windows command prompt.  (I tried getting it to run outside of the Windows command prompt such as in SQL Developer or Toad, but I could never get these IDEs to recognize the command):

That should be all that is necessary to get your .dmp file onto your Oracle 11g R2 instance!

No comments:

Post a Comment