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

最新推荐文章于 2023-02-28 15:35:14 发布

ctzzj06288

最新推荐文章于 2023-02-28 15:35:14 发布

阅读量196

点赞数

文章标签：数据库

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);