New Feature of Oracle11g: Read-only)

In versions earlier than Oracle11g, 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. Oracle11g allows

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

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

,