Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
During the process of an online index rebuild operation, when the related session is unexpectedly interrupted, even though there is no rebuild process, it seems that like the rebuild process is still in progress.
For this reason, it is caused ORA-08104 error when trying to perform the rebuild process again.
Rebuild process ends with error as follows.
1 2 3 4 5 | SQL> ALTER INDEX user.index rebuild online; ALTER INDEX user.index rebuild online * ERROR at line 1: ORA-08104: this index object 1234567 is being online built or rebuilt |
You can resolve this problem by running the dbms_repair.online_index_clean function.
Example use is as follows:
You must type the problematic index id instead of index_object_id. You can access this information from dba_objects, user_indexes or all_indexes. You need to connect and run with SYS.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | -bash-4.3$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 26 09:58:08 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> Begin declare lv_ret BOOLEAN; begin lv_ret := dbms_repair.online_index_clean(index_object_id); end; end; / |
