Export Import in oracle using Data Pump

NOTE:
1.    Oracle Data Pump is available only on Oracle Database 10g release 1 (10.1) and later.
2.    The new Data Pump Export and Import utilities (invoked with the expdp and impdp commands, respectively) have a similar look and feel to the original Export (exp) and Import (imp) utilities, but they are completely separate. Dump files generated by the new Data Pump Export utility are not compatible with dump files generated by the original Export utility. Therefore, files generated by the original Export (exp) utility cannot be imported with the Data Pump Import (impdp) utility
3.    If import is done on a different database, please make sure you have created the DIRECTORY same as export and given the READ, WRITE permissions to the import schema user. Also make sure you have copied the dump file from the export database directory to the import database directory.

STEPS TO DO:

1.    Take an export from the Production database by using the following sequence of steps:
a.    Creating Directories
Connect as SYSTEM user or the user having Grant privilege/Create Directories privilege and execute the below statements:

CREATE OR REPLACE DIRECTORY <DIR_NAME> AS '<PATH/DIR_NAME>';
GRANT READ, WRITE ON DIRECTORY <DIR_NAME> TO <SCHEMA>;

Example:

CREATE OR REPLACE DIRECTORY MYCOMP_DIR AS '/oracle/MYCOMP_DIR';
GRANT READ, WRITE ON DIRECTORY MYCOMP_DIR TO mydb_prod;

b.    Export Command:

EXPDP <USER/PASSWORD@SID> DIRECTORY=<DIR_NAME> DUMPFILE=<DUMP_FILE_NAME.dpdmp> JOB_NAME=<SOME_NAME> LOGFILE=<LOG_FILE_NAME.log> SCHEMAS=<USER>

Example:

EXPDP mydb_prod/welcome*123@PRODDB DIRECTORY=MYCOMP_DIR DUMPFILE=mydb_prod_dump_30032010.dpdmp> JOB_NAME=MYDB_DUMP LOGFILE= mydb_prod_dump_30032010.log> SCHEMAS=mydb_prod

2.    Make sure you have dropped the existing schema (If any) on the import database and create schema (Create User and Tablespaces)
3.    If import is done on a different database, please make sure you have created the DIRECTORY same as export and given the READ, WRITE permissions to the import schema user. (Please follow 1.a.) Also make sure you have copied the dump file from the export database directory to the import database directory.
4.    Do the import by following the commands:

Import Command:

IMPDP <USER/PASSWORD@SID> DIRECTORY=<DIR_NAME> DUMPFILE=<DUMP_FILE_NAME.dpdmp> LOGFILE=<LOG_FILE_NAME.log> FULL=N EXCLUDE=STATISTICS REMAP_TABLESPACE=<EXPORT_TABLESPACE_DATA>:<IMPORT_TABLESPACE_DATA> REMAP_TABLESPACE=<EXPORT_TABLESPACE_INDEX>:<IMPORT_TABLESPACE_INDEX REMAP_SCHEMA=<EXPORT_USER_SCHEMA>:<IMPORT_USER_SCHEMA> SCHEMAS=<EXPORT_USER_SCHEMA>

Example:

IMPDP MYDB_test/welcome*123@PRODDB DIRECTORY=MYCOMP_DIR DUMPFILE=mydb_prod_dump_30032010.dpdmp LOGFILE=MYDB_test_import_30032010.log> FULL=N EXCLUDE=STATISTICS REMAP_TABLESPACE=mydb_prod_data:MYDB_test_data REMAP_TABLESPACE=mydb_prod_index:MYDB_test_index REMAP_SCHEMA=mydb_prod:MYDB_test SCHEMAS=mydb_prod

5.    Now the Test instance is refreshed with latest Production dump.

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.