Alter Index Rebuild Online Hang With Wait Event "blocking txn id for DDL" (Doc ID 2728854.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

• Alter index rebuild online hang with wait event "blocking txn id for DDL", and blocker session is update statement which updated no row.
  
  
  
• This problem can be reproduced by following test case:
  
  
  grant dba to <USERNAME> identified by <PASSWORD>;
  conn <USERNAME>/<PASSWORD>
  create table <TABLE_NAME> (col number);
  create index <INDEX_NAME> on <TABLE_NAME>(col);
  insert into <TABLE_NAME> values (1);
  commit;
  
  Session 1:
  
  conn <USERNAME>/<PASSWORD>
  select sid, serial# from v$session where sid=(select userenv('sid') from dual);
  update <TABLE_NAME> set col=1 where 1<>1;
  
  Session 2:
  
  conn <USERNAME>/<PASSWORD>
  select sid, serial# from v$session where sid=(select userenv('sid') from dual);
  alter index <INDEX_NAME> rebuild online;
  
  session 3:
  col event for a32
  select s.sid,s.serial#,s.event,s.blocking_session
  from v$session s , v$process p
  where p.addr = s.paddr
  and s.username = '<USERNAME>';
  
  Test log:
  
  Session 1:
  
  SQL> select sid, serial# from v$session where sid=(select userenv('sid') from dual);
  
         SID    SERIAL#
  ---------- ----------
          42        691
  
  SQL> update <TABLE_NAME> set col=1 where 1<>1;
  
  0 rows updated.
  
  SQL>
  
  
  Session 2:
  
  SQL> select sid, serial# from v$session where sid=(select userenv('sid') from dual);
  
         SID    SERIAL#
  ---------- ----------
          37       3419
  
  SQL> alter index <INDEX_NAME> rebuild online; <<<<<<<<<
  
  
  
  session 3:
  
  SQL> col event for a32
  SQL> select s.sid,s.serial#,s.event,s.blocking_session
  from v$session s , v$process p
  where p.addr = s.paddr
  and s.username = '<USERNAME>';  2    3    4
  
         SID    SERIAL# EVENT                            BLOCKING_SESSION
  ---------- ---------- -------------------------------- ----------------
          37       3419 blocking txn id for DDL                        42
          42        691 SQL*Net message to client
  
  SQL>