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,但是只读模式的表可以用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.