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';