Oracle 11g Read Only Tables Page - 2

Page 2 of 2

Author: Saurabh Gupta Published on: 16th Jun 2011

Oracle 11g Read Only Tables

READ ONLY: Notes

As the name suggests and I stated earlier too, the READ-ONLY tables are only query tables. Except the SELECT and DROP commands, DML and DDL commands are ineffective in this mode.

Here, we shall see the impact of DML operations on the table T_READ. Oracle raises exception ORA-12081 to restrict the DML operations on the table.

Code [4]

Sample Code

SQL> INSERT INTO T_READ VALUES (1, 'INSERT');
INSERT INTO T_READ VALUES (1, 'INSERT')
*
ERROR at line 1:
ORA-12081: UPDATE operation NOT allowed ON TABLE "SCOTT"."T_READ"

Likewise INSERT statement; same exception would be raised for UPDATE and DELETE operations on the table T_READ. Similar to DML operations, few DDL operations like TRUNCATE and ALTER are also restricted for their user.

Code [5]

Sample Code

SQL> TRUNCATE TABLE T_READ;
TRUNCATE TABLE T_READ
*
ERROR at line 1:
ORA-12081: UPDATE operation NOT allowed ON TABLE "SCOTT"."T_READ"

Note the exception number and error message is same for all the restricted actions, DML or DDL on the table.

The ALTER command below attempts to add a column COL3 to the T_READ. The ALTER command fails since T_READ is Read-Only table.

Code [6]

Sample Code

SQL> ALTER TABLE T_READ
ADD COL3 NUMBER;
ALTER TABLE T_READ
*
ERROR at line 1:
ORA-12081: UPDATE operation NOT allowed ON TABLE "SCOTT"."T_READ"

But the DDL commands, which are not concerned with the data and structure of the table execute successfully. The ANALYZE, RENAME, and DROP commands execute successfully on READ-ONLY tables.

Code [7]

Sample Code

SQL> ANALYZE TABLE T_READ COMPUTE STATISTICS;
TABLE analyzed.
SQL> RENAME T_READ1 TO T_READ;
TABLE renamed.

Indexes can be created on the columns of READ-ONLY tables. These indexes are used by the optimizer in SELECT statements using READ-ONLY tables.

Code [8]

Sample Code

SQL> CREATE INDEX IDX_READ ON T_READ (COL1);
INDEX created.

Similarly, partitions can be created, DDL commands like DROP

READ-ONLY Mode Switch

A table in READ-ONLY mode can be switched back to READ WRITE mode using ALTER TABLE command.

Code [9]

Sample Code

SQL> ALTER TABLE T_READ READ WRITE;
TABLE altered.

The READ_ONLY status of the T_READ table in USER_TABLES is changed to NO.

Code [10]

Sample Code

SQL> SELECT TABLE_NAME, READ_ONLY FROM USER_TABLES WHERE TABLE_NAME='T_READ';
TABLE_NAME REA
------------------------------ ---
T_READ NO

Since T_READ is now a normal read/write supported table, data can be easily inserted, modified or deleted.

Code [11]

Sample Code

SQL> INSERT INTO T_READ VALUES (1,'INSERT');
1 row created.
SQL> INSERT INTO T_READ VALUES (2,'INSERT');
1 row created.
SQL> UPDATE T_READ
SET COL2 = 'INSERT'||COL1;
2 rows updated.
SQL> COMMIT;
Commit complete.

The mode can be toggled over to READ-ONLY mode to restrict further data changes.

Code [12]

Sample Code

SQL> ALTER TABLE T_READ READ ONLY;
TABLE altered.
SQL> SELECT * FROM T_READ;
COL1 COL2
---------- ----------
1 INSERT1
2 INSERT2

Applications of Read-Only Tables

Read-Only tables provide a secure way to ensure data integrity and data access. On a highly secure production system, a table containing confidential data can be declared READ ONLY, so as to make sure that data can only be selected, but not frequently modified.

1 2

Read Next: Oracle 11g Subprogram Overloading

This tutorial is part of a Oracle 11g Tutorial tutorial series. Read it from the beginning and learn yourself.

Oracle 11g Tutorial