How to Open a Database in READ ONLY Mode

This was tested on Oracle 11.2.0.2.7. You can open a database in READ ONLY mode as follows: 

SQL> startup open read only;

ORACLE instance started.

 

Total System Global Area  522092544 bytes

Fixed Size                  2159904 bytes

Variable Size             314575584 bytes

Database Buffers          197132288 bytes

Redo Buffers                8224768 bytes

Database mounted.

Database opened.

SQL>

 

You can check how a database was opened like this:

 

SQL> select open_mode from v$database;

 

OPEN_MODE

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

READ ONLY

 

SQL>

 

When a database is open in READ ONLY mode, you cannot create tables in it:

 

SQL> create table andrews_table (col1 number);

create table andrews_table (col1 number)

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-16000: database open for read-only access

 

SQL>

 

You cannot update tables either:

 

SQL> insert into another_table

  2  select * from another_table;

insert into another_table

            *

ERROR at line 1:

ORA-16000: database open for read-only access

 

SQL> update another_table

  2  set col1 = 2;

update another_table

       *

ERROR at line 1:

ORA-16000: database open for read-only access

 

SQL> delete another_table;

delete another_table

       *

ERROR at line 1:

ORA-16000: database open for read-only access

 

SQL>

 

You cannot even drop a table. I wanted to try this out because, strangely enough, you CAN drop tables from READ ONLY tablespaces:

 

SQL> drop table another_table;

drop table another_table

*

ERROR at line 1:

ORA-16000: database open for read-only access

 

SQL>

 

Bouncing the database returns it to READ WRITE mode:

 

SQL> startup force

ORACLE instance started.

 

Total System Global Area  522092544 bytes

Fixed Size                  2159904 bytes

Variable Size             314575584 bytes

Database Buffers          197132288 bytes

Redo Buffers                8224768 bytes

Database mounted.

Database opened.

SQL> select open_mode from v$database;

 

OPEN_MODE

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

READ WRITE

 

SQL>