How To Rename Tablespace In Oracle ~ Blog for oracle RAC Goldengate Dataguard ASM database

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.

How To Rename Tablespace In Oracle

Related Post