Make table readonly in Oracle11g

一直以来总有人问,能够将Oracle数据库中的表设置成只读吗?在Oracle11g之前回答是,不能。HJR甚至写过一篇完整的文章来阐述这个问题。

Oracle11g终于带来了这个新特性,设置表为Readonly,简单的一个命令而已。

SQL> CREATE TABLE “KAMUS”.”T1″ ( “N” NUMBER);

Table created

Executed in 0.047 seconds

SQL> alter table t1 read only;

Table altered

Executed in 0.125 seconds

SQL> insert into t1 values(1);

insert into t1 values(1)

ORA-12081: update operation not allowed on table “KAMUS”.”T1″

SQL> alter table t1 read write;

Table altered

Executed in 0.015 seconds

SQL> insert into t1 values(1);

1 row inserted

Executed in 0 seconds

Oracle一直在各个细节上不断地改善着,这是个值得信赖和继续投入热爱的产品。

One Comment Add yours

  1. Frank Zhou says:

    One of the possible “temporary” solution before Oracle 11G is as following :

    alter table t1 add constraint CK1 check ( 1=1) disable validate;
    alter table t1 disable table lock;

    http://oraqa.com/2006/01/09/how-to-set-a-table-in-read-only-mode/

    However this trick has side effects and is not as good as the
    “alter table t1 read only” in 11G

    Thanks,

    Frank

Leave a Reply

Your email address will not be published. Required fields are marked *