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)

	IMP id/password FROMUSER(scott) TABLES=(emp,dept)
	IMP id/password FROMUSER(adams) TABLES=(cust)

	IMP id/password FROMUSER(scott)

For More Information

• Dozens more answers to tough Oracle questions from Karen Morton are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.