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