合 Oracle 11g 新特性：只读表（Read-only）
Tags： Oracle
Oracle11g推出了一个新的特性，可以将table置于read only状态，处于该状态的table的不能执行DML操作和某些DDL操作。在Oracle11g之前的版本，只能将整个tablespace或者database置于read only状态。对于table的控制则只能通过权限来设定。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | CREATE TABLE products(prod_id varchar2(6) NOT null,quantity number,price number,expiry_date date); ALTER TABLE products READ only; TRUNCATE TABLE products; ALTER TABLE products SET unused(expiry_date); ALTER TABLE products DROP unused columns; CREATE INDEX idxxx ON products(price); ALTER TABLE products DROP COLUMN expiry_date; DROP TABLE products; HR@lhr121> CREATE TABLE products(prod_id varchar2(6) NOT null,quantity number,price number,expiry_date date); Table created. HR@lhr121> HR@lhr121> ALTER TABLE products READ only; Table altered. HR@lhr121> TRUNCATE TABLE products; TRUNCATE TABLE products * ERROR at line 1: ORA-12081: update operation not allowed on table "HR"."PRODUCTS" HR@lhr121> ALTER TABLE products SET unused(expiry_date); ALTER TABLE products SET unused(expiry_date) * ERROR at line 1: ORA-12081: update operation not allowed on table "HR"."PRODUCTS" HR@lhr121> ALTER TABLE products DROP unused columns; Table altered. HR@lhr121> CREATE INDEX idxxx ON products(price); Index created. HR@lhr121> ALTER TABLE products DROP COLUMN expiry_date; ALTER TABLE products DROP COLUMN expiry_date * ERROR at line 1: ORA-12081: update operation not allowed on table "HR"."PRODUCTS" HR@lhr121> DROP TABLE products; Table dropped. |
案例分析：
11:44:46 SCOTT@ test1 >select * from tab;
TNAME TABTYPE CLUSTERID
BONUS TABLE
CREDIT_CLUSTER CLUSTER
CREDIT_ORDERS TABLE 1
DEPT TABLE
EMP TABLE
EMP1 TABLE
11:44:56 SCOTT@ test1 >select count(*) from emp1;
COUNT(*)
----------
18
Elapsed: 00:00:00.04
11:45:12 SCOTT@ test1 >alter table emp1 read only;
Table altered.
11:51:46 SCOTT@ test1 >select read_only from user_tables where table_name='EMP1';
REA
---
YES
对只读表做DML:
11:45:20 SCOTT@ test1 >insert into emp1 select * from emp where rownum=1;
insert into emp1 select * from emp where rownum=1
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."EMP1"
Elapsed: 00:00:00.04
11:45:38 SCOTT@ test1 >delete from emp1;
delete from emp1
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."EMP1"
Elapsed: 00:00:00.00
11:45:47 SCOTT@ test1 >update emp1 set sal=6000 where empno=7788;
update emp1 set sal=6000 where empno=7788
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."EMP1"
TRUNCATE TABLE:
11:46:03 SCOTT@ test1 >truncate table emp1;
truncate table emp1
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."EMP1"
Elapsed: 00:00:00.09
DROP TABLE:
11:46:45 SCOTT@ test1 >drop table emp1;
Table dropped.
Elapsed: 00:00:00.70
11:47:05 SCOTT@ test1 >show recycle;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
EMP1 BIN$ComP5WftmQ7gUKjA+QgIyQ==$0 TABLE 2014-12-19:11:47:04
