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:
Oracle Database - Enterprise Edition - Version 11.2.0.4 and later Information in this document applies to any platform.
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>
Changes
Cause
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!