Import on one table from dump file

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database backup and recovery How to export triggers in an Oracle export command How to precreate Oracle table extents and define extent size How to trim the Oracle listener log in Unix/Linux How to avoid invalid objects in Oracle when restoring the database How to perform an Oracle 9i upgrade with the Database Upgrade Assistant How to avoid Oracle error ORA-00060 when dropping a datafile in Oracle 10g How to precreate tablespaces in Oracle before a new database import How to solve an Oracle import error How to use Transportable Tablespaces in Oracle to copy files quickly How to use Oracle table extents to allocate less disk space Oracle Database Administrator Understanding SQL string functions What is the difference between a database engineer, architect and administrator? Error during RMAN backup Can I drop a column in SYS schema? STATSPACK tool: transaction vs. execution measurement Should I port from Microsoft Access? How can I find statistics on total memory usage and database connections? Installing multiple Oracle homes Modifying SYS password in a RAC environment How to create Datafiles in a Data Guard (10g) environment

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

This is possible. And it is something I have been known to do from time to time. When you create a dump, you have the option of exporting the entire database (FULL=Y), an entire schema (OWNER=schema_name), or a list of tables (TABLES=t1,t2,t3). Similarly, when you import from a dump file, you can specify a FULL import, import a specific schema (FROMUSER) or just a list of TABLES. The import "mode" does not depend on the export "mode." In your case, your dump file contains one schema. To import just one table from that dump file, do something similar to the following:

imp file=mydump.dmp tables=table_x

The import process will import only the table noted in the TABLES parameter. All other objects in the dump will be skipped. But keep in mind that the import will also import any triggers, indexes, or constraints on this table unless you turn those off with the appropriate parameter.

Another example of mixing modes is to assume you have a full export of the entire database. You can import just the SCOTT schema similar to:

imp file=mydump.dmp fromuser=scott

Or assume that the dump file contains only two schemas (export was created with OWNER=schema1,schema2). You can import both schemas by specifying FULL=Y for the import.