How to Rename an Oracle Tablespace

In Oracle 9 you got an error if you tried to rename a tablespace: 

SQL> l

  1* alter tablespace users rename to andrew

SQL> /

alter tablespace users rename to andrew

                              *

ERROR at line 1:

ORA-01904: DATAFILE keyword expected

 

SQL>

… but from Oracle 10 onwards you can rename tablespaces. I decided to demonstrate this by renaming the USERS tablespace to ANDREW. First I checked that there was a tablespace called USERS but no tablespace called ANDREW:

SQL> select tablespace_name from dba_tablespaces
  2  where tablespace_name in ('ANDREW', 'USERS')
  3  /

TABLESPACE_NAME
------------------------------
USERS

 
SQL> 

Then I showed that there was a table called CARS in the USERS tablespace: 

SQL> select tablespace_name from dba_tables
  2  where table_name = 'CARS'
  3  /

TABLESPACE_NAME
------------------------------
USERS

SQL>

I renamed the USERS tablespace and called it ANDREW instead:

SQL> alter tablespace users rename to andrew
  2  /

Tablespace altered.

SQL>

After that I showed that there was a tablespace called ANDREW but no tablespace called USERS:

SQL> select tablespace_name from dba_tablespaces
  2  where tablespace_name in ('ANDREW', 'USERS')
  3  /

TABLESPACE_NAME
------------------------------
ANDREW

SQL>

Then I checked that the CARS table was in the ANDREW tablespace:

SQL> select tablespace_name from dba_tables
  2  where table_name = 'CARS'
  3  /

TABLESPACE_NAME
------------------------------
ANDREW

SQL>

I took the ANDREW tablespace offline then tried to rename it back to USERS but found that you cannot rename a tablespace if it is offline:

SQL> alter tablespace andrew offline
  2  /

Tablespace altered.

SQL> alter tablespace andrew rename to users
  2  /
alter tablespace andrew rename to users
*
ERROR at line 1:
ORA-01135: file 4 accessed for DML/query is offline
ORA-01110: data file 4: 'C:\DOCUMENTS AND SETTINGS\ANDREW\MY
DOCUMENTS\ADHOC_DATABASE\ADHOC\USERS01.DBF'

SQL> 

Next I confirmed that you cannot rename the SYSTEM or SYSAUX tablespaces:

SQL> alter tablespace system rename to fred
  2  /
alter tablespace system rename to fred
*
ERROR at line 1:
ORA-00712: cannot rename system tablespace

SQL> alter tablespace sysaux rename to john
  2  /
alter tablespace sysaux rename to john
*
ERROR at line 1:
ORA-13502: Cannot rename SYSAUX tablespace

SQL> 

Finally, if you try to give your tablespace an invalid name, you get an ORA-02150: 

SQL> alter tablespace users rename to 123;

alter tablespace users rename to 123

                                 *

ERROR at line 1:

ORA-02150: invalid new tablespace name

 

SQL> 

 

So, now my problem is - how can I get an ORA-00711? 

 

Solaris > oerr ora 00711

00711, 00000, "new tablespace name is invalid"

// *Cause:  An attempt to rename a tablespace failed because the new name

//          is invalid.

// *Action: Choose a valid new name and retry the command.

Solaris >