In a previous article I looked at how one could force imp to place user objects in the default tablespace of a user, in cases where the originating tablespace was not available in the destination database.
The newer Oracle Data Pump utilities, expdp and impdp provides a remapping option that lets you migrate data to a new tablespace during import. Data Pump was made the preferred way of doing imports and exports in Oracle 11g.
Data Pump reads and writes data to a directory on the database server, so the first thing we must do is define such a directory on both source and destination databases. The directory must be acessible by the oracle database.
SQL> CREATE DIRECTORY dpxfer1 AS ‘/u01/dpxfer1’;
We must also give the schema user access to the Data Pump directory:
GRANT READ,WRITE ON DIRECTORY dpxfer1 TO dbuser;
Then we’re set for the actual export. A number of options from the exp utility are available and will automatically be replaced by their new expdp equivalent
expdp dbuser/password DIRECTORY=dpxfer1 DUMPFILE=dbuser.dmp LOGFILE=dbuser.log CONSISTENT=Y
…
Legacy Mode Parameter: “consistent=TRUE” Location: Command Line, Replaced with: “flashback_time=TO_TIMESTAMP(‘2012-10-15 14:43:06’, ‘YYYY-MM-DD HH24:MI:SS’)”
As soon as we have transferred the dump file /u01/dpxfer1/dbuser.dmp to the destination server, we’re ready to run the import with the REMAP_TABLESPACE option, in this case placing any objects from the USERS tablespace on the source database into the DBUSER table space on the destination database. Naturally the user and table space must already exist on the destination, and the data pump directory as well as the associated user privilege must be added just like we did on the source database.
impdp dbuser/password DIRECTORY=dpxfer1 dumpfile=dbuser.dmp logfile=dbuser_imp.log REMAP_TABLESPACE=users:dbuser
