New Features of Oracle 11g-read-only tables

Last time we introduced the new feature of Oracle 11 GB-virtual columns. Today we will introduce another new feature-read-only tables.

Read-Only tables are no different from normal tables, but no DML (Insert, Update, Delete) operations can be performed on any transactions.

Before Oracle 11g, "read-only" is only valid for databases and tablespaces. When it reaches 11g, you can set a table as a read-only table.

Before 11g, if we want to implement a read-only table, we must implement it through triggers and constraints.

1-Table triggers

Next we will create a simple table and trigger to demonstrate this method:

 

01	CREATE TABLE READ_ONLY_TABLE (COL1 NUMBER);

02

03	CREATE OR REPLACE TRIGGER READ_ONLY_TABLE_TRG

04	BEFORE DELETE OR INSERT OR UPDATE

05	ON READ_ONLY_TABLE

06	REFERENCING NEW AS NEW OLD AS OLD

07	FOR EACH ROW

08

DECLARE

09

BEGIN

10	RAISE_APPLICATION_ERROR (-20001, 'Table is read only table.');

11

END;

12

13	INSERT INTO READ_ONLY_TABLE

14	VALUES (1);

Run this script and you will get the error message:


ORA-20001, Table is read only table.

2-check Constraints

Use the following SQL statement:

 

1	CREATE TABLE READ_ONLY_TABLE2 (COL1 NUMBER);

2

3	ALTER TABLE READ_ONLY_TABLE2 ADD CONSTRAINT READ_ONLY_CONST CHECK(0=0) DISABLE VALIDATE;

4

5	INSERT INTO READ_ONLY_TABLE2

6	VALUES (1);

Error message:

ORA-25128: No insert/update/delete on table with constraint SCOTT. READ_ONLY_CONST) disabled and validated

Very troublesome, right?

Oracle 11g can use the syntax alter table table_name raed only; to implement read-ONLY tables. Let's look at the following SQL statement:

 

1	CREATE TABLE READ_ONLY_TABLE3 (COL1 NUMBER);

2	ALTER TABLE READ_ONLY_TABLE3 READ ONLY;

3	INSERT INTO READ_ONLY_TABLE3 VALUES (1);

The error message after execution is:

ORA-12081: update operation not allowed on table "SCOTT". "READ_ONLY_TABLE3"

But how do I know whether a table is read-only?

You can know through the READ_ONLY column in the data dictionary view (ALL_TABLES, DBA_TABLES, USER_TABLES, TABS), such:

 

1	SELECT table_name, READ_ONLY FROM tabs;

Running result:

Full text!