analyze_table_index

rem -----------------------------------------------------------------------
rem Filename:   analyze_table_index.sql
rem Purpose:    Analyze all table and index
rem -----------------------------------------------------------------------

--set feed off echo off head off trimspool on line 500

spool /home/appprod/Desktop/spool1.sql
select 'ANALYZE TABLE '||owner||'.'||segment_name||' compute statistics;'
from   sys.dba_segments where owner='TEST' and segment_type='TABLE'
/
select 'ANALYZE INDEX '||owner||'.'||index_name||' validate structure;'
from   sys.dba_indexes where owner='TEST'
/

spool off
/
set feed on
@//home/appprod/Desktop/spool1.sql
! rm /home/appprod/Desktop/spool1.sql