How to Fix Oracle Index Creation Interrupted by Ctrl+C (ORA-08104)

Imagine you are building a large index using the ONLINE option, and the process suddenly gets interrupted—either by pressing Ctrl+C, a network disconnection, or running out of space. Now you find yourself in a frustrating state where you cannot recreate the index, nor can you drop it. In this article, we will examine why this happens and how to quickly resolve it.

1. Symptoms of the Problem

When an online index build is aborted, Oracle's data dictionary ends up in an inconsistent state. Trying to perform different operations on the index will result in conflicting errors:

• Recreating: Throws an error saying the name is already used (ORA-00955: name is already used by an existing object).
• Dropping: Claims that the index does not exist (ORA-01418: specified index does not exist).
• Rebuilding or Altering: Fails with the following message, indicating the index is still considered in-progress:

sys@searchcd(135)> ALTER INDEX SEARCH_ENG.IX_GOODS_REL_PRICE_ID rebuild;
ERROR at line 1:
ORA-08104: this index object 91565 is being online built or rebuilt

2. Why Does This Happen?

During an ONLINE CREATE INDEX operation, Oracle creates temporary internal objects (like journal tables) to allow concurrent DML transactions on the table. If you abort the execution mid-way, these metadata entries remain in the system tables (such as SYS.OBJ$) but are not visible in standard administrative views like DBA_INDEXES.

3. The Quick Fix: Using DBMS_REPAIR

The safest and most efficient way to manually clean up these orphan indexes is by using the DBMS_REPAIR package. Follow these simple steps:

Step 1: Find the Object ID

Query the dictionary table OBJ$ to locate the object ID (OBJ#) of the stuck index:

sys@searchcd(135)> select obj# ,name from obj$ where name='IX_GOODS_REL_PRICE_ID';

      OBJ# NAME
---------- -------------------------------------------------------
     91565 IX_GOODS_REL_PRICE_ID

Step 2: Clean Up the Index

Call the online_index_clean function using the retrieved OBJ# (in this case, 91565) to force-clean the metadata:

sys@searchcd(135)> declare
  lv_ret BOOLEAN;
begin
  lv_ret := dbms_repair.online_index_clean(91565);
end;
/
PL/SQL procedure successfully completed.

Step 3: Verify and Recreate the Index

Confirm the object has been removed from OBJ$. You can now recreate the index successfully:

sys@searchcd(135)> select obj# ,name from obj$ where OBJ#=91565;
no rows selected

sys@searchcd(135)> CREATE INDEX SEARCH_ENG.IX_GOODS_REL_PRICE_ID ON SEARCH_ENG.T_GOODS
  2  (RELEVANCE_SCORE DESC, PRICE DESC, ID)
  3  parallel 8 tablespace SEARCH_ENG;

Index created.

4. Other Scenarios Leading to This State

Apart from manual cancellation via Ctrl+C, you might encounter this issue due to:

• Network Dropouts: When the client connection drops, leaving a dead session on the database server.
• Temp Space Exceeded: Running out of space in the TEMP tablespace during the sorting phase.
• Parallel Query Failures: Errors in parallel query slaves (like ORA-12801) that interrupt the coordinator session.
• Instance Crash: Sudden power loss or server crash during an online DDL operation.

Brought to you by the Database Administration Team - Oracle 19c / 23ai / 26ai