How To Rename Tablespace In Oracle

See the below example to rename the tablespace : 

Check the tablespace name before rename :
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
UNDOTBS2

6 rows selected.
Steps to rename tablespace :
SQL> set pagesize 200
SQL> set lines 200
SQL> col file_name for a57
SQL> select file_id,file_name,tablespace_name from dba_data_files where tablespace_name='USERS';

FILE_ID FILE_NAME TABLESPACE_NAME
---------- --------------------------------------------------------- ------------------------------
6 +DATA/FINDB/DATAFILE/users.259.1038428347 USERS

SQL>
SQL> --- Rename the tablespace_name from USERS to USERS1
SQL>
SQL> alter tablespace USERS1 rename to USERS;

Tablespace altered.

SQL>
SQL> select file_id,file_name,tablespace_name from dba_data_files where tablespace_name='USERS';

no rows selected

SQL>
SQL> select file_id,file_name,tablespace_name from dba_data_files where tablespace_name='USERS1';

FILE_ID FILE_NAME TABLESPACE_NAME
---------- --------------------------------------------------------- ------------------------------
6 +DATA/FINDB/DATAFILE/users.259.1038428347 USERS1

SQL>
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS1
UNDOTBS2

6 rows selected.
NOTE: SYSTEM and SYSAUX tablespace cannot be renamed.
Previous
Next Post »