Is it possible to map one Tablespace to many Tablespaces with impdp?

Posted on by Ahmed Abdel Fattah

I got this question from one of my clients. He is going to migrate one database from one platform to another. In this database many users are sharing the same tablespace.

For example:

In the current database, all application users are sharing the tablespaces “USERS”

The client wants to change this, so as each schema has its own tablespace. For example:

We all know that when using data pump import, we can use Remap_tablespace to map tablespaces. For example:

  • remap_tablespace=USERS:USER1
  • remap_tablespace=EXAMPLE:USER2
  • remap_tablespace=DATA:USER3

so, we can use multiple remap_tablespace options to map many tablespaces, but one source tablespace cannot be repeated. So, using remap_tablespace option will not fulfill our requirement.

Also, we have another option remap_schema to map from one schema to another, but unfortunately, we cannot use nested remap options like: remap_tablespace & remap_schema to remap based on two factors: Tablespace & schema. (This may be raised as enhancement request to Oracle).

So, how we can workaround this requirement?

I have two solutions in my mind, you may come up with another solution.

Donate to support our blogging work

$1.00

Let us have a quick live demo about the proposed solutions:

Thanks
Ahmed

4 thoughts on “Is it possible to map one Tablespace to many Tablespaces with impdp?”

  1. Take one export and do multiple import for each user that way you can provide remap table space to different table space each time you invoke impdp

    Like

    Reply

Leave a comment