Script to start Oracle stats gathering in background from shell in Linux

Following is the script to gather stats for test.foo table. You can create a file at OS level and paste the following script:

touch gatherstats.sh
chmod a+x gatherstats.sh

then paste:

#!/bin/sh
sqlplus <<-EOF
/ as sysdba
set echo on timing on
exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'foo', ESTIMATE_PERCENT=>.01, METHOD_OPT=> 'FOR ALL INDEXED COLUMNS',CASCADE=>TRUE,DEGREE=>6);
exit
EOF
echo -e "Stat completed for table FOO." | mail -s "Stat completed"


Now you can run the script in background:

nohup ./gatherstats.sh &

The script will email you upon completion.



-------------------------------------------------x--------------------------------
 

Another script which will analyze all the tables and indexes.

cat Gather_stat.sh

ADMIN="tyagi.ankur07@gmail.com"

sqlplus /nolog <<EOF
connect / as sysdba

set head off
set underline off
set pages 0
set veri off

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_tables where last_analyzed is null;
spool off

@last_analyzed.lst
exit;
EOF

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