How To Make a Table READ ONLY in Oracle 11g?

Read Only tables in Oracle 11g:

Read-only table is a  good enhancement introduced in Oracle 11g , using which we can safeguard the tables from DML operations. This new feature helps to switch a table to READ ONLY mode by executing a single ALTER TABLE command.

SQL > Alter table table_name read only;

Alter any table privilege or alter privilege on the table is required to make the table read-only . Also, the COMPATIBLE initialization parameter must be set to 11.1.0 or greater.

The restrictions on a read only table is applicable to the table owner also. An attempt to execute any DML operation on a read-only table will result in ORA-12081 error.

Lets understand more on this with examples.

SQL> create table test ( serial_number number, entry varchar2(20));

Table created.

SQL> insert into test values ( 1, ‘entry1’);

1 row created.

SQL> insert into test values ( 2, ‘entry2’);

1 row created.

SQL> alter table test read only;

Table altered.

SQL> insert into test values ( 3, ‘entry3’);

insert into test values ( 3, ‘entry3’)
*

ERROR at line 1:
ORA-12081: update operation not allowed on table “TEST_USER”.”TEST”

SQL> delete from test where SERIAL_NUMBER=1;

delete from test where SERIAL_NUMBER=1

*

ERROR at line 1:

ORA-12081: update operation not allowed on table “TEST_USER”.”TEST”

SQL> truncate table test;
truncate table test
*
ERROR at line 1:

ORA-12081: update operation not allowed on table “TEST_USER”.”TEST”

SQL> select * from test;

SERIAL_NUMBER       ENTRY

————- —————–

1             entry1
2             entry2

We can change the table back to read-write mode at anytime, by executing the below command.

SQL > Alter table table_name read write;

SQL> alter table test read write;
table altered.

DML operations are permitted on the table once it is switched back to the normal mode.

SQL> insert into test values ( 3,’entry3′);
1 row created.

We can check the mode of a table by referring to the column “READ_ONLY” in the dba_tables/all_tables/user_tables data dictionary views.

Research and Article by : Anju