Oracle 12.2 New features----online move table

Before the Oracle12.2 version, a move operation on a table adds a exclusive lock to the table, and the DML operation cannot be performed on the table. Although the move operation has an online clause, it applies only to IoT tables, not to heap tables. This means that you cannot perform any DML operations while doing a move operation on the table, and you can only stop the business if you are doing a move operation on the key table. In the Oracle12.2 version, a new feature has been introduced----the online move table, which allows DML operations to be performed during the move process for ordinary heap tables.

The following is a comparison of the two versions of 11.2.0.4 and 12.2.0.1 to observe this new feature.

1, 11.2. Version 0.4 move operation

--Create a test table [Email protected]>create table t as select * from dba_ Objects Table created. Elapsed: 00:00:00.26[email protected]>insert into t select * from t ; 79608 rows created. Elapsed: 00:00:00.22[email protected]>/159216 rows created. Elapsed: 00:00:00.38[email protected]>/318432 rows created. Elapsed: 00:00:03.63[email protected]>/636864 rows created. Elapsed: 00:00:05.40[email protected]>/1273728 rows created. elapsed: 00:00:24.57[email protected]>select bytes/1024/1024 from user_segments; bytes/1024/1024---------------    392Elapsed: 00:00:00.07[email protected]> Commit Commit complete. elapsed: 00:00:00.01[email protected]>alter system flush buffer_cache; System altered. elapsed: 00:00:27.90--does not make move operation when deLete operation time [email protected]>delete from t where object_name= ' t ';32 rows  Deleted. elapsed: 00:00:00.13[email protected]>rollback; rollback complete.--Execute move--session 1[email protected]>select userenv (' Sid ')  from  dual; USERENV (' SID ')--------------  1150--session 2  [email protected]>select  Userenv (' Sid ')  from dual; USERENV (' SID ')--------------    15--session 1[email protected]>alter  table t move tablespace examples; Table altered. elapsed: 00:00:02.45--session 2[email protected]>delete from t where  Object_name= ' T '; 32 rows deleted. elapsed: 00:00:02.18[email protected]>rollback; Rollback complete.--session 3[email protected]>select /*+ rule */ sid, lmode,request,type,block from v$lock where sid in  (1150,15);       sidlmode 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     015    4        0 AE     015    0       3 TM      07 rows selected

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

2. Below to see the 12.2 version of the online move operation, you need to add the online keywords.

--Create a test table [email protected]>select segment_name,bytes/1024/1024 from user_segments; segment_name       bytes/1024/1024------------------------------ ----- ----------t   392--Delete operation time when there is no move [email protected]>delete from t  Where object_name= ' User_tables '; 256 rows deleted. elapsed: 00:00:00.44[email protected]>rollback; Rollback complete.--session 1[email protected]>select userenv (' Sid ')  from  Dual USERENV (' SID ')--------------    23--session 2[email protected]>select  Userenv (' Sid ')  from dual; USERENV (' SID ')--------------    27--to perform a move operation--session 1[email protected]> alter table t move online tablespace examples; Table altered. Elapsed: 00:00:34.73--session 2[email protected]>delete from t wherE object_name= ' User_tables '; 256 rows deleted. elapsed: 00:00:00.97[email protected]>rollback; Rollback complete.--session 3[email protected]>select /*+ rule */ sid, lmode,request,type,block from v$lock where sid in  (23,27);        sidlmode request ty block---------- ---------- ----------  -- ----------27    3       0 tm      027    4       0 AE      027    6       0 TX      023    4       0 AE      023    6       0 OD      023    3       0 dl      023    3       0 DL      023    6       0 TS      023    6       0 TM      023    4       0 TM      023    6       0 TM      023    3       0 TM      023    6       0 TX      023    6       0 TX      023    3       0 tm     023     3       0 TM     016  Rows selected.

As you can see from the above operation, the 12.2 move operation does not block the delete operation.

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

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1946289

Oracle 12.2 New features----online move table