When a blocking txn id for DDL wait event occurs on Oracle 11gR2, 11gr2txn

Source: Internet
Author: User

When a blocking txn id for DDL wait event occurs on Oracle 11gR2, 11gr2txn

This article is the original article, reproduced please indicate the source: http://blog.csdn.net/msdnchina/article/details/44726875

 

In a recent case (oracle db of version 11.2.0.4), we encountered the blocking txn id for DDL wait event.

The current operation is as follows:

create index idx_tab_1 on table_name (column_name)   tablespace xxx  online;
It takes 17 minutes and seven seconds to create an index. Then I generate an awr report for this period of time. The top 10 waiting events are intercepted as follows:

The wait time of blocking txn id for DDL is 1023.1 seconds, 17 minutes and 7 seconds = 1027 seconds. That is to say, the vast majority of the time of the create index statement was waiting.

 

Later, an ash report for this time period was generated. In the ash report, we can also clearly see that the wait event for the create index operation is blocking txn id for DDL, as shown below:

 

 

In the mos Article Alter Table Add Column Command Hangs With Wait Event 'blocking txn id for ddls' (Document ID 1553725.1), it is clear that the waiting Event:

The original article is as follows:

DDL commands require exclusive locks on internal structures. If these locks are not available, the commands return with an "ORA-00054: resource busy" error message.On Oracle 11.2 instead of ora-54, the ALTER TABLE statement will not error out immediately. Instead, it waits indefinitely.Prior to 11g, if these locks are not available, the DDL command will fail with an "ORA-00054: resource busy" error message.

Translation:

DDL commands require exclusive locks in the internal structure. If these locks are not available, the DDL command fails with the "ORA-00054: resource busy" error message returned.

In Oracle 11.2, instead of ora-54, the alter table statement does not return an error immediately, but waits indefinitely.
If these locks cannot be obtained before 11g, the DDL command fails and returns the "ORA-00054: resource busy" error message.

 

 

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.