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 Skant (id  NUMBER);
INSERT INTO Skant VALUES (1);
ALTER TABLE Skant READ ONLY;

Any DML statements that affect the table data results in an ORA-12081 error message.

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

DML and DDL operations return to normal once the table is switched back to read-write mode.

SQL> ALTER TABLE Skant READ WRITE;

Table altered.

SQL> DELETE FROM Skant;

1 row deleted.

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