Oracle 12.2新特性----在线move表
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操作
--创建测试表
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关键字。
--创建测试表
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