03 March 2013

Oracle 11G read only tables

In 2006, I have written a blog entry about how to make a table read only( a workaround way). Today, with Oracle 11g, this feature has came in the alter table options


drop table t;
create table t( n number);
insert into t(n) values(1);
insert into t(n) values(10);
commit;

--- prior 11g
alter table t add constraint t_ck check( n > 0 ) disable validate;
insert into t(n) values(100);
--ORA-25128: No insert/update/delete on table with constraint (HR.T_CK) disabled and validated

alter table t drop constraint t_ck ;
insert into t(n) values(100);


--- 11g read only
alter table t read only;
insert into t(n) values(100);
--- ORA-12081: update operation not allowed on table "HR"."T"

alter table t read write;
insert into t(n) values(100);

No comments: