ORA-01643 etc

I have used ALTER TABLESPACE to change the status of a tablespace in several other posts. However, in some situations, you cannot change a tablespace's status. You can see what I mean in the examples below, which I tried out in an Oracle 10 database running on Linux.

You cannot make the SYSTEM, UNDO or TEMPORARY tablespaces READ ONLY:

SQL> alter tablespace system read only

  2  /
alter tablespace system read only
*
ERROR at line 1:
ORA-01643: system tablespace can not be made read only

SQL> alter tablespace undotbs1 read only
  2  /
alter tablespace undotbs1 read only
*
ERROR at line 1:
ORA-30021: Operation not allowed on undo tablespace

SQL> alter tablespace temp read only
  2  /
alter tablespace temp read only
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY
TABLESPACE

You cannot make a tablespace READ WRITE if is already ONLINE (READ WRITE and ONLINE have the same meaning):

SQL> select tablespace_name, status
  2  from dba_tablespaces
  3  /

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE

SQL> alter tablespace users read write
  2  /
alter tablespace users read write
*
ERROR at line 1:
ORA-01646: tablespace 'USERS' is not read only -
cannot make read write

SQL> alter tablespace users read only
  2  /

Tablespace altered.

SQL>

... and once a tablespace is READ ONLY, you cannot make it READ ONLY again!

SQL> alter tablespace users read only
  2  /
alter tablespace users read only
*
ERROR at line 1:
ORA-01644: tablespace 'USERS' is already read only

SQL>