DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Saturday, October 3, 2015

How to gather stats for single table using shell script

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

export MAILEE=""

$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(-
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;

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

1 comment:

Sridevi K said...

Sridevi Koduru (Senior Oracle Apps Trainer
LinkedIn profile -
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at | +91 - 9581017828.