Tips - Data pump

Tips - Data pump

Export:

1. Use buffer parameter while using export and import by atleast 5000000( 5MB ) which will increase the performance of exp and imp by 3 folds. (conventional path)
2. Take care with NLS_LANG. It must match with V$NLS_PARAMETERS.NLS_CHARACTERSET.
3. For Speedy Exports set parameter db_file_multiblock_read_count =128. (OS Dependent)
4. The Export parameter BUFFER applies only to conventional path Exports. For direct path Export, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export file.
5. Use direct mode export (direct=Y).
6. RECORDLENGTH recommended values: Multiples of the file system I/O block size, Multiples of DB_BLOCK_SIZE

Import:

1. Use indexes=n to ignore the index importing.
2. Using indexfile to create the index to file, create the indexes after the importing using script file.
3. Using rows=n indexes=y to import index in a separate import action.

Example:

exp username/username@pwd file=username.dmp log=username.log direct=y recordlength=640000 statistics=none

imp username/username@pwd file=username.dmp fromuser=username touser=username indexes=n

imp username/username@pwd file=username.dmp fromuser=username touser=username indexes=y rows=n

Dharmendra