How to take DB Dump via import export and Data Dump (Oracle)

Using Oracle imp.exe and exp.exe utilities we can take data dump from any one schema and apply it to another schema. It may be noted, however, that it works when the tablespaces are named the same for the source and the target.

You may need the following permissions:

Example:

To Export a DB dump to a local Path:

exp ‘USER_ABC/PASSWORD@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(PORT=1001)(HOST=abc.xyz.com)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))’ file=C:\\PROD_DB_DUMP.dmp

To Import the exported Prod dump:

imp ‘USER_XYZ/PASSWORD@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(PORT=1523)(HOST=abc.xyz.com)))(CONNECT_DATA=(SERVICE_NAME=ORCL_SVC)))’ fromuser=USER_ABC touser=USER_XYZ file=C:\PROD_DB_DUMP.dmp ignore=y

Alternatively, We can use Data Pump (expdp, impdp) utilities in Oracle bin path to achieve much faster dump export and import.

parameters for expdp:
DIRECTORY=DATA_PUMP
DUMPFILE=expdp_DB%u.dmp
EXCLUDE=statistics
FILESIZE=1g
JOB_NAME=expdp_DB
LOGFILE=expdp_DB.log
PARALLEL=4
SCHEMAS=SCHEMA_NAME
FLASHBACK_SCN=9357749197023 <- query current_scn from v$database for this SCN number
parameters for impdp:
DIRECTORY=DATA_PUMP
DUMPFILE=expdp_DB%u.dmp
JOB_NAME=IMPDP_DB
LOGFILE=impdp_DB.log
FULL=Y
REMAP_SCHEMA=SOURCE_SCHEMA_NAME:TARGET_SCHEMA_NAME
REMAP_TABLESPACE=SOURCE_TABLE_SPACE_NAME:TARGET_TABLE_SPACE_NAME

Advertisements

#oracle-data-dump