Read–Only Tables in Oracle Database 11g

Prior to Oracle Database 11gR1 in order to create a read only table you had to employ clever uses of triggers, constraints or other methods to prevent the data from being changed. In many of those cases only INSERT, UPDATE, and DELETE operations were prevented while many DDL operations were not. Oracle Database 11gR1 provides the ability to create read only tables with a simple ALTER TABLE statement.

The command ALTER TABLE … READ ONLY will make a table read only. In order to make a table read only you have to have the ALTER TABLE or ALTER ANY TABLE privilege.

SQL> alter table countries read only; 

Table altered.

SQL>

The read only status of a table can be found DBA/ALL/USER_TABLES.READ_ONLY.

SQL> select read_only from user_tables where table_name = 'COUNTRIES';

REA
---
YES

SQL>

Operations that attempt to modify the data of at tabled marked read only will result in an error.

SQL> insert into countries(country_id) values('AD');
insert into countries(country_id) values('AD')
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."COUNTRIES"


SQL> alter table countries add code number;
alter table countries add code number
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."COUNTRIES"


SQL> 

The command ALTER TABLE … READ WRITE will once again allow modifications to the data in the table.

SQL> alter table countries read write;

Table altered.

SQL> insert into countries(country_id) values ('NV');

1 row created.

SQL>