Oracle 11g新特性之--只读表（read only table）

32rwqe

    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 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101

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