How To Rename A Tablespace In Oracle?

Prior to Oracle 10g, it was a tedious task to rename a tablespace. We had to follow a series of steps.

Lets say, we need to rename tablespace TEST_1 to TEST_2

1. Create a tablespace TEST_2

2. Export all the objects from TEST_1 tablespace

3. Import the data to TEST_2

4. Drop the tablespace TEST_1
Oracle has made this task simpler from 10g onwards and can be accomplished in a single step.

ALTER TABLESPACE TBS_OLD RENAME to TBS_NEW;

SQL> create tablespace test_1 datafile ‘/ora03/data03/datafile/test_1.dbf’ size 500m;

Tablespace created.

SQL> select TABLESPACE_NAME,FILE_NAME,FILE_ID from dba_data_files where TABLESPACE_NAME=’TEST_1′;

TABLESPACE_NAME  FILE_NAME                            FILE_ID
—————- ———————————- ———-
TEST_1           /ora03/data03/datafile/test_1.dbf      56

SQL> alter tablespace test_1 RENAME to test_2;

Tablespace altered.

SQL> select TABLESPACE_NAME,FILE_NAME,FILE_ID from dba_data_files where TABLESPACE_NAME=’TEST_1′;

no rows selected

SQL> select TABLESPACE_NAME,FILE_NAME,FILE_ID from dba_data_files where TABLESPACE_NAME=’TEST_2′;

TABLESPACE_NAME  FILE_NAME                                  FILE_ID
—————- —————————————– ———-
TEST_2            /ora03/data03/datafile/test_1.dbf           56

>> The datafile name and the file_id will remain the same as before.

However , there are a few limitations for this command:
1. The COMPATIBLE parameter must be set to 10.0.0 or higher
2. Cannot rename SYSTEM or SYSAUX tablespace
3. Cannot rename a offline tablespace or a tablespace with any offline datafiles.
4. Renaming a tablespace will not change the name of datafiles.

Article by : Anju