Oracle DB 11g New Feature (Read Only Tables)

I posted before about new features about Oracle Database 11g you can read it from below

Oracle DB 11g New Feature ( Virtual Columns ) 

Oracle DB 11g New Feature ( Compound Triggers )

Today I will produce new feature which called Read Only Tables

Read only tables are like normal  tables but it restricts any transaction to perform any DML(Insert, Update, Delete) operation against it.

Before oracle database version 11g READ ONLY was related to DATABASE and TABLE SPACE only but in version 11g you can do READ ONLY to tables too.

Implement READ ONLY in Previous Versions

In version previous 11g we use a workarounds to do READ ONLY against tables by table triggers or check constraints.

1- Table Trigger

I will create table for demo and create trigger on table for restricting DML operations. 

 CREATE TABLE READ_ONLY_TABLE (COL1 NUMBER);  
   

ATE OR REPLACE TRIGGER READ_ONLY_TABLE_TRG  
   
CR
EBEFORE DELETE OR INSERT OR UPDATE  

G NEW AS NEW OLD AS OLD
ON READ_ONLY_TABLE  
   REFERENCI
N  
   FOR EACH ROW  
 DECLARE  
 BEGIN  

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

END;  


RT INTO READ_ONLY_TABLE  
    
INS

EVALUES (1);  

If I try to run previous script it will raise exception 

ORA -20001, Table is read only table.

2- Check Constraint

I will create table for demo and create check constraint with disabled validation which has condition will be true every time exposed.

 CREATE TABLE READ_ONLY_TABLE2 (COL1 NUMBER);  
   

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

   

 INSERT INTO READ_ONLY_TABLE2  

    VALUES (1);  

If I try to run previous script it will raise exception 

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

Implement READ ONLY in 11g

Oracle support READ ONLY feature directly in version 11g using the below syntax

ALTER TABLE table_name RAED ONLY;

 CREATE TABLE READ_ONLY_TABLE3 (COL1 NUMBER);  
   
 ALTER TABLE READ_ONLY_TABLE3 READ ONLY;  

(1)
INSERT INTO READ_ONLY_TABLE3  
    VALUES

 ;  

If I try to run previous script it will raise exception 

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

Now RAED ONLY feature is provide in version 11g so how I can know is table READ ONLY or no?

You can new this from tables data dictionary views ( ALL_TABLES,DBA_TABLES,USER_TABLES,TABS ) fromREAD_ONLY column which has two values ( YES, NO)

 SELECT table_name, READ_ONLY FROM tabs;  

The output of query is

Thanks

Sekhar