Read-Only Tables in Oracle Database 11g Release 1

14 Dec

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.

SQL> ALTER TABLE table_name READ ONLY;
SQL> ALTER TABLE table_name READ WRITE;

The following script creates a table, inserts a row, then sets the table to read-only.

SQl> CREATE TABLE test ( id NUMBER);
SQL> INSERT INTO test VALUES (1);
SQL> ALTER TABLE test 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 test VALUES (2);

ERROR at line 1:
ORA-12081: update operation not allowed on table “DDO”.”Test”

SQL> UPDATE test SET id = 2;
ERROR at line 1:
ORA-12081: update operation not allowed on table “DDO”.”Test”

SQL> DELETE FROM test;
ERROR at line 1:
ORA-12081: update operation not allowed on table “ddo”.”test”

DDL statements that affect the table data are also restricted.
SQL> TRUNCATE TABLE test;

ERROR at line 1:
ORA-12081: update operation not allowed on table “ddo”.”test”

SQL> ALTER TABLE test ADD (description VARCHAR2(50));

ERROR at line 1:
ORA-12081: update operation not allowed on table “ddo”.”test”

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 test READ WRITE;

Table altered.

SQL> DELETE FROM test;
1 row deleted.

The read-only status of tables is displayed in the READ_ONLY column of the following views (DBA_TABLES, ALL_TABLES, USER_TABLES).

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: