Skip to content

impdp REMAP_TABLESPACE Multiple Examples

  • by

In this post, we'll talk about 2 topics.

  1. REMAP_TABLESPACE Single Tablespace
  2. REMAP_TABLESPACE Multiple Tablespaces

REMAP_TABLESPACE Single Tablespace

To prevent ORA-00959: tablespace does not exist when importing tables, we can either create the same name tablespace in the target database or remap the tablespace. The parameter we should use in data pump (impdp) is REMAP_TABLESPACE.

To remap tablespace, we used to use the format:

REMAP_TABLESPACE=<SOURCE_TABLESPACE>:<TARGET_TABLESPACE>

For example:

[oracle@test ~]$ impdp system/password@orclpdb schemas=ERPAPP REMAP_TABLESPACE=ERP_TBS_08:ERP_TBS_01 dumpfile=full.dmp

Please note that, we usually use system to perform data migration, but using sys as sysdba is also working to import data.

We know how to map one tablespace, how about two or more tablespaces?

REMAP_TABLESPACE Multiple Tablespaces

There're 2 ways to set multiple remapping tablespaces.

Delimited by Commas

Every mapping set should be delimited by a comma like the following example:

[oracle@test ~]$ impdp system/password@orclpdb schemas=ERPAPP REMAP_TABLESPACE=ERP_TBS_08:ERP_TBS_01,ERP_TBS_09:ERP_TBS_01 dumpfile=full.dmp

We used one REMAP_TABLESPACE and multiple tablespace mappings.

Repeated REMAP_TABLESPACE

Delimiting by commas may not feasible for many tablespace remapping, so you may split them by the parameter REMAP_TABLESPACE.

[oracle@test ~]$ impdp system/password@orclpdb schemas=ERPAPP REMAP_TABLESPACE=ERP_TBS_08:ERP_TBS_01 REMAP_TABLESPACE=ERP_TBS_09:ERP_TBS_01 dumpfile=full.dmp

As you can see, there're two REMAP_TABLESPACE in the command.

In summary, the basic rule is that the source tablespace name can not be duplicated, but the target tablespace can be the same, just like the examples we demonstrated above.

Leave a Reply

Your email address will not be published. Required fields are marked *