Did you know you can rename tablespaces?

You probably knew that. There is some possibility that I’m the last person to figure it out and even I knew about it for at least two years. After all renaming tablespaces existed since 10.1, which makes it almost 6 years old.

Just in case you didn’t know:
alter tablespace users rename to cool_users;

According to the docs (and my tests confirmed that), the rename changes the tablespace name but not the tablespace id – so it will not break behaviors such as default tablespace and file ownership and such.

Why is it such a cool feature? Because sometimes I get an export dump file from a customer who is moving to our service. I have to import the schemas from the dump file to our DB. I want his schemas to reside on CUST_X tablespace, but the dump file says they are in MYTBS. What do I do?

Before the magical rename: import to an indexfile. Use VI to modify the file to create all objects in CUST_X tablespace instead of MYTBS. Run the indexfile. Run the import.

After magical rename: rename CUST_X to MYTBS. Import. Rename back.

Much better!

Of course, datapump has the even more magical remap_tablespace parameter. Alas, not all my customers use datapump.

About these ads

6 Comments on “Did you know you can rename tablespaces?”

  1. Sweet :)

    Even i didn’t know. And the use you talked about is also wonderful. Simple but very useful.

    Cheers !

  2. Marcus Mönnig says:

    Great info.

    Btw, you can do this with remap_tablespace in datapump and use fromuser/touser parameters with the old imp.

  3. Iggy Fernandez says:

    As Tom Kytes likes to say: “It ain’t so much the things we don’t know that get us into trouble. It’s the things you know that just ain’t so or just ain’t so anymore or just ain’t always so.” We should all sentence ourselves to reading the New Features guides for Oracle 8i, 9i, 10g, and 11g.

    There are many good reasons to use the data pump utilities–instead of the exp and imp utilities–but at least three places where they fall short.

    You cannot use them on an Oracle client machine. The reason for the restriction is that the new utilities can only read from and write to special Oracle directories on the database server. Unfortunately, application developers don’t always have access to the database server. Further, you cannot use them to export to magnetic tapes or named pipes and you cannot use them with standby databases.

    Oracle has threatened to remove the exp and imp utilities in a future release but I hope they won’t.

  4. Amit says:

    Chen,

    I think one more alternative would be to specify default tablespace for the user to CUST_X and perform import. Any object which does not have corresponding tablespace will be imported in this tablespace.

    Cheers
    Amit

  5. Yas says:

    Chen, as Amit said it is possible to import into a different tablespace without renaming tablespaces. Set a default tablespace for the user, revoke the unlimited tablespace privilege and then run the import. It creates the objects in the default tablespace.

  6. […] Chen Shapira –  Did You Know You Can Rename Tablespaces? […]


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,113 other followers