Q

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.


This was first published in October 2004

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close