When creating or rebuilding indexes on hot tables, we will use parallel option to make the it quickly, but please remember to disable the parallel after creating or rebuilding, else it will make the relative SQL run in parallel. On busy OLTP system this will make the oracle instance crash (we hit this before). But you may hit resource busy error when you are trying to disable the parallel option of the index. How to make sure the modification succeed? Please use the following PL/SQL scripts:
alter index ...... rebuild ... parallel ... ONLINE;
declare
resource_busy exception;
pragma exception_init (resource_busy,-54);
begin
loop
begin
execute immediate 'alter index ...... noparallel';
exit;
exception
when resource_busy then
dbms_lock.sleep(1);
end;
end loop;
end;
/
These scripts can also be used in other cases, for example adding column on hot tables. Or you can change the error code to process another kind of error, it's all up to you!