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>
