put table in read-only mode

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


命令alter table xxx read only;可以将表置为只读状态, 即使表的owner也不能修改数据.
create table treadonly as select * from dba_objects;
alter table treadonly read only;

SQL> select read_only from user_tables where table_name='TREADONLY';
REA
---
YES

SQL> insert into treadonly select * from treadonly;
insert into treadonly select * from treadonly
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."TREADONLY"


SQL> delete treadonly;
delete treadonly
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."TREADONLY"


SQL> update treadonly set object_id=object_id+1;
update treadonly set object_id=object_id+1
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."TREADONLY"


SQL> select * from treadonly for update;
select * from treadonly for update
              *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."TREADONLY"


SQL> truncate table treadonly;
truncate table treadonly
               *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."TREADONLY"




SQL> alter table treadonly read write;


Table altered.




SQL>  select read_only from user_tables where table_name='TREADONLY';


REA
---
NO

SQL> delete treadonly  where rownum<10;
9 rows deleted.

11g之前没有alter table xxx read only命令，可以用 disabled  and validated的check constraint 实现
ALTER TABLE xxx ADD CONSTRAINT cons1 CHECK (1=1) DISABLE VALIDATE;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/37279/viewspace-2126115/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/37279/viewspace-2126115/