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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s