Read-Only Tables in Oracle Database 11g

In previous Oracle releases, tables could be made to appear read-only to other users by only granting theSELECT object privilege to them, but the tables remained read-write for the owner. Oracle 11g allows tables to be marked as read-only using theALTER 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,但是只读模式的表可以用drop命令删除。

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.