Read Only Tables

Oracle 11 introduced a new ALTER TABLE syntax, which allows you to make a table READ ONLY, so I decided to try it out. First I created a table:

SQL> create table table_list

  2  as select table_name from dba_tables

  3  /

 

Table created.

SQL>

... then I made it READ ONLY:

 

SQL> alter table table_list read only

  2  /

 

Table altered.

... so when I tried to update it, Oracle gave me an error:

 

SQL> update table_list

  2  set table_name = 'BLAH'

  3  /

update table_list

       *

ERROR at line 1:

ORA-12081: update operation not allowed on table

"ORACLE"."TABLE_LIST" 

SQL>

I changed the table back to READ WRITE mode like this: 

 

SQL> alter table table_list read write

  2  /

 

Table altered.

SQL>

... then I was able to update it:

 

SQL> update table_list

  2  set table_name = 'BLAH'

  3  /

 

3081 rows updated.

 

SQL>

When you create a table, it is in READ WRITE mode by default. If you try to make it READ WRITE again, you get an error. Once a table is in READ ONLY mode, you get an error if you try to make it READ ONLY again. I checked this out as follows:

SQL> create table andrew (col1 varchar2(1))
  2  /

Table created.

SQL> alter table andrew read write

  2  /

alter table andrew read write

*

ERROR at line 1:

ORA-14140: table ORACLE.ANDREW is already in

read/write mode

SQL> alter table andrew read only

  2  /

Table altered.

SQL> alter table andrew read only

  2  /

alter table andrew read only

*

ERROR at line 1:

ORA-14139: table ORACLE.ANDREW is already in read-only

mode

SQL>