Read-Only Tables in Oracle Database 11g

Read-Only Tables in Oracle Database 11g Release 1


In previous Oracle releases, tables could be made to appear read-only to other users by only granting the SELECT object privilege to them, but the tables remained read-write for the owner. 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;
The following script creates a table, inserts a row, then sets the table to read-only.
CREATE TABLE ro_tab (
  id  NUMBER
);


INSERT INTO ro_tab VALUES (1);
ALTER TABLE ro_tab READ ONLY;
Any DML statements that affect the table data and SELECT ... FOR UPDATE queries result in an ORA-12081 error message.
SQL> INSERT INTO ro_tab VALUES (2);
INSERT INTO ro_tab VALUES (2)
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"




SQL> UPDATE ro_tab SET id = 2;
UPDATE ro_tab SET id = 2
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"




SQL> DELETE FROM ro_tab;
DELETE FROM ro_tab
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"
DDL statements that affect the table data are also restricted.
SQL> TRUNCATE TABLE ro_tab;
TRUNCATE TABLE ro_tab
               *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"




SQL> ALTER TABLE ro_tab ADD (description VARCHAR2(50));
ALTER TABLE ro_tab ADD (description VARCHAR2(50))
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"
Operations on indexes associated with the table are unaffected by the read-only state. DML and DDL operations return to normal once the table is switched back to read-write mode.
SQL> ALTER TABLE ro_tab READ WRITE;


Table altered.


SQL> DELETE FROM ro_tab;


1 row deleted.


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