Oracle12.2版本之前，对表做move操作时会对表加exclusive锁，表上无法执行DML操作。虽然move操作有ONLINE子句，但只适用于IOT表，不适用于堆表。这就意味着在对表做move操作时，无法执行任何DML操作，如果对关键表做move操作时只能停业务来完成。到了Oracle12.2版本，推出了一个新特性----在线move表，对于普通堆表可以在move过程中执行DML操作。
下面以11.2.0.4和12.2.0.1这两个版本为对比，观察这一新特性。
1、11.2.0.4版本的move操作
|
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
|
--创建测试表
zx@ORA11G>
create
table
t
as
select
*
from
dba_objects;
Table
created.
Elapsed: 00:00:00.26
zx@ORA11G>
insert
into
t
select
*
from
t;
79608
rows
created.
Elapsed: 00:00:00.22
zx@ORA11G>/
159216
rows
created.
Elapsed: 00:00:00.38
zx@ORA11G>/
318432
rows
created.
Elapsed: 00:00:03.63
zx@ORA11G>/
636864
rows
created.
Elapsed: 00:00:05.40
zx@ORA11G>/
1273728
rows
created.
Elapsed: 00:00:24.57
zx@ORA11G>
select
bytes/1024/1024
from
user_segments;
BYTES/1024/1024
---------------
392
Elapsed: 00:00:00.07
zx@ORA11G>
commit
;
Commit
complete.
Elapsed: 00:00:00.01
zx@ORA11G>
alter
system flush buffer_cache;
System altered.
Elapsed: 00:00:27.90
--不做move操作时delete操作时间
zx@ORA11G>
delete
from
t
where
object_name=
'T'
;
32
rows
deleted.
Elapsed: 00:00:00.13
zx@ORA11G>
rollback
;
Rollback
complete.
--执行move
--session 1
zx@ORA11G>
select
userenv(
'sid'
)
from
dual;
USERENV(
'SID'
)
--------------
1150
--session 2
zx@ORA11G>
select
userenv(
'sid'
)
from
dual;
USERENV(
'SID'
)
--------------
15
--session 1
zx@ORA11G>
alter
table
t
move
tablespace examples;
Table
altered.
Elapsed: 00:00:02.45
--session 2
zx@ORA11G>
delete
from
t
where
object_name=
'T'
;
32
rows
deleted.
Elapsed: 00:00:02.18
zx@ORA11G>
rollback
;
Rollback
complete.
--session 3
sys@ORA11G>
select
/*+
rule
*/ sid,lmode,request,type,block
from
v$lock
where
sid
in
(1150,15);
SID LMODE REQUEST TY BLOCK
---------- ---------- ---------- -- ----------
1150 6 0 TM 1
1150 4 0 AE 0
1150 6 0 TS 0
1150 6 0 TX 0
1150 2 0 XR 0
15 4 0 AE 0
15 0 3 TM 0
7
rows
selected
|
从上面的查询中可以看出表move操作阻塞了delete操作。
2、下面来看12.2版本的在线move操作，需要添加online关键字。
|
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
|
--创建测试表
zx@ORA12C>
select
segment_name,bytes/1024/1024
from
user_segments;
SEGMENT_NAME BYTES/1024/1024
------------------------------ ---------------
T 392
--在没有move时delete操作时间
zx@ORA12C>
delete
from
t
where
object_name=
'USER_TABLES'
;
256
rows
deleted.
Elapsed: 00:00:00.44
zx@ORA12C>
rollback
;
Rollback
complete.
--session 1
zx@ORA12C>
select
userenv(
'sid'
)
from
dual;
USERENV(
'SID'
)
--------------
23
--session 2
zx@ORA12C>
select
userenv(
'sid'
)
from
dual;
USERENV(
'SID'
)
--------------
27
--执行move操作
--session 1
zx@ORA12C>
alter
table
t
move
online tablespace examples;
Table
altered.
Elapsed: 00:00:34.73
--session 2
zx@ORA12C>
delete
from
t
where
object_name=
'USER_TABLES'
;
256
rows
deleted.
Elapsed: 00:00:00.97
zx@ORA12C>
rollback
;
Rollback
complete.
--session 3
sys@ORA12C>
select
/*+
rule
*/ sid,lmode,request,type,block
from
v$lock
where
sid
in
(23,27);
SID LMODE REQUEST TY BLOCK
---------- ---------- ---------- -- ----------
27 3 0 TM 0
27 4 0 AE 0
27 6 0 TX 0
23 4 0 AE 0
23 6 0 OD 0
23 3 0 DL 0
23 3 0 DL 0
23 6 0 TS 0
23 6 0 TM 0
23 4 0 TM 0
23 6 0 TM 0
23 3 0 TM 0
23 6 0 TX 0
23 6 0 TX 0
23 3 0 TM 0
23 3 0 TM 0
16
rows
selected.
|
从上面的操作可以看出12.2的move操作没有阻塞delete操作。
参考：http://docs.oracle.com/database/122/NEWFT/new-features.htm#GUID-BEEEA34D-3D81-4360-887C-A92BC711816D
3174
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
