create or replace procedure mon_filesystem_pro
as
v_body long;
v_subject varchar2(1000) :='Filesystem Warning';
msg varchar2(1000);
cursor c1 is
SELECT hostname,filesystem,allbytes,usebytes,freebytes,usepct,mounton,mon_datetime FROM MON_FILESYSTEM WHERE replace(usepct,'%','')>70;
v1 c1%rowtype;
v_count number default 0;
begin
if c1%isopen then
close c1;
end if;
open c1;
v_body:=' '||'http://www.163.com'||'& nbsp;'||'帳號和密碼和工號一樣
';
v_body:=v_body||'
| HostName '; v_body:=v_body||' | FileSystem '; v_body:=v_body||' | AllBytes '; v_body:=v_body||' | UseBytes '; v_body:=v_body||' | FreeBytes '; v_body:=v_body||' | UsePCT '; v_body:=v_body||' | MountON '; v_body:=v_body||' | Mon_DateTime '; loop fetch c1 into v1; exit when c1%notfound; v_body:=v_body||' |
|---|---|---|---|---|---|---|---|
| '||v1.hostname||' | ';'||v1.filesystem||' | ';'||v1.allbytes||' | ';'||v1.usebytes||' | ';'||v1.freebytes||' | ';'||v1.usepct||' | ';'||v1.mounton||' | ';'||v1.mon_datetime||' | ';
close c1;
if(v_count>0) then
sendmail(v_body,v_subject);
end if;
exception
when others then
msg:=substr(sqlerrm,1,1000);
insert into proc_err values(to_char(sysdate,'yyyy/mm/dd hh24:mi:ss'),msg);
end;
生成的格式如下:
http://www.163.com& nbsp;帳號和密碼和工號一樣
| HostName | FileSystem | AllBytes | UseBytes | FreeBytes | UsePCT | MountON | Mon_DateTime |
|---|---|---|---|---|---|---|---|
| xxxxx | /dev/sda5 | 62216440 | 50560564 | 8444488 | 86% | /u01 | 2016/03/28 23:53:01 |
| standby | /dev/mapper/VolGroup-lv_home | 211849568 | 152769972 | 48318176 | 76% | /home | 2016/03/28 23:53:01 |
| xxxxx | /dev/sda5 | 62216440 | 50559512 | 8445540 | 86% | /u01 | 2016/03/27 23:51:08 |
| standby | /dev/mapper/VolGroup-lv_home | 211849568 | 152200112 | 48888036 | 76% | /home | 2016/03/27 23:51:08 |
| xxxxx | /dev/sda5 | 62216440 | 50559512 | 8445540 | 86% | /u01 | 2016/03/27 23:53:01 |
| standby | /dev/mapper/VolGroup-lv_home | 211849568 | 152200136 | 48888012 | 76% | /home | 2016/03/27 23:53:01 |
| xxxxx | /dev/sda5 | 62216440 | 50559876 | 8445176 | 86% | /u01 | 2 016/03/28 07:53:01 |
| standby | /dev/mapper/VolGroup-lv_home | 211849568 | 152320352 | 48767796 | 76% | /home | 2016/03/28 07:53:01 |
| xxxxx | /dev/sda5 | 62216440 | 50560188 | 8444864 | 86% | /u01 | 2016/03/28 15:53:01 |
| standby | /dev/mapper/VolGroup-lv_home | 211849568 | 152508940 | 48579208 | 76% | /home | 2016/03/28 15:53:01 |