Oracle: Rebuilding unusable index

My script to automatically rebuilding unusable indexes. Use at your own risk. As a good dba, you should look through the script before using. There was request on automating this so it can be put on dbms scheduler. Some batch jobs (likely follow by tables repartitioning) will alter the base tables and causing indexes stay in unusable mode. Unusable indexes could cause performance issues and preventing record to be altered (update or insert).



set serveroutput on;
set feedback off;
--spool bad_index.txt
declare

cursor bad_index
is select index_name, status from dba_indexes where status='UNUSABLE';

v_bad_index bad_index%rowtype;
--sqlstmt VARCHAR2(200);
begin
dbms_output.put_line ('INDEX NAME                           STATUS ');
dbms_output.put_line('#############################################');

open bad_index;
loop
fetch bad_index into v_bad_index;
--handles here, if no row found, it exits from here instead of later on where the pl/sql will error out.
-- do not use if then else to catch this no row found. not worthy. It has performance hits and triggering ORA-04030:
EXIT WHEN bad_index%NOTFOUND;


dbms_output.put_line(v_bad_index.index_name || ' >>>>>>> ' ||  v_bad_index.status);

--sqlstmt:='ALTER INDEX '|| v_bad_index.index_name ||' REBUILD online';
--EXECUTE IMMEDIATE sqlstmt;

execute immediate 'ALTER INDEX '|| v_bad_index.index_name ||' REBUILD online';
--note: use full execute instead of exec. There is a diffferent in pl/sql. exec will error with missing symbol.

end loop;
close bad_index;
end;
/

No comments:

Post a Comment