Tuesday, 1 October 2013

How To Make Tables Read Only in oracle 11g Database with illustration

How To Make Tables Read Only in oracle 11g Database


there is new feature of making tables read only in oracle 11g database .prior to oracle 11g there ,you can make tables read only to other users by only give SELECT grant to the user.

For making tables read only in oracle 11g

alter table table_name read only;

example:-

let us we have to make a table named TEST (no number(10),name varchar2(30)) as read only

  1. SQL> select * from test;
    SQL> insert into test values (2,'kumar');
    SQL> commit;
  2. SQL> alter table test read only;
    SQL>insert into test values (3,'john');
    ORA-12081: update operation not allowed on table "TEST"
    SQL> update test set no=1 where no=2
    ORA-12081: update operation not allowed on table "TEST"

Now TEST table is read only no one can alter ,update or delete operation on TEST.


For making tables read only to read write oracle 11g
3. SQL> alter table test read write;
    SQL> insert into test values (3,'john');
    SQL> commit;

No comments:

Post a Comment