How to Rename Tablespace In Oracle10g

zhanglei_itput發表於2010-11-19
Oracle
How to Rename Tablespace In Oracle10g

Another great new feature in tablespace management is rename tablespace.
Tablespace Rename Overview
In Oracle 10g, you can simply rename a tablespace TBS01 to TBS02 by issuing the following command:
ALTER TABLESPACE tbs01 RENAME TO tbs02;
 
However, you must follow the rules when renaming a tablespace:
You must set compatibility level to at least 10.0.1.
You cannot rename the SYSTEM or SYSAUX tablespaces.
You cannot rename an offline tablespace.
You cannot rename a tablespace that contains offline datafiles.
Renaming a tablespace does not changes its tablespace identifier.
Renaming a tablespace does not change the name of its datafiles.

Tablespace Rename Benefits
Tablespace rename provides the following benefits:
It simplifies the process of tablespace migration within a database.
It simplifies the process of transporting a tablespace between two databases.
Examples
Example 1: Rename a tablespace within a database. In Oracle9i or earlier releases, you must take the following steps to rename a tablespace from OLD_TBS to NEW_TBS:
Create a new tablespace NEW_TBS.
Copy all objects from OLD_TBS to NEW_TBS.
Drop tablespace OLD_TBS.
In Oracle 10g, you can accomplish the same thing in one step and rename tablespace OLD_TBS to NEW_TBS.
ALTER TABLESPACE old_tbs RENAME TO new_tbs;
 
Example 2: Transport a tablespace between two databases. In the following example (see figure 3.2), you cannot transport a tablespace TBS01 from database A to database B in the previous release of Oracle server because database B also has a tablespace called TBS01. In Oracle 10g, you can simply rename TBS01 to TBS02 in database B before transporting tablespace TBS01.
 
參考文獻：
 
  

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9252210/viewspace-678526/，如需轉載，請註明出處，否則將追究法律責任。

相關文章