create or replace procedure sys.rebuildindex is

tx varchar2(1000);

/*create table index_rebuid (name varchar2(30),del_lf_rows number(12),lf_rows number(12),frag_pct varchar2(10));

*/

cursor cur is

select 'analyze index ' || a.owner || '.' || index_name ||

' validate structure' as sql_text

from dba_indexes a

where a.table_name = 'TJFPERSONINFO';

begin

for i in cur loop

tx := i.sql_text;

execute immediate tx;

insert into index_rebuid

(name, del_lf_rows, lf_rows, frag_pct)

select name,

del_lf_rows,

lf_rows,

round(del_lf_rows / decode(lf_rows, 0, 1, lf_rows) * 100, 0) || '%' frag_pct

from index_stats

where round(del_lf_rows / decode(lf_rows, 0, 1, lf_rows) * 100, 0) > 10;

end loop;

commit;

end;

ITPub博客

统计需要重建的索引