Guenadi N Jilevski's Oracle BLOG

11g New Features – Read only Tables

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…

SQL> create table department as select * from dept;

Table created.

SQL> select * from department;

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

SQL> insert into department values (50,’New Dept’,’New Location’);

1 row created.

SQL> commit;

Commit complete.

Make it READ ONLY

SQL> alter table department read only;

Table altered.

SQL> insert into department values (60,’New Dep1′,’New Location1′);
insert into department values (60,’New Dep1′,’New Location1′)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “SCOTT”.”DEPARTMENT”

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

SQL> select table_name,read_only from user_tables where table_name=’DEPARTMENT’;

TABLE_NAME REA
—————————— —
DEPARTMENT YES

Make it READ WRITE again to insert data…

SQL> alter table department read write;

Table altered.

SQL> insert into department values (60,’New Dep1′,’New Location1′);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from department;

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.

Now we looked at making tables read-only.

Credit Prasad

March 8, 2010 - Posted by gjilevski | oracle