ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 每天发送邮件监控oracle的存储过程.把这个sp加入到job里面

每天发送邮件监控oracle的存储过程.把这个sp加入到job里面

原创 Linux操作系统 作者:mugen 时间:2019-03-11 09:57:04 0 删除 编辑

CREATE OR REPLACE PROCEDURE CheckSpace
IS

used number;
free number;
total number;
hit number;
pins number;
reloads number;
lib number;
smem number;
sdisk number;
para varchar2(4000);
pbytes number;
err number;
BEGIN

 select sum(usedmb),sum(freemb),sum(totalmb)
 into used,free,total
 from
 (
  select usedmb,freemb,totalmb
   from ts_used_free
   where to_char(check_date,'yyyymmdd')=to_char(sysdate,'yyyymmdd')
 );
 SELECT 1 - (phy.value / (cur.value + con.value))
 into Hit FROM v$sysstat cur, v$sysstat con, v$sysstat phy
 WHERE cur.name = 'db block gets' AND con.name = 'consistent gets'
 AND phy.name = 'physical reads';

 select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
     sum(reloads)/sum(pins) *100 libcache
     into pins,reloads,lib
     from v$librarycache;

 
 SELECT  value  into smem
  FROM v$sysstat WHERE name IN ('sorts (memory)');
 SELECT  value  into sdisk
  FROM v$sysstat WHERE name IN ('sorts (disk)');


 select bytes  into pbytes
 from v$sgastat where pool='shared pool' and name ='free memory';


 select count(*) into err
  from alert_tab  WHERE text LIKE '%ORA-%' ;


 para := 'Oracle Pirmary Server Used MB '||'  '
 || to_char(used) || ' ' || ' Free MB' ||'  '
 || to_char(free) || '   ' || ' Total MB'||'  '
 || to_char(total)|| '   ' ||' Data buffer cache Hit Ratio Value is '||'  '
 || to_char(hit)||' ' ||' Share Pool cache Hit Ratio should less than 1%,and the value is  '
 ||'  '
 || to_char(lib)||' ' || '  Memory Sort is '||' '||to_char(smem)||'     Disk Sort is'||'  '
 || to_char(sdisk) ||' '|| 'The Disk/Mem ,the less the better'||'  '
 || 'Share Pool Free Memory bytes:'||' '||to_char(pbytes)  ||'  '
 || 'The ORA- error number in alert log is'|| '  '|| to_char(err);
 
 procsendemail(para) ;
  send_email('shenjie@akey.net.cn',para);  
  send_email('shenjie@itownet.cn',para);  
  send_email('shenjie7810@163.com',para);  
  send_email('luowuhong@itownet.cn',para);  
  /* */
 send_email('suhuilin@itownet.cn',para);  
 send_email('yangyongbing@itownet.cn',para); 
 

END;
/



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9408/viewspace-102254/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2002-04-05

  • 博文量
    464
  • 访问量
    354875