DOYENSYS Knowledge Portal




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




Friday, March 22, 2013

To generate an email if more than 10 archives are generated in an Hour

SDATE=`date +"%d%b%y %H:%M"`;
sqlplus -s '/ as sysdba' @/home/oracle/COOL/archcounthr.sql > /home/oracle/COOL/archcounthr.log; cnt=`cat /home/oracle/COOL/archcounthr.log`
if [ $cnt -gt 10 ]
then echo "$cnt Archives generated in QTYDB at $SDATE" | mailx -s "QTYDB:Archives Generating >10" dba@xyz.com
fi
exit;

 SQL File
=======
 archcounthr.sql

set head off;
set feed off;
select to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),to_char(sysdate,'HH'),1,0)),'99') "HH24" from v$log_history where trunc(first_time)=trunc(sysdate) group by to_char(first_time,'YYYY-MM-DD') order by to_char(first_time,'YYYY-MM-DD') desc;
exit;

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.