批量重建索引

set   serveroutput   on   size   50000;

/*--
1、存储过程使用了dba视图，所以执行行时必须用sys账户进去执行exec tsioms.rebuildindex;
2、排除了带有大字段表索引情况
3、排除 “UNUSA“，”VALID“，已经索引高度和高水位情况
--*/

CREATE OR REPLACE PROCEDURE TSIOMS."REBUILDINDEX"

is
cursor spetial is
select index_name,table_owner from user_indexes where table_owner='TSIOMS'and status='UNUSA';
cursor index_name is
--因为该库索引表空间较小，如果一次把所有全部执行 会报ORA-02327 错误，采用分批执行  每次执行
--前需要把like  A——Z  替换。
select index_name, table_owner from user_indexes where table_name like 'B%' and table_owner = 'TSIOMS' and status = 'VALID' and table_name not in (select distinct table_name  from user_tab_columns where data_type like '%LO%');

height index_stats.height%TYPE :=0;
lf_rows index_stats.lf_rows%TYPE :=0;
del_lf_rows index_stats.del_lf_rows%TYPE :=0;
distinct_keys index_stats.distinct_keys%TYPE :=0;
begin
for c_spetial in spetial loop
execute immediate 'alter index '|| c_spetial.table_owner ||'.'|| c_spetial.index_name ||' rebuild online nologging parallel';
end loop;
for c_index_name in index_name loop
execute immediate 'alter index '|| c_index_name.table_owner ||'.'|| c_index_name.index_name ||' rebuild online nologging parallel';
end loop;

for indexname in index_name loop
execute immediate 'analyze index '|| indexname.table_owner ||'.'|| indexname.index_name ||' validate structure';
select height,decode(lf_rows,0,1,lf_rows),del_lf_rows,decode(distinct_keys,0,1,distinct_keys) into height,lf_rows,del_lf_rows,distinct_keys from index_stats;
if(height>=4) or ((del_lf_rows/lf_rows)>0.2) then
dbms_output.put_line('The height of '|| indexname.table_owner || '.'|| indexname.index_name || ' is '||height || ' and the DEL_LF_ROWS/LF_ROWS is ' || del_lf_rows/lf_rows || ' needs rebuild!!!');
execute immediate 'alter index '|| indexname.table_owner ||'.'|| indexname.index_name ||' rebuild online nologging parallel';
end if;
end loop;

end REBUILDINDEX;

-----------------------------------------其它手记---------------------

declare
cursor spetial is
select index_name,owner from dba_indexes where owner not in
('SYS','SYSMAN','SYSTEM','MDSYS','OLAPSYS','DMSYS','ORDSYS','EXFSYS','XDB','CTXSYS','WMSYS','OUTLN','ISMSYS','DBSNMP','TSMSYS') and status='UNUSA';
cursor index_name is
select index_name,owner from dba_indexes where owner not in
('SYS','SYSMAN','SYSTEM','MDSYS','OLAPSYS','DMSYS','ORDSYS','EXFSYS','XDB','CTXSYS','WMSYS','OUTLN','ISMSYS','DBSNMP','TSMSYS') and status='VALID';
height index_stats.height%TYPE :=0;
lf_rows index_stats.lf_rows%TYPE :=0;
del_lf_rows index_stats.del_lf_rows%TYPE :=0;
distinct_keys index_stats.distinct_keys%TYPE :=0;
begin
for c_spetial in spetial loop
dbms_output.put_line(c_spetial.owner ||'.' || c_spetial.index_name ||' is unusable.');
end loop;
for c_index_name in index_name loop
dbms_output.put_line(c_index_name.owner ||'.' || c_index_name.index_name ||' is vaild.');
end loop;

for indexname in index_name loop
execute immediate 'analyze index '|| indexname.owner ||'.'|| indexname.index_name ||' validate structure';
select height,decode(lf_rows,0,1,lf_rows),del_lf_rows,decode(distinct_keys,0,1,distinct_keys) into height,lf_rows,del_lf_rows,distinct_keys from index_stats;
if(height>=4) or ((del_lf_rows/lf_rows)>0.2) then
dbms_output.put_line('The height of '|| indexname.owner || '.'|| indexname.index_name || ' is '||height || ' and the DEL_LF_ROWS/LF_ROWS is ' || del_lf_rows/lf_rows || ' needs rebuild!!!');
execute immediate 'alter index '|| indexname.owner ||'.'|| indexname.index_name ||' rebuild online nologging parallel';
end if;
end loop;
end;
/
----------------------------------------------------------------------
alter index PROD_SKDI_SRCE_REGN_ID rebuild online nologging parallel;

 execute immediate  ('ANALYZE INDEX  '||Id.Index_Name||'  VALIDATE  STRUCTURE ');

 set   serveroutput   on   size   50000;

declare
cursor spetial is
select index_name,table_owner from user_indexes where table_owner='TSIOMS'and status='UNUSA';
cursor index_name is
select index_name,table_owner from user_indexes where table_owner='TSIOMS'and status='VALID';
height index_stats.height%TYPE :=0;
lf_rows index_stats.lf_rows%TYPE :=0;
del_lf_rows index_stats.del_lf_rows%TYPE :=0;
distinct_keys index_stats.distinct_keys%TYPE :=0;
begin
for c_spetial in spetial loop
dbms_output.put_line(c_spetial.table_owner ||'.' || c_spetial.index_name ||' is unusable.');
end loop;
for c_index_name in index_name loop
dbms_output.put_line(c_index_name.table_owner ||'.' || c_index_name.index_name ||' is vaild.');
end loop;
for indexname in index_name loop
execute immediate 'analyze index '|| indexname.table_owner ||'.'|| indexname.index_name ||' validate structure';
select height,decode(lf_rows,0,1,lf_rows),del_lf_rows,decode(distinct_keys,0,1,distinct_keys) into height,lf_rows,del_lf_rows,distinct_keys from index_stats;
if(height>=4) or ((del_lf_rows/lf_rows)>0.2) then
dbms_output.put_line('The height of '|| indexname.table_owner || '.'|| indexname.index_name || ' is '||height || ' and the DEL_LF_ROWS/LF_ROWS is ' || del_lf_rows/lf_rows || ' needs rebuild!!!');
execute immediate 'alter index '|| indexname.table_owner ||'.'|| indexname.index_name ||' rebuild online nologging parallel';
end if;
end loop;
end;
/

CREATE OR REPLACE PROCEDURE "REBUILDINDEX"
is
cursor spetial is
select index_name,table_owner from user_indexes where table_owner='TSIOMS'and status='UNUSA';
cursor index_name is
select index_name,table_owner from user_indexes where table_owner='TSIOMS'and status='VALID';
height index_stats.height%TYPE :=0;
lf_rows index_stats.lf_rows%TYPE :=0;
del_lf_rows index_stats.del_lf_rows%TYPE :=0;
distinct_keys index_stats.distinct_keys%TYPE :=0;
begin
for c_spetial in spetial loop
dbms_output.put_line(c_spetial.table_owner ||'.' || c_spetial.index_name ||' is unusable.');
execute immediate 'alter index '|| indexname.table_owner ||'.'|| c_spetial.index_name ||' rebuild online nologging parallel';
end loop;
for c_index_name in index_name loop
dbms_output.put_line(c_index_name.table_owner ||'.' || c_index_name.index_name ||' is vaild.');
execute immediate 'alter index '|| indexname.table_owner ||'.'||c_index_name.index_name||' rebuild online nologging parallel';
end loop;
for indexname in index_name loop
execute immediate 'analyze index '|| indexname.table_owner ||'.'|| indexname.index_name ||' validate structure';
select height,decode(lf_rows,0,1,lf_rows),del_lf_rows,decode(distinct_keys,0,1,distinct_keys) into height,lf_rows,del_lf_rows,distinct_keys from index_stats;
if(height>=4) or ((del_lf_rows/lf_rows)>0.2) then
dbms_output.put_line('The height of '|| indexname.table_owner || '.'|| indexname.index_name || ' is '||height || ' and the DEL_LF_ROWS/LF_ROWS is ' || del_lf_rows/lf_rows || ' needs rebuild!!!');
execute immediate 'alter index '|| indexname.table_owner ||'.'|| indexname.index_name ||' rebuild online nologging parallel';
end if;
end loop;
end REBUILDINDEX;

  set   serveroutput   on   size   50000;

declare
cursor spetial is
select index_name,table_owner from user_indexes where table_owner='TSIOMS'and status='UNUSA';
cursor index_name is
select index_name, table_owner from user_indexes where table_name like'B%' and table_owner = 'TSIOMS' and status = 'VALID' and table_name not in (select distinct table_name  from user_tab_columns where data_type like '%LO%');

height index_stats.height%TYPE :=0;
lf_rows index_stats.lf_rows%TYPE :=0;
del_lf_rows index_stats.del_lf_rows%TYPE :=0;
distinct_keys index_stats.distinct_keys%TYPE :=0;
begin
for c_spetial in spetial loop
execute immediate 'alter index '|| c_spetial.table_owner ||'.'|| c_spetial.index_name ||' rebuild online nologging parallel';
end loop;
for c_index_name in index_name loop
execute immediate 'alter index '|| c_index_name.table_owner ||'.'|| c_index_name.index_name ||' rebuild online nologging parallel';
end loop;

for indexname in index_name loop
execute immediate 'analyze index '|| indexname.table_owner ||'.'|| indexname.index_name ||' validate structure';
select height,decode(lf_rows,0,1,lf_rows),del_lf_rows,decode(distinct_keys,0,1,distinct_keys) into height,lf_rows,del_lf_rows,distinct_keys from index_stats;
if(height>=4) or ((del_lf_rows/lf_rows)>0.2) then
dbms_output.put_line('The height of '|| indexname.table_owner || '.'|| indexname.index_name || ' is '||height || ' and the DEL_LF_ROWS/LF_ROWS is ' || del_lf_rows/lf_rows || ' needs rebuild!!!');
execute immediate 'alter index '|| indexname.table_owner ||'.'|| indexname.index_name ||' rebuild online nologging parallel';
end if;
end loop;
end;
/

--execute immediate 'alter index '|| c_spetial.table_owner ||'.'|| c_spetial.index_name ||' rebuild online nologging parallel';

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ONLYONE_HTLIU/archive/2010/11/15/6010480.aspx

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25088941/viewspace-681848/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25088941/viewspace-681848/