Friday, August 5, 2011

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

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

chmod a+x

then paste:

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);
echo -e "Stat completed for table FOO." | mail -s "Stat completed"

Now you can run the script in background:

nohup ./ &

The script will email you upon completion.

No comments: