1.shell脚本的方式
index_re.sh
sqlplus / as sysdba <<EOF
spool /tmp/i.sql rep
select ‘alter index ‘||owner||’.”‘||index_name||'” rebuild;’ from dba_indexes where owner=’SCOTT’ and status=’UNUSABLE’;
spool off
ho sed ‘/^alter index /p’ -n /tmp/i.sql > /tmp/i1.sql
start /tmp/i1.sql
EOF
2.匿名PLSQL
begin
for i in (select index_name from user_indexes where status = ‘UNUSABLE’) loop
execute immediate ‘alter index “‘||i.index_name||'” rebuild’;
end loop;
end;
/
3.调用过程
create or replace procedure index_rebuild
is
begin
for i in (select index_name from user_indexes where status = ‘UNUSABLE’) loop
execute immediate ‘alter index “‘||i.index_name||'” rebuild’;
end loop;
end;
/
exec index_rebuild;