Taking a Datafile Offline in NOARCHIVELOG Mode

I read some RMAN documentation which said that you could not take a datafile offline if the database was in NOARCHIVELOG mode. I don’t know much about taking datafiles offline but this seemed reasonable so I gave it a try in Oracle 12.1. First I checked the database’s LOG_MODE: 

SQL> select log_mode from v$database

  2  /

 

LOG_MODE

------------

NOARCHIVELOG

 

SQL>

Then I tried to take a datafile offline but this failed, as the documentation had said it would: 

SQL> alter database datafile

  2  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'

  3  offline

  4  /

alter database datafile

*

ERROR at line 1:

ORA-01145: offline immediate disallowed unless media

recovery enabled

 

SQL>

 

I read somewhere in the Oracle documentation that you could do this if the tablespace was READ ONLY. This also seemed reasonable so I tried it too: 

SQL> alter tablespace users read only

  2  /

 

Tablespace altered.

 

SQL> alter database datafile

  2  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'

  3  offline

  4  /

 

Database altered.

 

SQL> 

Of course, once I had done this, I could not change the tablespace to READ WRITE mode again: 

SQL> l

  1* alter tablespace users read write

SQL> /

alter tablespace users read write

*

ERROR at line 1:

ORA-01135: file 6 accessed for DML/query is offline

ORA-01110: data file 6:

'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'

 

SQL> 

… until I had brought the datafile ONLINE: 

SQL> alter database datafile

  2  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'

  3  online

  4  /

 

Database altered.

 

SQL> alter tablespace users read write

  2  /

 

Tablespace altered.

 

SQL>

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 >