New Features of Oracle 11g: Read-only)

In Oracle versions earlier than 11g, if you want to set the table to read-only, you can grant the SELECT object permission to these users, but the table owner still reads and writes the table. Oracle 11g allows the TABLE to be marked as read-only through the alter table command.

You can use the following command to set the read and write permissions of a table:

Alter table table_name read only;

Alter table table_name read write;

A simple example is as follows:

Create table ro_test (

Id number

);

Insert into ro_test VALUES (1 );

Alter table ro_test read only;

 

Any DML statements and SELECT... for update query statements that affect table data return ORA-12081 error messages

SQL> INSERT INTO ro_test VALUES (2 );
Insert into ro_test VALUES (2)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST". "RO_TEST"

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

SQL> DELETE FROM ro_test;
Delete from ro_test
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST". "RO_TEST"

DDL statements that affect table data are also restricted.

SQL> TRUNCATE TABLE ro_test;
Truncate table ro_test
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST". "RO_TEST"

SQL> ALTER TABLE ro_test ADD (description VARCHAR2 (50 ));
Alter table ro_test ADD (description VARCHAR2 (50 ))
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST". "RO_TEST"

A table is a read-only table, but its operations on related indexes are not affected. When the table is switched back to read/write mode, the DML and DDL operations return to normal.

SQL> ALTER TABLE ro_test READ WRITE;

Table altered.

SQL> DELETE FROM ro_test;

1 row deleted.

SQL>

Read more: New Features of Oracle 11g-read-only tables