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
|
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1591685，如需转载请自行联系原作者
