Rename Tablespace in Oracle database 10g

Renaming permanent and temporary tablespaces is now allowed in Oracle 10g (except for SYSTEM and SYSAUX) using the following command.

ALTER TABLESPACE ts_current_name RENAME TO ts_new_name;

The tablespace and all it's datafiles must be online and the database must have a COMPATIBLE setting of 10.0.0 or greater.

If the tablespace is read-only the datafile headers are not altered to reflect the name change and a message is written to the alert log to notify you of this fact. The impact on recovery is that the tablespace will be recovered to it's old name if the controlfile is recreated and datafiles containing the old headers are used.

If an undo tablespace is renamed in a instance which uses a pfile, rather than an spfile, a message is written to the alert log reminding you to change the value of the UNDO_TABLESPACE parameter.

[@more@]