Author: John Memon

Page: 1 2

In prior releases of Oracle users could be granted SELECT privilege to make a table read-only. However the owner of the table had the read-write privileges and hence an owner could do updates and inserts on that table.

Oracle 11g database has made it possible to make a table read only for not only the database user but the owner of the table as well. You can convert a table from read/write state to read only state and vice-versa. 

Oracle 11g Read-Only Tables:

Below command can be used to make a table read only. 

ALTER TABLE table_name READ ONLY;

Below script creates a table, inserts a row and then sets the table to read-only.

CREATE TABLE READ_ONLY_TABLE (
id NUMBER
);

INSERT INTO READ_ONLY_TABLE VALUES (1);
ALTER TABLE READ_ONLY_TABLE READ ONLY;

The [DBA|ALL|USER]_TABLES views have a new column called READ_ONLY which can be used to see which tables are read only in the schema.

DML Statements for Read-Only Table:

Now if you try to run any query that affect the table data and SELECT ... FOR UPDATE queries result in an ORA-12081 error message.

Read Only Tables – Insert Query:

SQL> INSERT INTO READ_ONLY_TABLE VALUES (5);

INSERT INTO READ_ONLY_TABLE VALUES (5)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."READ_ONLY_TABLE"

Read Only Tables – Update Query:

SQL> UPDATE READ_ONLY_TABLE SET id = 2;

UPDATE READ_ONLY_TABLE SET id = 2
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."READ_ONLY_TABLE"

Read Only Tables – Delete Query:

SQL> DELETE FROM READ_ONLY_TABLE;

DELETE FROM READ_ONLY_TABLE
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."READ_ONLY_TABLE"

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Efficient Tips to Get Rid of Long Running Queries!!
   Oracle Replication, Some Concerns
   Beware of major Oracle Failures: Are you Safe??
   Top-N Queries for Oracle - SQL, Some Concerns