自動重建索引

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;