Friday, January 12, 2018

EXPDP AND IMPDP Tricks

Have you ever been a situation that you needed to refresh a schema on DEV using data from PROD? This is a common scenario for a DBA.

Case1: Different SCHEMA and/or TABLESPACE

In my years of experience it is common to see that PROD and DEV environments does not have the same structure in terms of server capacity, file structure, schema and even tablespaces.

Let's say we have target DB called TDB and a source DB called SDB (pretty cool, i know right!). Our schema are TSCHEMA and SSCHEMA respectively and TSPACE and SSPACE for the tablespaces.

Step 1

In your source DB do this 

expdp directory=DATA_PUMP_DIR schemas=SSCHEMA dumpfile=expSSCHEMA.dmp logfile=expSSCHEMA.log 

(if you are in a *nix box, create this in a expdpSSCHEMA.sh so you would only have to key in the user and password. It is efficient instead of looking at "Burlesin Pages" all the time).


Step 2

Transfer expSSCHEMA.dmp to the target server.

Step 3

Once it is there do this

impdp directory=DUMPDIR 
remap_schema=expSSCHEMA:expTSCHEMA 
REMAP_TABLESPACE=SSPACE:TSSPACE
TABLE_EXISTS_ACTION=TRUNCATE 
dumpfile=expSSCHEMA.dmp
logfile=impSSCHEMA.log 

(if you are in a *nix box, create this in a expdpSSCHEMA.sh so you would only have to key in the user and password. It is efficient instead of looking at "Burlesin Pages" all the time).

Your Done.

No comments:

Post a Comment