READ Only Tables in ORACLE 11G

In ORACLE releases before to 11G, we can achieve the read-only concept on table just by granting SELECT privilege to users.

But the owner of the table still have read-write access on the table.

In 11G, We have a flexibility to mark table as READ ONLY.

Lets observe the following scenario:

First, create a test table
create table read_only_test_tab (column1 int);

Now, mark the table as READ ONLY by executing the following command

SQL> alter table read_only_test_tab read only;
Table altered.

SQL> select table_name, read_only from
  2  dba_tables
  3  where table_name = 'READ_ONLY_TEST_TAB';

TABLE_NAME                     READ_ONLY
------------------------------ --------------------------
READ_ONLY_TEST_TAB             YES

SQL>


Now, try executing the DML statements on this table

SQL> insert into read_only_test_tab values(1);
insert into read_only_test_tab values(1)
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table
"MAHENDRA"."READ_ONLY_TEST_TAB"

SQL> update read_only_test_tab
  2  set column1 = column1;
update read_only_test_tab
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table
"MAHENDRA"."READ_ONLY_TEST_TAB"
SQL> delete from read_only_test_tab;
delete from read_only_test_tab
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table
"MAHENDRA"."READ_ONLY_TEST_TAB"

SQL>

Now, try with DDL statements on the table

SQL> alter table read_only_test_tab add (column2 int);
alter table read_only_test_tab add (column2 int)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table
"MAHENDRA"."READ_ONLY_TEST_TAB"

SQL> create index read_only_test_tab_idx on read_only_test_tab(column1);
Index created.
From the above , it is confirmed that when the table is in READ ONLY mode we can't perform DML, DDL statements.

Let us convert the table into READ WRITE and then try with DML, DDL statements

SQL> alter table read_only_test_tab read write;
Table altered.

SQL> select table_name, read_only from
  2      dba_tables
  3      where table_name = 'READ_ONLY_TEST_TAB';
TABLE_NAME                     READ_ONLY
------------------------------ --------------------
READ_ONLY_TEST_TAB             NO

SQL> insert into read_only_test_tab values (1);
1 row created.

SQL>
SQL> alter table read_only_test_tab add (column2 int);
Table altered.

The value of compatible parameter should be 11.0.0.0.0 or greater to use this concept

select * from

v$parameter

where name ='compatible'

If the value of compatible parameter is lower than 11.0.0.0.0 then we will get errors as follows

SQL> ALTER TABLE ro_tab READ ONLY;

ALTER TABLE ro_tab READ ONLY

*

ERROR at line 1:

ORA-00406: COMPATIBLE parameter needs to be 11.0.0.0.0 or greater

ORA-00722: Feature "ALTER TABLE READ ONLY, ALTER TABLE READ WRITE"