Thursday, June 06, 2013

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 > 


If you have an Oracle book on Amazon, which you would like to advertise here for free, please write to me at international_dba@yahoo.co.uk.

No comments: