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="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

1 comment:

Sridevi K said...


Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.