Systems Engineering and RDBMS

Archive for September 6th, 2007

Renaming tablespaces in Oracle

Posted by decipherinfosys on September 6, 2007

Prior to Oracle 10g, there was no easy way to rename the tablespaces. One had to create a new tablespace, move all the objects from the old tablespace to the new tablespace and then drop the old tablespace along with its data files. This becomes a very tedious process and requires double the storage till the drop is done.

In 10g, Oracle introduced a new feature of renaming tablespace just like renaming a table.  First, we will check for the existing tablespace name in the database by issuing the following query (make sure you are connected to the database with correct privileges):

SQL>SELECT tablespace_name
FROM user_tablespaces
WHERE tablespace_name = ‘SLMDATA’;


Now we will rename it by issuing the following command.


Tablespace altered.

If you execute the first query again, it won’t return any record as tablespace is renamed to new name.

Few things to keep in mind when renaming the tablespaces:

•    Rename tablespace is not supported for SYSTEM and SYSAUX tablespaces. Attempt to rename these tablespaces will result into an error.
•    Database compatibility parameter (COMPATIBLE) in init.ora should be set to at least
•    To rename a tablespace, the tablespace and its data files should be online.
•    Temporary tablespace can be renamed as well.

When tablespace is being renamed, database updates the references in control file, data dictionary and online data file headers as they all reference the tablespace. One can verify that by querying the user_tables table to see the change in tablespace name for the objects which belong to older tablespace.

Posted in Oracle | Leave a Comment »