Oracle 12.2 New features----online move table

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.