Oracle Rename and delete tablespaces
You can modify the name of a table space as needed. Modifying the table space name does not affect the data in the table space. However, the system tablespace and sysaux names cannot be modified.
The syntax for renaming a tablespace is as follows:
Alter tablespace tablespace_name rename to new_tablespace_name;
Note:
If the tablespace status is offline, The tablespace cannot be renamed.
Delete a tablespace
To delete a tablespace, use the drop tablespace statement. The syntax is as follows:
Drop tablespace tablespace_name [including contents [and datafiles]
Syntax description:
Including contents
Deletes all database objects in the tablespace while deleting the tablespace. This option is required if the tablespace contains database objects.
And datafiles
Delete the data files corresponding to the tablespace while deleting the tablespace. If this option is not applicable, the delete tablespace only deletes information about the tablespace from the data dictionary and control file, and does not delete the data files corresponding to the tablespace in the operating system.
SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
---------------------------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
MYTEMP ONLINE
TEMPGROUP ONLINE
TEMPGROUP02 ONLINE
MYBIGSPACE ONLINE
BLOCKSPACE ONLINE
INSPUR ONLINE
TESTSPACE ONLINE
TEST ONLINE
13 rows have been selected.
SQL> alter tablespace mytemp rename to userspace;
The tablespace has been changed.
SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
---------------------------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
USERSPACE ONLINE
TEMPGROUP ONLINE
TEMPGROUP02 ONLINE
MYBIGSPACE ONLINE
BLOCKSPACE ONLINE
INSPUR ONLINE
TESTSPACE ONLINE
TEST ONLINE
13 rows have been selected.
SQL> drop tablespace userspace
2 including contents and datafiles;
The tablespace has been deleted.
How to handle Undo tablespace failures
Oracle Undo tablespace reconstruction and restoration
Oracle 11g installation manual on RedHat Linux 5.8 _ x64 Platform
Installing Oracle 12C in Linux-6-64
Install Oracle 11gR2 (x64) in CentOS 6.4)
Steps for installing Oracle 11gR2 in vmwarevm
Install Oracle 11g XE R2 In Debian