Thursday, June 02, 2016

ORA-01109

I read that you cannot take a tablespace offline if the database is only mounted so I decided to test this in an Oracle 11.2.0.4 database. I mounted the database and tried to take the USERS tablespace offline. Oracle returned an ORA-01109. I opened the database then I was able to take the tablespace offline:
 
SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2252448 bytes
Variable Size             310378848 bytes
Database Buffers          201326592 bytes
Redo Buffers                7979008 bytes
Database mounted.
SQL> alter tablespace users offline
  2  /
alter tablespace users offline
*
ERROR at line 1:
ORA-01109: database not open

SQL> alter database open
  2  /

Database altered.

SQL> alter tablespace users offline
  2  /

Tablespace altered.

SQL> alter tablespace users online
  2  /

Tablespace altered.

SQL>