Read-Only Tables in Oracle Database 11g Release 1

Read-Only Tables in Oracle Database 11g Release 1

 In previous Oracle releases, tables could be made to appear read-only to other users by only granting the SELECT object privilege to them, but the tables remained read-write for the owner. Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command.
           ALTER TABLE table_name READ ONLY;    
           ALTER TABLE table_name READ WRITE;

1.The following script creates a table, inserts a row, then sets the table to read-only.

        TEST@ sms> create table t1( id number,name varchar2(20));
        
        TEST@ sms> insert into t1 values(1,'aa');
        
        TEST@ sms> insert into t1 values(2,'bb');
        TEST@ sms> alter table t1 read only;
        TEST@ sms> select TABLE_NAME,READ_ONLY from user_tables where table_name='T1';
        
        TABLE_NAME           READ_ONLY
        -------------------- ------------------------------
        T1                   YES

2.Any DML statements that affect the table data and SELECT ... FOR UPDATE queries result in an ORA-12081 error message.
         TEST@ sms>  insert into t1 values(3,'cc');
          insert into t1 values(3,'cc')
                      *
         ERROR at line 1:
         ORA-12081: update operation not allowed on table "TEST"."T1"
         
         TEST@ sms>  update t1  set id=10 where id=1;
          update t1  set id=10 where id=1
                 *
         ERROR at line 1:
         ORA-12081: update operation not allowed on table "TEST"."T1"
         
         
         TEST@ sms> delete t1 where id=1;   
         delete t1 where id=1
                *
         ERROR at line 1:
         ORA-12081: update operation not allowed on table "TEST"."T1"
         
         
         TEST@ sms> truncate table t1;
         truncate table t1
                        *
         ERROR at line 1:
         ORA-12081: update operation not allowed on table "TEST"."T1"

        
3.DDL statements that affect the table data are also restricted.
         
         TEST@ sms> drop table t1;
         
         Table dropped.
         
         
         TEST@ sms> show recyclebin;
         ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
         ---------------- ------------------------------ ------------ -------------------
         T1               BIN$BY3lIab8LA7gUwEAAH8WYQ==$0 TABLE        2014-10-17:00:48:02
         
         
         TEST@ sms> flashback table t1 to before drop;
                                                      
         Flashback complete  
         
         TEST@ sms> select TABLE_NAME,READ_ONLY from user_tables where table_name='T1';                         
                                                                                       
         TABLE_NAME           READ_ONLY                                                
         -------------------- ------------------------------                           
         T1                   YES                                                      

         
4. Operations on indexes associated with the table are unaffected by the read-only state. DML and DDL operations return to normal once the table is switched back to read-write mode.
        
        TEST@ sms> alter table t1 read write;
        
        Table altered.
        
        TEST@ sms> insert into t1 values(3,'ccc');
        
        1 row created.



5. The read-only status of tables is displayed in the READ_ONLY column of the [DBA|ALL|USER]_TABLES views.