Alter Index Rebuild Online Report ORA-00054

刘泽宇

现象：
Please see below test case
TEST ON 12.2.0.1, Same behavior on 11.2.0.4

SESSION 1
SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL> create table t01(
c1 number,
c2 varchar2(200)
); 2 3 4
Table created.
SQL> create index t01_n1 on t01(c2) ;
Index created.
SESSION 2
SQL> update t01 set c1=1;
0 rows updated.
SESSION 1
SQL> alter index t01_n1 rebuild online;
alter index t01_n1 rebuild online
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ==》 error happens when there is no segment

SQL> alter session set deferred_segment_creation=false;
Session altered.
SQL> create table t02 (
c1 number,
c2 varchar2(200)
); 2 3 4
Table created.
SQL> create index t02_n1 on t02(c2) ;
Index created.
SESSION 2
SQL> update t02 set c1=1;
0 rows updated.
SESSION 1
SQL> alter index t02_n1 rebuild online; ===> HANG HERE Wait event is ‘blocking txn id for DDL’.



Case is also reported after switch empty partition

-- 1. exchange an empty partition of "the history table" with current daily table, not including indexes;
alter table <history table name> exchange partition <partition name>with table <daily table name >;

-- 2. rebuild new current daily table's  index online
alter index <daily table's  index name> rebuild online;



原因：
Expected behavior

This hang is new feature in 11g, See
Alter Table Add Column Command Hangs With Wait Event 'blocking txn id for DDL' (Doc ID 1553725.1)

ORA-00054 is discussed in

Bug 22192163 : 47117-ORA0054 CREATE IDX ONLINE WITH PARALLEL DDL AND DML

Closed as ‘Not a Bug’，i.e. expected behavior, Bug explains:

If a table is completely empty and deferred, running a create index online of
a non-partitioned table concurrently with a insert may lead to ora-54
depending on the timing of the operations.


处理方法：
Expect Behavior
If you prefer to see a hang instead of ORA-00054, you could create a segment to workaround this.

e.g. Set deferred_segment_creation=false when create table or partition. Or allocate a real segment to existing table as below

alter table <table name> allocate extent;

alter table <table name> modify partition allocate extent;  

This would not consume much space, and no bad impact.