Wednesday, May 23, 2018

rebuild unusable indexes

Set serverout on size 1000000
declare
    cursor idx_cur IS
      select owner, index_name  from dba_indexes where status = 'UNUSABLE';
    v_string varchar2(400);
   
begin
    dbms_output.put_line('These indexes are in unusable.');
    for idx_rec in idx_cur
    loop
        exit when idx_cur%notfound;
           dbms_output.put_line(idx_rec.owner||' '||idx_rec.index_name);
           v_string := 'alter index '|| idx_rec.owner ||'.'|| idx_rec.index_name||' rebuild;';
           dbms_output.put_line(v_string);
           dbms_output.put_line(chr(10));
           execute immediate v_string ;
    end loop;
end;
/