Ask the Expert

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.

    Requires Free Membership to View

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.

This was first published in October 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: