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
2 Comments »
Leave a Reply
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
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.
This is one of the features I like as well.