Did you know you can rename tablespaces?Posted: June 13, 2009
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.
Of course, datapump has the even more magical remap_tablespace parameter. Alas, not all my customers use datapump.