磁盘管理之查看磁盘信息(二)
v_disk_info_all := '';
IF v_cnt12 > 0 THEN
FOR i12 IN ( SELECT text as disk_info FROM disk_info@tdw12_lym ORDER BY rowid ) LOOP
v_disk_info := i12.disk_info;
v_disk_info_all := v_disk_info_all||v_disk_info||chr(10);
END LOOP;
END IF;
IF v_cnt13 > 0 THEN
FOR i13 IN ( SELECT text as disk_info FROM disk_info@tdw13_lym ORDER BY rowid ) LOOP
v_disk_info := i13.disk_info;
v_disk_info_all := v_disk_info_all||v_disk_info||chr(10);
END LOOP;
END IF;
IF v_cnt21 > 0 THEN
FOR i21 IN ( SELECT text as disk_info FROM disk_info ORDER BY rowid ) LOOP
v_disk_info := i21.disk_info;
v_disk_info_all := v_disk_info_all||v_disk_info||chr(10);
END LOOP;
END IF;
IF v_disk_info_all IS NULL THEN
v_disk_info_all := 'All Disk''s space are OK!';
END IF;
RETURN v_disk_info_all;
END;
/
-- *3.3. 创建存储过程调用函数，得到磁盘空间使用信息
CREATE OR REPLACE PROCEDURE get_disk_info_proc
AS
/******************************************************************************
** 功能：监控Oracle数据库服务器(10.4.1.12、10.4.1.13、10.4.1.21)
** 各磁盘空间的使用情况，如果其占用空间超过其总空间的80%，将邮件报警!
****************************************************************************/
v_disk_info varchar2(4000);
BEGIN
SELECT DISK_INFO_FUNC() INTO v_disk_info FROM dual;
IF v_disk_info <> 'All Disk''s space are OK!' THEN
PROCSENDEMAIL(v_disk_info,'Disk Space Emergency!!!','ymluo@corp.tudou.com','13691147539@139.com','10.5.101.1','25','0',NULL,NULL,NULL,'bit 7');
END IF;
-- dbms_output.put_line(v_disk_info);
END;
/
-- *3.4. 创建 Job 定时监控
-- job 1.1 -- 每15分钟执行一次
variable job_disk_info_proc number;
begin
dbms_job.submit(:job_disk_info_proc,'get_disk_info_proc;',sysdate,'SYSDATE+1/96');
end;