Read-Only Tables in Oracle 11g

In pre-Oracle 11g, one option to place a table in read-only mode is to create a trigger on that table that raises an exception on INSERT, UPDATE, and DELETE. In Oracle Database 11g, you can place a table in read-only mode by merely executing ALTER TABLE…READ ONLY statement.

Following is a simple test:

I create a test table “TEST1” and insert a dummy record. Using the ALTER TABLE …READ ONLY statement, I place “TEST1” table in read-only mode: Once a table is marked as read-only, all DML transactions are protected against that table. Any attempt to modify the “TEST1” table results in ORA-12081 error. You can find out whether a table is in read-only mode by querying “READ_ONLY” column of “USER_TABLES” view. A value of “YES” indicates that a table is read-only while a value of “NO” indicates a table is in read-write mode. At any time, you may place the table back in read-write mode using ALTER TABLE…READ WRITE statement. Read Oracle Database 11g Administrators Guide, for a complete list of allowed and barred operations on read-only tables.