New feature of Oracle 12.2-online move table

Before the Oracle12.2 version, the table was locked with exclusive when performing move operations on the table, and the DML operation could not be performed on the table. Although the move operation has an ONLINE clause, it only applies to IOT tables, not to heap tables. This means that when you move a table, you cannot perform any DML operations. If you do move operations on key tables, you can only stop the business to do so. In the Oracle12.2 version, a new feature has been introduced-online move table. For ordinary heap tables, you can perform DML operations during the move process.

Take a look at this new feature by comparing versions 11.2.0.4 and 12.2.0.1.

1. Move operation in version 11.2.0.4

-- create a test table zx@ORA11G > create table t as select * from dba_objects;Table created.Elapsed: 00:00:00.26zx@ORA11G > insert into t select * from twit79608 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: 00VOV 27.90 move-delete operation time zx@ORA11G > delete from t where object_name='T';32 rows deleted.Elapsed: 00:00:00.13zx@ORA11G > rollback without delete operation Rollback complete.-- executes move--session 1zx@ORA11G > select userenv ('sid') from dual;USERENV (' SID')-1150--session 2zx@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 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 150 3 TM 07 rows selected

You can see from the above query that the table move operation blocks the delete operation.

2. Let's take a look at the online move operation of version 12.2. You need to add the online keyword.

-- create a test table zx@ORA12C > select segment_name,bytes/1024/1024 from user_segments SEGMENT_NAME BYTES/1024/1024---T 392 zx@ORA12C-delete operating time without move 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-perform move operation-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 Magi 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.

From the above operation, you can see that the move operation of 12.2 does not block the delete operation.

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