Oracle 11g Read Only Tables

Before the release of Oracle 11g, READ-ONLY mode could only be associated with the database and tablespace. Oracle 11g extends this feature by introducing behavioral modes for database tables.

A Read-Only table is a normal data storage table, which restricts any transactional action on it. In the tutorial, we shall study the behavior of READ-ONLY tables under different scenarios.

Read-Only tables: History

Prior to Oracle 11g, all tables were used to be transactional. The table owner and the privileged users used to perform DML and DDL operations on the table. Possible way to impose restriction on transactions was through check constraint method or through triggers. We shall see them as below.

Method 1: DML trigger method

A table TAB_ORCL_10G was created. A Before DML trigger TRG_ORCL_10G was created to restrict the DML operations on the table. Note that DDL commands are still executed on the table.

Refer the illustration below.

SQL&gt; CREATE TABLE TAB_ORCL_10G
     (A NUMBER, B NUMBER);
 
TABLE created.
 
SQL&gt; CREATE OR REPLACE TRIGGER TRG_ORCL_10G
     BEFORE INSERT OR UPDATE OR DELETE
     ON TAB_ORCL_10G
     BEGIN
     RAISE_APPLICATION_ERROR (-20001, 'OPERATION RESTRICTED.');
     END;
     /
 
TRIGGER created.
 
SQL&gt; INSERT INTO TAB_ORCL_10G VALUES(1,3);
INSERT INTO TAB_ORCL_10G VALUES(1,3)
            *
ERROR at line 1:
ORA-20001: OPERATION RESTRICTED.
ORA-06512: at "SCOTT. TRG_ORCL_10G ", line 2
ORA-04088: error during execution OF TRIGGER SCOTT. TRG_ORCL_10G

Method 2: Disable Validate Constraint method

The second method creates a table and a check constraint in disable validated state. Note that the DDL commands like ALTER, TRUNCATE and DROP are executed successfully.

SQL&gt; CREATE TABLE TAB_ORCL_10G
     (A NUMBER, B NUMBER);
 
TABLE created.
 
SQL&gt; ALTER TABLE TAB_ORCL_10G ADD CONSTRAINT CHECK_CONST CHECK(1=1) DISABLE VALIDATE;
 
TABLE altered.
 
SQL&gt; INSERT INTO TAB_ORCL_10G VALUES(1,3);
INSERT INTO TAB_ORCL_10G VALUES(1,3)
*
ERROR at line 1:
ORA-25128: No INSERT/UPDATE/DELETE ON TABLE WITH CONSTRAINT
(SCOTT.TEST_READ_ONLY) disabled AND validated
 
SQL&gt; TRUNCATE TABLE t_read_ol2;
TRUNCATE TABLE t_read_ol2
*
ERROR at line 1:
ORA-25128: No INSERT/UPDATE/DELETE ON TABLE WITH CONSTRAINT
(CCPDEV.TEST_READ_ONLY) disabled AND validated

Read-Only tables: the Purpose

In spite of smooth feature flow, Oracle considered both the workaround solutions as implementation overhead. Very soon, this consideration was molded into action because read only is an object property and it must not be imposed with a workaround solution.

With 11g release, a table can be established in READ-ONLY mode. The mode restricts all DML operations and DDL operations (TRUNCATE and ALTER). Surely and simply, this way enhances security at table level.

Oracle 11g release doesn’t provide READ-ONLY specification at table creation level. Therefore, a table has to be created in transactional mode still as in Code [1].

Code [1]

SQL&gt; CREATE TABLE T_READ
     (COL1 NUMBER,
      COL2 VARCHAR2(10))
      /
 
TABLE created.

Now, above table can be switched over to READ-ONLY mode as shown in Code [2].

Code [2]

SQL&gt; ALTER TABLE T_READ READ ONLY;
 
TABLE altered.

A new column READ_ONLY has been added in [USER | DBA | ALL] _TABLES views to reveal the mode of the table.

Code [3]

SQL&gt; SELECT TABLE_NAME, READ_ONLY FROM USER_TABLE 
WHERE TABLE_NAME='T_READ'
  /
 
TABLE_NAME                     REA
------------------------------ ---
T_READ                         YES

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]

SQL&gt; 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]

SQL&gt; 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]

SQL&gt; 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]

SQL&gt; ANALYZE TABLE T_READ COMPUTE STATISTICS;
 
TABLE analyzed.
 
SQL&gt; 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]

SQL&gt; 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]

SQL&gt; 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]

SQL&gt; 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]

SQL&gt; INSERT INTO T_READ VALUES (1,'INSERT');
 
1 ROW created.
 
SQL&gt; INSERT INTO T_READ VALUES (2,'INSERT');
 
1 ROW created.
 
SQL&gt; UPDATE T_READ
     SET COL2 = 'INSERT'||COL1;
 
2 ROWS updated.
 
SQL&gt; COMMIT;
 
Commit complete.

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

Code [12]

SQL&gt; ALTER TABLE T_READ READ ONLY;
 
TABLE altered.
 
SQL&gt; 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.