set linesize 300
set serveroutput on size 100000
set feedback off
set timing on
set term off
set head off
spool rebuild.log
begin
dbms_output.enable('9999999');
for rIndex in (select s.owner,s.segment_name,sum(s.bytes/1024/1024)
from dba_segments s , dba_indexes i
where s.segment_type = 'INDEX'
and s.segment_name = i.index_name
and s.owner = i.owner
and i.index_type = 'NORMAL'
and s.owner not in ('SYS','SYSTEM','SYSMAN','SYSAUX','APPLSYS')
group by s.owner,s.segment_name
having sum(bytes/1024/1024) > 100) loop
execute immediate 'analyze index '||rIndex.owner||'.'||rIndex.segment_name||' validate structure';
end loop;
dbms_output.put_line('--- Reorganizando os indices ---');
for rRebuild in(SELECT name, height, lf_rows, owner
FROM INDEX_STATS st, dba_indexes di
where st.name = di.index_name) loop
execute immediate 'alter index '||rRebuild.owner||'.'||rRebuild.name||' rebuild online';
end loop;
end;
/
spool off
set serveroutput on size 100000
set feedback off
set timing on
set term off
set head off
spool rebuild.log
begin
dbms_output.enable('9999999');
for rIndex in (select s.owner,s.segment_name,sum(s.bytes/1024/1024)
from dba_segments s , dba_indexes i
where s.segment_type = 'INDEX'
and s.segment_name = i.index_name
and s.owner = i.owner
and i.index_type = 'NORMAL'
and s.owner not in ('SYS','SYSTEM','SYSMAN','SYSAUX','APPLSYS')
group by s.owner,s.segment_name
having sum(bytes/1024/1024) > 100) loop
execute immediate 'analyze index '||rIndex.owner||'.'||rIndex.segment_name||' validate structure';
end loop;
dbms_output.put_line('--- Reorganizando os indices ---');
for rRebuild in(SELECT name, height, lf_rows, owner
FROM INDEX_STATS st, dba_indexes di
where st.name = di.index_name) loop
execute immediate 'alter index '||rRebuild.owner||'.'||rRebuild.name||' rebuild online';
end loop;
end;
/
spool off
Comentários
Postar um comentário