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
- 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.
