We help IT Professionals succeed at work.

Import dump file (.dmp)

shliau asked
I have a .dmp file with me which was imported from remote database.  It is a compact file that contains 16 tables with respective data.  But I don't know how to 'extract' tables from the .dmp files then replicate the resources into local database?

May I know what are the command steps required.

Watch Question

your dump file was created with the export utility "exp".
now you can import all or some of the tables with the "imp" command.
you can do this in Unix on command line or in Windows in command prompt. (imp/exp is in $ORACLE_HOME/bin directory)

if you want to import one table from the dump-file you can enter the following command:
imp USER/PASSWD ignore=y log=imp.log file=mydump.dmp tables=MyTable
USER/PASSWD is your connect-String to the database

If the export was done by another user the USER you have to add fromuser/touser like:
imp USER/PASSWD fromuser=USERX touser=USERPASSWD ignore=y log=imp.log file=mydump.dmp tables=MyTable

form more information on exp/imp see Oracle Utilities Documentation.

Hi !

You can extract data from the dump (.dmp) file into your database using the imp (import) utility.  Since you are new user check the situation that suits you and exectue the command given below:

1) You know the USER (oracle DB User) who exported/created the dump file and a USER with the same name exists in the local database where you are doing the import then u run the command:

IMP USERID=<username>/<password>@string FILE=myfile.dmp LOG=myfile.imp IGNORE=Y

2) You know the USER (oracle DB User) who exported/created the dump file. But you want to import/extract the data into different USER in the local database.  then u run the command:

IMP USERID=system/<password>@string FROMUSER=<user_who_exported> TOUSER=<new_user_on_local_db> FILE=myfile.dmp LOG=myfile.imp IGNORE=Y

If you don't know the USER (oracle DB User) who exported/created the dump file then run the command:

IMP USERID=system/<password>@string FILE=myfile.dmp LOG=myfile.imp IGNORE=Y show=y

Now open the myfile.imp log file and check the top lines there you find the USER who exported or created the dump file.

Hope your prob. is solved.



Good answer but i wonder why you asked not to repeat this question?  Does it due to the level of question which is too basic or it is rather a common question that everyone know?

Hi Shliau !

Thanx for the complement. I am happy that my answer could solve your problem.

What I ment by saying not to repeat the question was because i found your question posted three times.  That's the only reason. Because people who would be posting replies may post for 1st one, 2nd one and 3rd one.  So you would be missing may be some valuable reply.  Hope you got my point.

Please remove from your mind reg. LEVEL of question like basic or common question. We all are in learning stage only what differs are concepts.  So please free to post your question and don't hesitate. We all here are to exchange our knowledge.

Have a nice day.


Explore More ContentExplore courses, solutions, and other research materials related to this topic.