RE: expdp

From: Rich Jesse <rjoralist3_at_society.servebeer.com>
Date: Wed, 28 Jan 2015 10:47:59 -0600 (CST)
Message-ID: <f949c345ce0d3ea0de1e8155005ba6fc.squirrel_at_society.servebeer.com>

Hey Brian,

> What if it uses several tablespaces? I have a tablespace for the tables and
> a tablespace for the indexes.
> Also I am moving it from prod to test but the tablespace names are the same,
> so does it matter?

Just keep adding more REMAP_TABLESPACE='OLDTS2:NEWTS2' lines to your impdp PARFILE to catch them all.

I used this method many times when moving schemas from Prod to Test. e.g.:

expdp mydbauser parfile=EXPPROD.DAT

...where EXPPROD.DAT contains:

DIRECTORY=MY_DIR
DUMPFILE=MYEXP.dmp

LOGFILE=MYEXP.log
CONTENT=ALL
SCHEMAS=('MYSCHEMA1','MYSCHEMA2')

Then, I copied the resulting MYEXP.dmp from the Prod Server's OS directory that's referenced in the Oracle database directory "MY_DIR", to the Test Server in the OS directory appropriate for the Test database directory "TEST_DIR". I then run this on the Test Server:

impdp mytestdba parfile=IMPTEST.DAT

...where IMPTEST.DAT contains:

DIRECTORY=TEST_DIR
DUMPFILE=MYEXP.dmp
LOGFILE=MYIMP.log

REMAP_SCHEMA='MYSCHEMA1:TEST1'
REMAP_SCHEMA='MYSCHEMA2:TEST2'
REMAP_TABLESPACE='MYSCHEMA1TSI:TEST1TSI'
REMAP_TABLESPACE='MYSCHEMA1TSD:TEST1TSD'
REMAP_TABLESPACE='MYSCHEMA2TSI:TEST2TSI'
REMAP_TABLESPACE='MYSCHEMA2TSD:TEST2TSD'
EXCLUDE=SCHEMA_EXPORT/TABLE/GRANT

EXCLUDE=SCHEMA_EXPORT/TABLE/STATISTICS
SCHEMAS=('MYSCHEMA1', 'MYSCHEMA2') This PARFILE remaps the two schemas, remaps the data and index tablespaces for each of the two schemas, and ignores some overhead I didn't want.

Hope this helps!

Rich

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 28 2015 - 17:47:59 CET