Wednesday, March 22, 2017

ORA-08104: this index object is being online built or rebuilt

Got this error after session was killed (or if we have done a ctrl + C) while doing an index rebuild and then trying to rebuild the index again, its seems the cause is – A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is ongoing when in fact it is not

SQL> ALTER INDEX ORAUSER.TEST_INDX
REBUILD
TABLESPACE EPS_DATA
STORAGE (
         INITIAL     64M
         NEXT        64M
        )
ONLINE;
ALTER INDEX ORAUSER.TEST_INDX
*
ERROR at line 1:
ORA-08104: this index object 18902057 is being online built or rebuilt

SQL> select obj# ,name from obj$ where OBJ#=18902057;

      OBJ# NAME
---------- ------------------------------
  18902057 TEST_INDX

SQL> select obj#,flags from ind$ where obj#=18902057;

      OBJ#      FLAGS
---------- ----------
  18902057        517


SQL> declare
   lv_ret BOOLEAN;
    begin
    lv_ret :=dbms_repair.online_index_clean(18902057);
    end;
    /

PL/SQL procedure successfully completed.

SQL> select obj#,flags from ind$ where obj#=18902057;

      OBJ#      FLAGS
---------- ----------
  18902057          5

SQL> select obj# ,name from obj$ where OBJ#=18902057;

      OBJ# NAME
---------- ------------------------------
  18902057 TEST_INDX

SQL> ALTER INDEX ORAUSER.TEST_INDX
REBUILD
TABLESPACE EPS_DATA
STORAGE (
         INITIAL     64M
         NEXT        64M
        )
ONLINE;

Index altered.

No comments:

Post a Comment