Oracle 11g中可以将一个表从普通表修改为只读表,在这种只读状态下,表的操作被限制,具体的限制如下:
不允许增删改DML操作;
不允许select ... for update悲观锁定;
不允许影响只读表 T数据的DDL操作;
允许索引操作。
具体看一下只读状态下的表的行为特征。
1.创建测试表T并初始化一条数据
2.使表T处于只读状态
3.验证只读表不允许DML操作
1)插入操作不允许
2)删除操作不允许
3)更新操作不允许
4.验证只读表不可悲观锁定
5.影响只读表T数据的DDL操作不允许
1)增加新字段的DDL操作不允许
2)修改字段类型操作不允许
6.只读表上允许索引类操作
1)为只读表创建索引
2)删除只读表上的索引
7.USER_TABLES中的READ_ONLY字段指示表的只读状态
如果 read_ only字段是“YES”表示是只读表,如果是“NO”表示非只读表
Oracle在11g之前的版本中实现只读表的方法是通过授权间接实现的,影响面很大。使用文中介绍的只读表( read- only table)技术我们就掌握了细粒度控制表行为的方法。
不允许增删改DML操作;
不允许select ... for update悲观锁定;
不允许影响只读表 T数据的DDL操作;
允许索引操作。
具体看一下只读状态下的表的行为特征。
1.创建测试表T并初始化一条数据
代码:
tacsoft_sql> create
table t (x varchar2(8));
Table created.
tacsoft_sql> insert into t values ('secooler');
1 row created.
tacsoft_sql> commit;
Commit complete.
Table created.
tacsoft_sql> insert into t values ('secooler');
1 row created.
tacsoft_sql> commit;
Commit complete.
2.使表T处于只读状态
代码:
3.验证只读表不允许DML操作
1)插入操作不允许
代码:
tacsoft_sql> insert into
t values ('HOU');
insert into t values ('HOU')
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SEC"." T"
insert into t values ('HOU')
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SEC"." T"
2)删除操作不允许
代码:
tacsoft_sql> delete from
t;
delete from t
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SEC"." T"
delete from t
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SEC"." T"
3)更新操作不允许
代码:
tacsoft_sql> update
t set x = 'HOU';
update t set x = 'HOU'
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SEC"." T"
update t set x = 'HOU'
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SEC"." T"
4.验证只读表不可悲观锁定
代码:
tacsoft_sql> select * from
t for update;
select * from t for update
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SEC"." T"
select * from t for update
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SEC"." T"
5.影响只读表T数据的DDL操作不允许
1)增加新字段的DDL操作不允许
代码:
tacsoft_sql>
alter table t add y varchar2(8);
alter table t add y varchar2(8)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SEC"." T"
alter table t add y varchar2(8)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SEC"." T"
2)修改字段类型操作不允许
代码:
tacsoft_sql>
alter table t modify x varchar2(10);
alter table t modify x varchar2(10)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SEC"." T"
alter table t modify x varchar2(10)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SEC"." T"
6.只读表上允许索引类操作
1)为只读表创建索引
代码:
tacsoft_sql> create index i_
t on
t(x);
Index created.
Index created.
2)删除只读表上的索引
代码:
tacsoft_sql> drop index i_
t;
Index dropped.
Index dropped.
7.USER_TABLES中的READ_ONLY字段指示表的只读状态
如果 read_ only字段是“YES”表示是只读表,如果是“NO”表示非只读表
代码:
tacsoft_sql> select
table_name,
read_
only from user_tables;
TABLE_NAME REA
------------------------------ ---
T YES
8.恢复只读表为普通表
tacsoft_sql> alter table t read write;
Table altered.
tacsoft_sql> select table_name, read_ only from user_tables;
TABLE_NAME REA
------------------------------ ---
T NO
TABLE_NAME REA
------------------------------ ---
T YES
8.恢复只读表为普通表
tacsoft_sql> alter table t read write;
Table altered.
tacsoft_sql> select table_name, read_ only from user_tables;
TABLE_NAME REA
------------------------------ ---
T NO
Oracle在11g之前的版本中实现只读表的方法是通过授权间接实现的,影响面很大。使用文中介绍的只读表( read- only table)技术我们就掌握了细粒度控制表行为的方法。