Problem solve Get help with specific problems with your technologies, process and projects.

Importing exp.dmp file into Oracle 10g

I have been given an exp.dmp file that was exported in Oracle 9i and I have to import it in Oracle 10g. I have no knowledge who did the export, and all I have done is to create a new database and user. I would like the database to be under the user's schema. Please help since I have made many attempts but they have all been futile.

You can find out the contents of this dump file with the import utility's SHOW parameter. Use a command similar to the following:

imp userid=system/manager file=exp.dmp full=y show=y log=show.log

The FULL=Y parameter will tell import that you want to look at the full dump file. The SHOW=Y tells import that you do not really want to import the dump file, rather you want import to show you the contents. The output from the SHOW=Y parameter can be quite lengthy, so the output is logged to a file (show.log in this case). Once the import is done processing, you can examine this file. This file will contain all of the DDL statements that the import process would execute if you imported then entire contents of the dump file. The nice thing is that you can see statements like CREATE USER, CREATE TABLESPACE, CREATE TABLE, GRANT, ALTER, etc. After you are done looking at this file, you have an idea of what your dump file contains.

It sounds like the next thing you want to do is to import one schema's objects into another schema in your database. That can be done with a command similar to the following:

imp userid=system/manager file=exp.dmp fromuser=user1 touser=user2

This will take all objects owned by USER1 in the dump file and import them into the USER2 account.

Dig Deeper on Oracle database design and architecture