MoveTable and Reindex

--collect index info
create table idx_stats
as select * from index_stats where 1=2

begin
 for mycur in (select index_name from user_indexes) loop
  execute immediate 'analyze index '|| mycur.index_name ||
  ' validate structure';
    insert into idx_stats select * from index_stats;
  commit;
 end loop;
end;

--move table
SET SERVEROUTPUT ON ;
begin
 for mycur in (select table_name from user_tables  where TABLESPACE_NAME='TS_GES_01' AND table_name!='TOAD_PLAN_TABLE') loop
    dbms_output.put_line('move table:'|| mycur.table_name);
  execute immediate 'alter table '|| mycur.table_name ||' move';
 end loop;
end;

--rebulid index
select 'alter index ' ||name || ' rebuild online;'
from idx_stats
where DEL_LF_rows/nullif(LF_Rows,0)>=0

--check table
select name,table_space from dba_tables where owner='POLY'

--check index
select index_name,status from user_indexes

--load new index stat
create table idx_stats2

as select * from index_stats where 1=2
begin
 for mycur in (select index_name from user_indexes) loop
  execute immediate 'analyze index '|| mycur.index_name ||
  ' validate structure';
    insert into idx_stats2 select * from index_stats;
  commit;
 end loop;
end;
--compare index stat
select * from poly.idx_stats --142
WHERE DEL_LF_rows/nullif(LF_Rows,0)>=0 --115
order by DEL_LF_rows desc

select * from poly.idx_stats2 --142
WHERE DEL_LF_rows/nullif(LF_Rows,0)>=0 --115
order by DEL_LF_rows

select a.name, a.DEL_LF_rows/nullif(a.LF_Rows,0) as ratio_a,
 b.DEL_LF_rows/nullif(b.LF_Rows,0) as ratio_b
from poly.idx_stats a
inner join  poly.idx_stats2 b on a.name=b.name
order by 2 desc

 

--recompile package
SELECT
 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type) ||' '||owner||
  '."'||object_name||'" '||decode(object_type,'PACKAGE BODY','COMPILE BODY','COMPILE')||';'
 FROM all_objects
 WHERE owner like '%'
 AND owner not in ('SYS', 'SYSTEM')
 AND object_type IN
 ('PACKAGE','PACKAGE BODY','VIEW','PROCEDURE','TRIGGER','FUNCTION')
 AND status='INVALID';