24 July, 2012

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);  
   
 CREATE OR REPLACE TRIGGER READ_ONLY_TABLE_TRG  
   BEFORE DELETE OR INSERT OR UPDATE  
   ON READ_ONLY_TABLE  
   REFERENCING NEW AS NEW OLD AS OLD  
   FOR EACH ROW  
 DECLARE  
 BEGIN  
   RAISE_APPLICATION_ERROR (-20001, 'Table is read only table.');  
 END;  
   
 INSERT INTO READ_ONLY_TABLE  
    VALUES (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;  
   
 INSERT INTO READ_ONLY_TABLE3  
    VALUES (1);  

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 ) from READ_ONLY column which has two values ( YES, NO)

 SELECT table_name, READ_ONLY FROM tabs;  

The output of query is



Thanks

6 comments:

  1. Good to know this, thanks for the post.

    ReplyDelete
  2. how can i drop read constraints a table?

    ReplyDelete
    Replies
    1. If you use trigger or constarint you can disable it.
      If you use Read Only Table to disable read only you can alter table using the below script
      ALTER TABLE READ_ONLY_TABLE3 READ WRITE;

      Delete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...