Rename Tablespace
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:
- 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.
Figure 3.2: Rename
tablespace for tablespace transport.
