Monday, December 02, 2024

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

If you are getting this error while trying to execute DROP INDEX after unsuccessfully trying to create an index with the ONLINE clause:
DROP INDEX xyz;

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

then you might need to call function DBMS_REPAIR.ONLINE_INDEX_CLEAN to be able to retry the DROP INDEX statement, however there are two caveats:

  1. DBMS_REPAIR is not granted by default to all users, so you probably need to ask the DBA for the grant execute first;
  2. When I tried the operation the DBA granted temporarily DBA role to my user, just in case, so I don't know if this is really necessary as the documentation doesn't mention it. 

The first parameter of the function call is the object ID mentioned in the error message.
The second optional parameter wait_for_lock (missing in the code below) enables a retry mechanism in case the underlying table is locked for a short time by some other sessions.

set serveroutput on
DECLARE
b boolean;
BEGIN 
b := sys.dbms_repair.online_index_clean(983257);
if b then
dbms_output.put_line('cleanup successful');
else
dbms_output.put_line('cleanup failed');
end if;
END;
/

In my case after running the function, I could drop the index successfully.

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles