How to set a single table read-only in Oracle

Posted by David Yahalom under Oracle

It is easy to set an entire TABLESPACE read-only in Oracle by using the following command:<

ALTER TABLESPACE tbs_name READ ONLY;

But what about setting a single table in read only mode?

Usually the answer was “you can’t”. That is, however, not completely true.

While there isn’t any dedicated syntax in Oracle for setting a table read only, there is a quick and easy workaround. Simply create a check constraint on the table while specifying disable validate.

ALTER TABLE table_name ADD CONSTRAINT table_name_read_only check(1=1) disable validate;

Trying to insert, delete or update that table would yield the an ORA-25128: No insert/update/delete on table with constraint disabled and validated error and prevent the DML operations.