Blocking TXN ID for DDL wait event encountered on Oracle 11GR2

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

In a recently processed case (11.2.0.4 version of Oracle DB), the blocking Txn ID for DDL was encountered for this wait event.

Here's how it was done:

CREATE INDEX idx_tab_1 on table_name (column_name)   tablespace xxx  online;

The index-based operation was completed with 17 minutes, 7 seconds. After I generate the AWR report for that time period, intercept the TOP10 wait events as follows:

The wait time for the blocking TXN ID for DDL is 1023.1 seconds, 17minute7second = 1027 seconds, which means that the most of the CREATE INDEX statement was waiting.

Later, the ash report for that time period was generated, and in the ash report it was clear that the wait event for the CREATE index operation was blocking TXN ID for DDL, as follows:

In the MOS article alter Table ADD Column Command hangs with the wait event ' blocking TXN ID for DDL ' (document ID 1553725.1), a clear description of the waiting events:

The original text reads 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.

The translation is as follows:

The DDL command requires an exclusive lock on the internal structure. If these locks are not available, the DDL command fails and returns a "Ora-00054:resource busy" error message.

In Oracle 11.2, replacing the Ora-54,alter table statement will not immediately return an error, but will wait indefinitely.

Before 11g, if these locks are not available, the DDL command fails and returns a "Ora-00054:resource busy" error message.

Encountered blocking TXN ID for DDL wait event on Oracle 11gr2