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.26zx@ORA11G>insert into t select * from t;79608 rows created.Elapsed: 00:00:00.22zx@ORA11G>/159216 rows created.Elapsed: 00:00:00.38zx@ORA11G>/318432 rows created.Elapsed: 00:00:03.63zx@ORA11G>/636864 rows created.Elapsed: 00:00:05.40zx@ORA11G>/1273728 rows created.Elapsed: 00:00:24.57zx@ORA11G>select bytes/1024/1024 from user_segments;BYTES/1024/1024---------------            392Elapsed: 00:00:00.07zx@ORA11G>commit;Commit complete.Elapsed: 00:00:00.01zx@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.13zx@ORA11G>rollback;Rollback complete.--执行move--session 1zx@ORA11G>select userenv('sid') from dual;USERENV('SID')--------------          1150--session 2  zx@ORA11G>select userenv('sid') from dual;USERENV('SID')--------------            15--session 1zx@ORA11G>alter table t move tablespace examples;Table altered.Elapsed: 00:00:02.45--session 2zx@ORA11G>delete from t where object_name='T';32 rows deleted.Elapsed: 00:00:02.18zx@ORA11G>rollback;Rollback complete.--session 3sys@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          07 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.44zx@ORA12C>rollback;Rollback complete.--session 1zx@ORA12C>select userenv('sid') from dual;USERENV('SID')--------------            23--session 2zx@ORA12C>select userenv('sid') from dual;USERENV('SID')--------------            27--执行move操作--session 1zx@ORA12C>alter table t move online tablespace examples;Table altered.Elapsed: 00:00:34.73--session 2zx@ORA12C>delete from t where object_name='USER_TABLES';256 rows deleted.Elapsed: 00:00:00.97zx@ORA12C>rollback;Rollback complete.--session 3sys@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          016 rows selected.

从上面的操作可以看出12.2的move操作没有阻塞delete操作。

参考：http://docs.oracle.com/database/122/NEWFT/new-features.htm#GUID-BEEEA34D-3D81-4360-887C-A92BC711816D