Nov 17 2007

Make table readonly in Oracle11g

Published by kamus at 10:22 pm under Oracle RDBMS

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

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

  1. SQL> CREATE TABLE "KAMUS"."T1" ( "N" NUMBER);
  2.  
  3. Table created
  4.  
  5. Executed in 0.047 seconds
  6.  
  7. SQL> alter table t1 read only;
  8.  
  9. Table altered
  10.  
  11. Executed in 0.125 seconds
  12.  
  13. SQL> insert into t1 values(1);
  14.  
  15. insert into t1 values(1)
  16.  
  17. ORA-12081: update operation not allowed on table "KAMUS"."T1"
  18.  
  19. SQL> alter table t1 read write;
  20.  
  21. Table altered
  22.  
  23. Executed in 0.015 seconds
  24.  
  25. SQL> insert into t1 values(1);
  26.  
  27. 1 row inserted
  28.  
  29. Executed in 0 seconds

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

One Response to “Make table readonly in Oracle11g”

  1. Frank Zhou Says:
    January 3rd, 2008 at 3:09 am [ Quote ]

    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

Trackback URI | Comments RSS


Leave a Reply

-> :( :! :D :| :? :X ;;) :) ;)