Rebuild unusable indexes thru SP
Can I automatically schedule a stored procedure to rebuild my unusable indexes which is happening a lot in my DB since there is direct dataload almost every night. We are using 9i. ANy suggestion ?
yes you can
have a loop to find them and exec immediate to rebuild them
Can you please provide me a sample example quick and dirty?
you will only learn if you do it you do it yuorself, here is some pseudo code
for (each index which is unusable) loop
execute immediate 'rebuild that index';
end the loop
Here is an example...
FOR c1_rec IN
(select * from user_indexes where status = 'UNUSABLE')
stmt := 'ALTER INDEX ' || c1_rec.index_name || ' REBUILD';
execute immediate stmt;
Click Here to Expand Forum to Full Width