Chanel [K]

面朝大海，春暖花开

Make table readonly in Oracle11g

with one comment

一直以来总有人问，能够将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一直在各个细节上不断地改善着，这是个值得信赖和继续投入热爱的产品。

Written by kamus

November 17th, 2007 at 10:22 pm

Posted in Oracle RDBMS

Tagged with ,

«
»

One Response to 'Make table readonly in Oracle11g'

Subscribe to comments with RSS or TrackBack to 'Make table readonly in Oracle11g'.

  1. 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

    Frank Zhou

    3 Jan 08 at 3:09 am

Leave a Reply