Oracle10g Rename Tablespace

출처 :  http://www.remote-dba.net/10g_43.htm



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

1. Create a new tablespace NEW_TBS.
2. Copy all objects from OLD_TBS to NEW_TBS.
3. 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.