Oracle 11g Read Only Tables

Page 1 of 2

Author: Saurabh Gupta Published on: 16th Jun 2011

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.

Sample Code

SQL> CREATE TABLE TAB_ORCL_10G
(A NUMBER, B NUMBER);
TABLE created.
SQL> 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> 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.

Sample Code

SQL> CREATE TABLE TAB_ORCL_10G
(A NUMBER, B NUMBER);
TABLE created.
SQL> ALTER TABLE TAB_ORCL_10G ADD CONSTRAINT CHECK_CONST CHECK(1=1) DISABLE VALIDATE;
TABLE altered.
SQL> 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> 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].