Thursday, April 02, 2015

Oracle Impdp Remap Tablespace

When I import a Oracle dump file produced by datapump export utility expdp from another database instance, I get the following error. This is caused the the default tablespace for the user is different.


shell> impdp myuser/mypassword directory=DM_DUMP dumpfile=tmpm_r_pmml.dmp

ORA-39083: Object type TABLE:"MYUSER"."DATA_TRAIN" failed to create with error:
ORA-00959: tablespace 'MYUSER' does not exist
To find our the default tablepsace name for the current user, I log onto the user.
Shell> sqlplus myuser/mypassword

SQL> select default_tablespace from user_users;

DEFAULT_TABLESPACE
------------------------------
TBS

I add "remap_tablespace=DMUSER:TBS" to impdp command and the dump file is imported correctly.
impdp dmuser/dmuser directory=DM_DUMP dumpfile=tmpm_r_pmml.dmp 
   remap_tablespace=MYUSER:TBS

No comments: