Shell Script to Gather Stats

ADMIN="tyagi.ankur07@gmail.com"

sqlplus /nolog <<EOF
connect / as sysdba

set head off
set underline off
set pages 0
set veri off
set lines 130
spool last_analyzed.lst

--select distinct 'EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('''||owner||''''||',DBMS_STATS.AUTO_SAMPLE_SIZE);' from dba_tables where last_analyzed is null;
select distinct 'EXECUTE DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','''||table_name||''',cascade=>true);' from dba_tab_statistics where last_analyzed is null and STATTYPE_LOCKED <> 'ALL'; --added to avoid locked objects

spool off

--@last_analyzed.lst
exit;
EOF

echo -e "Gather stats completed" | mailx -s "stat compeleted" $ADMIN