Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA 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 | oracle

2 Comments »

  1. Read only table is the best security feature introduced in Oracle 11g. Before it has only read only tablespace clause in past releases of Oracle.

    Comment by Jack Nicholson | April 12, 2010 | Reply

    • This is one of the features I like as well.

      Comment by gjilevski | May 27, 2010 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 614 other followers

%d bloggers like this: