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

11:47:52 SCOTT@ test1 >flashback table emp1 to before drop;

Flashback complete.

11:49:56 SCOTT@ test1 >select count(*) from emp1;

  COUNT(*)

----------

        18

        

MOVE TABLE:        

11:50:06 SCOTT@ test1 >alter table emp1 move;

Table altered.

Elapsed: 00:00:00.54


压缩表：

11:51:27 SCOTT@ test1 >alter table emp1 compress;

Table altered.

Elapsed: 00:00:00.09

11:51:39 SCOTT@ test1 >alter table emp1 nocompress;

Table altered.

Elapsed: 00:00:00.16


约束管理：

11:52:53 SCOTT@ test1 >alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept(deptno);

Table altered.


11:54:29 SCOTT@ test1 >alter table emp1 drop constraint fk_emp1;

Table altered.


11:54:47 SCOTT@ test1 >create index emp1_empno_ind on emp1(empno) tablespace indx;

Index created.


索引管理：

11:55:17 SCOTT@ test1 >drop index emp1_empno_ind;

Index dropped.


配置read write：

11:55:27 SCOTT@ test1 >alter table emp1 read write;

Table altered.


11:55:37 SCOTT@ test1 >select read_only from user_tables where table_name='EMP1';

REA

---

NO


在11g前的版本中，若想对表设置为只读，可以通过赋予SELECT对象权限给这些用户，但表的拥有者还是读写的。而Oracle 11g 允许表标记为只读（read-only)通过ALTER  TABLE 命令。

可以通过下面命令对表读写权限进行设置：

  ALTER   TABLE   table_name READ ONLY;

  ALTER   TABLE   table_name READ WRITE;

简单示例如下：

CREATE   TABLE ro_test (

     id  number

 );

INSERT   INTO  ro_test  VALUES (1);

ALTER  TABLE   ro_test  READ ONLY;

 

任何影响表数据的DML语句和SELECT...FOR UPDATE查询语句都返回ORA-12081错误信息

SQL> INSERT INTO ro_test   VALUES (2); 
INSERT INTO ro_test   VALUES (2) 
            * 
ERROR at line 1: 
ORA-12081: update operation not allowed on table "TEST"."RO_TEST"


SQL> UPDATE ro_test   SET id = 2; 
UPDATE ro_test   SET id = 2 
       * 
ERROR at line 1: 
ORA-12081: update operation not allowed on table "TEST"."RO_TEST"

郑州不孕不育医院：http://yyk.39.net/zz3/zonghe/1d427.html

SQL> DELETE FROM ro_test; 
DELETE FROM ro_test 
            * 
ERROR at line 1: 
ORA-12081: update operation not allowed on table "TEST"."RO_TEST"

影响表数据的DDL语句也受限制

SQL> TRUNCATE TABLE ro_test; 
TRUNCATE TABLE ro_test 
               * 
ERROR at line 1: 
ORA-12081: update operation not allowed on table "TEST"."RO_TEST"


SQL> ALTER TABLE ro_test ADD (description VARCHAR2(50)); 
ALTER TABLE ro_test ADD (description VARCHAR2(50)) 
* 
ERROR at line 1: 
ORA-12081: update operation not allowed on table "TEST"."RO_TEST"

表是只读表但在与之相关的索引上操作不受影响。当表切换回读写模式时DML和DDL操作恢复正常。

SQL> ALTER TABLE ro_test READ WRITE;

Table altered.

SQL> DELETE FROM ro_test;

1 row deleted.

SQL>