Oracle11g推出了一个新的特性，可以将table置于read only状态，处于该状态的table的不能执行DML操作和某些DDL操作。在Oracle11g之前的版本，只能将整个tablespace或者database置于read only状态。对于table的控制则只能通过权限来设定。
SQL> create table t(i int);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> commit;
Commit complete.
Table created.
SQL> insert into t values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t read only;
Table altered.
SQL> select read_only from user_tables where table_name='T';
READ_ONLY
------
YES
Table altered.
SQL> select read_only from user_tables where table_name='T';
READ_ONLY
------
YES
对于read only的table，不能执行DML操作
SQL> insert into t values(2);
insert into t values(2)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "NING"."T"
SQL> delete from t;
delete from t
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "NING"."T"
SQL> update t set i=2 where i=1;
update t set i=2 where i=1
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "NING"."T"
insert into t values(2)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "NING"."T"
SQL> delete from t;
delete from t
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "NING"."T"
SQL> update t set i=2 where i=1;
update t set i=2 where i=1
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "NING"."T"
不能执行truncate
SQL> truncate table t;
truncate table t
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "NING"."T"
truncate table t
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "NING"."T"
不能添加删除修改column
SQL> alter table t add c varchar2(30);
alter table t add c varchar2(30)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "NING"."T"
SQL> alter table t modify i varchar2(10);
alter table t modify i varchar2(10)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "NING"."T"
SQL> alter table t rename column i to id;
alter table t rename column i to id
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "NING"."T"
alter table t add c varchar2(30)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "NING"."T"
SQL> alter table t modify i varchar2(10);
alter table t modify i varchar2(10)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "NING"."T"
SQL> alter table t rename column i to id;
alter table t rename column i to id
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "NING"."T"
可以执行move
SQL> alter table t move tablespace users;
Table altered.
SQL> alter table t move tablespace system;
Table altered.
Table altered.
SQL> alter table t move tablespace system;
Table altered.
可以执行压缩
SQL> alter table t compress;
Table altered.
SQL> alter table t nocompress;
Table altered.
Table altered.
SQL> alter table t nocompress;
Table altered.
对于约束和索引的添加删除没有影响
SQL> alter table t add primary key (i);
Table altered.
SQL> alter table t drop primary key;
Table altered.
SQL> create index i_test on t(i);
Index created.
SQL> drop index i_test;
Index dropped.
Table altered.
SQL> alter table t drop primary key;
Table altered.
SQL> create index i_test on t(i);
Index created.
SQL> drop index i_test;
Index dropped.
可以将table重新置于read write状态
SQL> alter table t read write;
Table altered.
SQL> select read_only from user_tables where table_name='T';
READ_ONLY
------
NO
Table altered.
SQL> select read_only from user_tables where table_name='T';
READ_ONLY
------
NO
可以drop处于read only状态的table
SQL> alter table t read only;
Table altered.
SQL> drop table t;
Table dropped.
Table altered.
SQL> drop table t;
Table dropped.
只需要有alter table的权限就可以对该table执行alter table table_name read only/read write的操作，所以，对于alter table/alter any table的授权要注意了。
SQL> conn test/test@11g
Connected.
SQL> alter table ning.t read only;
alter table ning.t read only
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn ning/ning@11g
Connected.
SQL> grant alter on t to test;
Grant succeeded.
SQL> conn test/test@11g
Connected.
SQL> alter table ning.t read only;
Table altered.
Connected.
SQL> alter table ning.t read only;
alter table ning.t read only
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn ning/ning@11g
Connected.
SQL> grant alter on t to test;
Grant succeeded.
SQL> conn test/test@11g
Connected.
SQL> alter table ning.t read only;
Table altered.
