分享程序员开发的那些事...
更新时间：2022-10-01 22:33:44
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 TABLECREDIT_CLUSTER CLUSTERCREDIT_ORDERS TABLE 1
DEPT TABLEEMP TABLEEMP1 TABLE11: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 |