How to make a table read only in Oracle

How to make a table read only in Oracle

Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command.

ALTER TABLE table_name READ ONLY;
ALTER TABLE table_name READ WRITE;

Let’s create a table and make it read-only.

CREATE TABLE Example (id  NUMBER);
INSERT INTO Example VALUES (1);
ALTER TABLE Example READ ONLY;

CREATE TABLE Example (id  NUMBER);
INSERT INTO Example VALUES (1);
ALTER TABLE Example READ ONLY;
Any DML statements that affect the table data results in an ORA-12081 error message.

SQL> INSERT INTO Example VALUES (10);
INSERT INTO Example VALUES (10);
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."Example"

SQL> INSERT INTO Example VALUES (10);
INSERT INTO Example VALUES (10);
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."Example"
DML and DDL operations return to normal once the table is switched back to read-write mode.

SQL> ALTER TABLE Example READ WRITE;
Table altered.

SQL> DELETE FROM Example;
1 row deleted.

SQL> ALTER TABLE Example READ WRITE;
 Table altered.

SQL> DELETE FROM Example;
 1 row deleted.

The read-only status of tables is displayed in the READ_ONLY column of the [DBA|ALL|USER]_TABLES views.