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

Restoring data from a .dmp file using the imp command

How does one import data (not restore the full database) from a .dmp file into an existing Oracle database with the same (metadata) structure, using the imp command?

You can restore as little as a single table or an individual schema from your .dmp file. Also if you are importing into a database with existing tables, you may run into referential integrity problems (foreign keys) or other constraint errors when loading data. It is often a good idea to disable those things before running your import and then re-enabling them after it is complete.

OK....now on to how to do the import! There are four basic import modes: FULL, TABLES, FROMUSER, and TRANSPORT_TABLESPACE. All modes (but FULL since you don't want to do a full import) can be used to accomplish what you desire at varying levels. Let's look at two in particular: TABLES and FROMUSER. TABLES is used to import only specified tables. FROMUSER allows import of all objects owned by a specified user.

If you use TABLES mode, you will simply list the tables you want to import.

	IMP id/password TABLES=(emp,dept,cust)
If you need to import the tables from a specific schema, you'd use the following:
	IMP id/password FROMUSER(scott) TABLES=(emp,dept)
	IMP id/password FROMUSER(adams) TABLES=(cust)
If you want to import just a given user's schema, you'd use:
	IMP id/password FROMUSER(scott)
For more information see the Oracle Utilities Reference document.

For More Information

Dig Deeper on Oracle database backup and recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.