11g以前,当需要设置一个表只读时,我们通过赋予某些用户select权限。但对于表的owner来说,还是可以读写的。
从Oracle 11g开始,我们可以通过一下命令设置表只读或可读可写:
alter table tab1 read only;
alter table tab1 read write;
SQL> create table t1 (t number);
Table created
--设置表为只读
SQL> alter table t1 read only;
Table altered
--DML操作,insert/update/delete都不允许
SQL> insert into t1 values (1);
insert into t1 values (1)
ORA-12081: update operation not allowed on table "TOUGH"."T1"
SQL> update t1 set t=0;
update t1 set t=0
ORA-12081: update operation not allowed on table "TOUGH"."T1"
SQL> delete from t1;
delete from t1
ORA-12081: update operation not allowed on table "TOUGH"."T1"
--DDL操作,truncate不允许
SQL> truncate table t1;
truncate table t1
ORA-12081: update operation not allowed on table "TOUGH"."T1"
SQL> alter table t1 add (a number);
alter table t1 add (a number)
ORA-12081: update operation not allowed on table "TOUGH"."T1"
--虽然表设置成了只读,但此处对表进行与索引相关操作,因为索引修改的是数据字典,和表不相关,所以可以进行
SQL> create index t1_indx on t1(t);
Index created