Renaming Tablespaces

Renaming Tablespaces

Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary tablespace.


SQL> alter tablespace users RENAME TO userts;

Tablespace altered.


When you rename a tablespace the database updates all references to the tablespace name in the data dictionary, control file, and (online) datafile headers. The database does not change the tablespace ID so if this tablespace were, for example, the default tablespace for a user, then the renamed tablespace would show as the default tablespace for the user in the DBA_USERS view.

The following affect the operation of this statement:

The COMPATIBLE parameter must be set to 10.0 or higher.

If the tablespace being renamed is the SYSTEM tablespace or the SYSAUX tablespace, then it will not be renamed and an error is raised.

SQL> alter tablespace system RENAME TO system1;
alter tablespace system RENAME TO system1
*
ERROR at line 1:
ORA-00712: cannot rename system tablespace


SQL> alter tablespace sysaux RENAME TO system1;
alter tablespace sysaux RENAME TO system1
*
ERROR at line 1:
ORA-13502: Cannot rename SYSAUX tablespace

If any datafile in the tablespace is offline, or if the tablespace is offline, then the tablespace is not renamed and an error is raised.

SQL> alter tablespace userts RENAME TO users;
alter tablespace userts RENAME TO users
*
ERROR at line 1:
ORA-01135: file 4 accessed for DML/query is offline
ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF'

If the tablespace is read only, then datafile headers are not updated. This should not be regarded as corruption; instead, it causes a message to be written to the alert log indicating that datafile headers have not been renamed. The data dictionary and control file are updated.

SQL> alter tablespace userts read only;

Tablespace altered.

SQL> alter tablespace userts RENAME TO users;

Tablespace altered.
alert_orcl.log
Sat Sep 08 19:52:42 2007
alter tablespace userts RENAME TO users
Tablespace 'USERTS' is renamed to 'USERS'.
Tablespace name change is not propagated to file headers because the tablespace is read only.

If the tablespace is the default temporary tablespace, then the corresponding entry in the database properties table is updated and the DATABASE_PROPERTIES view shows the new name.

SQL> select property_value
2 from database_properties
3 where property_name like '%TEMP%';

PROPERTY_VALUE
------------------------------------------------

TEMP2

SQL> alter tablespace temp2 rename to temp;

Tablespace altered.

SQL> select property_value
2 from database_properties
3 where property_name like '%TEMP%';

PROPERTY_VALUE
------------------------------------------------

TEMP

If a traditional initialization parameter file (PFILE) is being used then a message is written to the alert file stating that the initialization parameter file must be manually changed.

SQL> --when we rename undo tablespace and instance is startup with PFILE then
SQL> --rename entry is recorded in alert_sid.log file.
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> --database is startup from pfile
SQL> alter tablespace undotbs1 rename to undotbs;

Tablespace altered.

Sat Sep 08 20:14:52 2007
Tablespace 'UNDOTBS1' is renamed to 'UNDOTBS'.
PFILE is being used. It must be manually modified to reflect the new tablespace name if the old tablespace name is specified as UNDO_TABLESPACE in the PFILE.