EXP and IMP Utilities

Command line EXP utility example for export of HR schema

exp fzafar/123@orcl file=c:\source\hr.dmp log=c:\source\hr.log buffer=100000 owner=HR  CONSISTENT=Y DIRECT=Y

IMP utility example to import only two tables in another schema HR_DUP

imp fzafar/123@orcl fromuser=HR touser=HR_DUP buffer=100000 file=c:\source\hr.dmp log=c:\source\hr_dup Tables=Jobs,Locations

Export Parameters

This section contains descriptions of some of the Export command-line parameters.


Default: operating system-dependent.

Specifies the size, in bytes, of the buffer used to fetch rows. As a result, this parameter determines the maximum number of rows in an array fetched by Export. Use the following formula to calculate the buffer size:

buffer_size = rows_in_array * maximum_row_size
buffer_size = (number of rows)100 * 120(max row length in a table)

If you specify zero, the Export utility fetches only one row at a time.Tables with columns of type LOBs, LONG, BFILE, REF, ROWID, LOGICAL ROWID, or DATE are fetched one row at a time.


The BUFFER parameter applies only to conventional path Export. It has no effect on a direct path Export. For direct path Exports, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export file.

CONSISTENT (Default: n)

Specifies whether or not Export uses the SET TRANSACTION READ ONLY statement to ensure that the data seen by Export is consistent to a single point in time and does not change during the execution of the exp command. You should specify CONSISTENT=y when you anticipate that other applications will be updating the target data after an export has started.


Specifies whether or not the Export utility uses the SET TRANSACTION READ ONLY statement to ensure that the data exported is consistent to a single point in time and does not change during the export. If OBJECT_CONSISTENT is set to y, each object is exported in its own read-only transaction, even if it is partitioned. In contrast, if you use the CONSISTENT parameter, then there is only one read-only transaction.

Import Parameters
FROMUSER (Default: none)

A comma-delimited list of schemas to import.  The parameter enables you to import a subset of schemas from an export file containing multiple schemas (for example, a full export dump file or a multischema, user-mode export dump file).

Only the name of the object is affected. After the import has completed, items in any TOUSER schema should be manually checked for references to old (FROMUSER) schemas, and corrected if necessary.

TOUSER (Default: none)

Specifies a list of user names whose schemas will be targets for Import. The user names must exist prior to the import operation; otherwise an error is returned. The IMP_FULL_DATABASE role is required to use this parameter. To import to a different schema than the one that originally contained the object, specify TOUSER. For example:

imp SYSTEM/password FROMUSER=scott TOUSER=joe TABLES=emp

If multiple schemas are specified, the schema names are paired. The following example imports scott's objects into joe‘s schema, and fred‘s objects into ted's schema:

imp SYSTEM/password FROMUSER=scott,fred TOUSER=joe,ted
imp SYSTEM/password FROMUSER=scott,fred TOUSER=joe,joe (to import both users)
INDEXFILE (Default: none)

Specifies a file to receive index-creation statements.

When this parameter is specified, index-creation statements for the requested mode are extracted and written to the specified file, rather than used to create indexes in the database. No database objects are imported.

If the Import parameter CONSTRAINTS is set to y, Import also writes table constraints to the index file.The file can then be edited (for example, to change storage parameters) and used as a SQL script to create the indexes.


2 responses to this post.

  1. Just want to say your article is as astonishing. The clarity in your post is simply
    great and i could assume you’re an expert on this subject. Well with your permission allow me to grab your feed to keep updated with forthcoming post. Thanks a million and please continue the rewarding work.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: