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"

 SQL File

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;

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.