How to gather stats for single table using shell script

#usage S_ACT_EMP_stats.ksh  <DBPASSWD>
. /home/oracle/ora_PROD.env

export MAILEE=”xyz@doyensys.com”

$ORACLE_HOME/bin/sqlplus -s $CONNECT_DBA << EOF > /home/oracle/siebel_stats_gathering/S_ACT_EMP.log
set head off
set pagesize 0
set linesize 100
set feed off
set trimspool off
set time on
set timing on

select ‘Started At : ‘||to_char(sysdate,’DD/MON/YYYY HH:MI:SS’) from dual;

exec dbms_stats.gather_table_stats(-
OWNNAME =>’SIEBEL’, –
tabname => ‘S_ACT_EMP’, –
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,-
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’, –
cascade => TRUE, –
degree => 4 –
);

select ‘Ended At   : ‘||to_char(sysdate,’DD/MON/YYYY HH:MI:SS’) from dual;
EOF

mailx  -s “Stats collection for S_ACT_EMP table Completed” $MAILEE < /home/oracle/siebel_stats_gathering/S_ACT_EMP.log

  • October 3, 2015 | 24 views