11g New Features - "Read only Tables"

Did you read that right? It's read only TABLES...11g has introduced read only tables. It's now possible to make a table read only to it's owner also. Earlier we used to grant "SELECT" privilege on a table to other users or create a view to make it read only for others BUT that was only for other users and not the owner. See the example below to switch the table from read-write to read-only and vice-versa...

SCOTT@11glab> create table deptnew as select * from dept;

Table created.

SCOTT@11glab> select * from deptnew;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SCOTT@11glab> insert into deptnew values (50,'New Dept','New Location');

1 row created.

SCOTT@11glab> commit;

Commit complete.

Make it READ ONLY

SCOTT@11glab> alter table deptnew read only;

Table altered.

SCOTT@11glab> insert into deptnew values (60,'New Dep1','New Location1');
insert into deptnew values (60,'New Dep1','New Location1')
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."DEPTNEW"

How do I find out if a table is read-only or read-write. Check the new column in "USER_TABLES"

SCOTT@11glab> select table_name,read_only from user_tables where table_name='DEPTNEW';

TABLE_NAME REA
------------------------------ ---
DEPTNEW YES

Make it READ WRITE again to insert data...

SCOTT@11glab> alter table deptnew read write;

Table altered.

SCOTT@11glab> insert into deptnew values (60,'New Dep1','New Location1');

1 row created.

SCOTT@11glab> commit;

Commit complete.

SCOTT@11glab> select * from deptnew;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 New Dept New Location
60 New Dep1 New Location1

6 rows selected.

Very useful indeed...