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:
- DBMS_REPAIR is not granted by default to all users, so you probably need to ask the DBA for the grant execute first;
- 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:
Post a Comment