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.
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