标题: Alter Index Rebuild Online Report ORA-00054 [打印本页] 作者: 刘泽宇 时间: 2024-11-3 10:54 标题: 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.