Tips by Burleson|
Oracle10g Rename Tablespace
Another great new feature in tablespace management is rename
Tablespace Rename Overview
In Oracle 10g, you can simply rename a tablespace TBS01 to
TBS02 by issuing the following command:
ALTER TABLESPACE tbs01 RENAME
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
- Renaming a tablespace does not changes its tablespace
- Renaming a tablespace does not change the name of its
Tablespace Rename Benefits
Tablespace rename provides the following benefits:
- It simplifies the process of tablespace migration within a
- It simplifies the process of transporting a tablespace
between two databases.
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.