-- 下面是我的实现代码(当然,在写过程前,你得有你们的邮件服务器,且在oracle中配置好ACL)-- 一、监控土豆北京Oracle数据库服务器各表空间的使用情况:-- 在以上4台服务器的data_monitor用户中创建视图: GRANT SELECT ON DBA_FREE_SPACE TO DATA_MONITOR; GRANT SELECT ON DBA_DATA_FILES TO DATA_MONITOR; GRANT CREATE VIEW TO DATA_MONITOR;CREATE OR REPLACE VIEW data_monitor.tablespace_info AS SELECT a.tablespace_name as tablespace_name, to_char(b.total/1024/1024,999999.99) as Total, to_char((b.total-a.free)/1024/1024,'9999990D99') as Used, to_char(a.free/1024/1024,'9999990D99') as Free, to_char(round((total-free)/total,4)*100,'9999990D99')||'%' as Used_Rate FROM (SELECT tablespace_name, sum(bytes) free FROM SYS.DBA_FREE_SPACE GROUP BY tablespace_name) a, (SELECT tablespace_name, sum(bytes) total FROM SYS.DBA_DATA_FILES GROUP BY tablespace_name ) b WHERE a.tablespace_name=b.tablespace_name AND round((total-free)/total,4)*100 > 90 ORDER BY round((total-free)/total,4) DESC;---------------------------------------------------------------------------- -- *1). 创建存储过程用以监控各表空间的使用情况create or replace PROCEDURE pro_get_tbs_info AS /****************************************************************************** ** 功能:监控Oracle数据库服务器 ** 10.103.23.102 ---- oracle(操作系统用户) ** 10.103.23.103 ---- oraadv(操作系统用户) ** 10.106.23.166 ---- oraweb(操作系统用户) ** 10.106.23.167 ---- oraadv(操作系统用户) ** 各表空间的使用情况,如果其占用空间超过总分配空间的90%,将邮件报警! ** 创建者:罗友谋 ** 创建时间:2013.12.21 ****************************************************************************/ v_tablespace_info varchar2(4000); v_tablespace_info_all varchar2(4000); v_title varchar2(400); v_host_ip tb_monitor_info.host_ip%type; v_host_name varchar2(100); v_monitor_type tb_monitor_info.monitor_type%type; v_db_link_name tb_monitor_info.db_link_name%type; v_tb_name tb_monitor_info.tb_name%type; v_monitor_times tb_monitor_log.monitor_times%type; v_error_times tb_monitor_log.error_times%type; v_date_id tb_monitor_log.date_id%type; v_last_date tb_monitor_log.last_date%type; v_last_text tb_monitor_log.last_text%type; v_mail_info VARCHAR2(4000); v_mail_title VARCHAR2(200); v_mail_from varchar2(50); v_mail_to tb_monitor_info.mail_to%type; v_mail_to_dba varchar2(50); v_fail_text varchar2(4000); v_err_code NUMBER; v_err_msg VARCHAR2(200); v_err_info VARCHAR2(400); v_sql1 varchar2(4000); v_sql2 varchar2(4000); v_cnt number(18,0); cursor cur_tablespace_info IS SELECT t1.host_ip, t1.monitor_type, t1.db_link_name, t1.tb_name, t1.mail_to, to_number(to_char(sysdate,'YYYYMMDD')) as date_id, sysdate as last_date, decode(t2.monitor_times,null,1,t2.monitor_times+1) as monitor_times, nvl(t2.error_times,0) as error_times, nvl(last_text,'') as last_text, 'SELECT COUNT(1) AS cnt FROM '||t1.tb_name||'@'||t1.db_link_name as sqls FROM tb_monitor_info t1 left join tb_monitor_log t2 on t1.host_ip=t2.host_ip and t1.monitor_type=t2.monitor_type and t2.date_id=to_number(to_char(sysdate,'YYYYMMDD')) WHERE t1.monitor_type='tablespace_used' AND t1.status=1; rec_tablespace_info cur_tablespace_info%rowtype; BEGIN v_tablespace_info := ''; v_tablespace_info_all := ''; v_fail_text := ''; select data_monitor.fun_mail_from, data_monitor.fun_mail_to_dba into v_mail_from, v_mail_to_dba from dual; OPEN cur_tablespace_info; LOOP FETCH cur_tablespace_info INTO rec_tablespace_info; EXIT WHEN cur_tablespace_info%NOTFOUND; v_host_ip := rec_tablespace_info.host_ip; v_monitor_type := rec_tablespace_info.monitor_type; v_db_link_name := rec_tablespace_info.db_link_name; v_tb_name := rec_tablespace_info.tb_name; v_mail_to := rec_tablespace_info.mail_to; v_date_id := rec_tablespace_info.date_id; v_last_date := rec_tablespace_info.last_date; v_monitor_times := rec_tablespace_info.monitor_times; v_error_times := rec_tablespace_info.error_times; v_last_text := rec_tablespace_info.last_text; v_sql1 := rec_tablespace_info.sqls; BEGIN EXECUTE IMMEDIATE v_sql1 INTO v_cnt; IF v_cnt >0 THEN BEGIN EXECUTE IMMEDIATE 'SELECT listagg(lpad(tablespace_name,30,'' '')||chr(9)||lpad(total,11,'' '')||chr(9)||lpad(used,11,'' '')||chr(9)||lpad(free,11,'' '')||chr(9)||lpad(used_rate,12,'' ''),chr(10)) within group(order by rownum) as tbs_info FROM '||v_tb_name||'@'||v_db_link_name INTO v_tablespace_info; IF v_error_times < 4 THEN SELECT 'Tablespace_Info For '||v_host_ip||' Date: '||to_char(v_last_date,'YYYY-MM-DD HH24:MI:SS')||chr(10)|| lpad(' ',120,'-')||chr(10)|| lpad('Tablespace_Name',30,' ')||chr(9)|| lpad('Total(M)',11,' ')||chr(9)|| lpad('Used(M)',11,' ')||chr(9)|| lpad('Free(M)',11,' ')||chr(9)|| lpad('Used_Rate',12,' ')||chr(10) INTO v_title FROM dual; v_mail_info := v_title||v_tablespace_info; v_tablespace_info_all := v_tablespace_info_all||v_mail_info||chr(10)||chr(10); v_mail_title := '表空间报警!('||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||')'; PRO_SENDEMAIL(v_mail_info,v_mail_title,v_mail_from,v_mail_to); END IF; END; ELSE v_tablespace_info := 'OK'; END IF; merge into tb_monitor_log t1 using (select v_host_ip as host_ip, v_monitor_type as monitor_type, v_date_id as date_id, v_last_date as last_date, v_monitor_times as monitor_times, decode(v_tablespace_info,'OK',0,v_error_times+1) as error_times, v_tablespace_info as last_text from dual) t2 on (t1.host_ip=t2.host_ip and t1.monitor_type=t2.monitor_type and t1.date_id=t2.date_id) when matched then update set t1.last_date=t2.last_date, t1.monitor_times=t2.monitor_times, t1.error_times=t2.error_times, t1.last_text=t2.last_text when not matched then insert (host_ip,monitor_type,date_id,last_date,monitor_times,error_times,last_text) values (t2.host_ip,t2.monitor_type,t2.date_id,t2.last_date,t2.monitor_times,t2.error_times,t2.last_text); COMMIT; EXCEPTION WHEN OTHERS THEN BEGIN NULL; v_err_code := SQLCODE; v_err_msg := SUBSTR(SQLERRM, 1, 200); v_fail_text := 'Error code: '||v_err_code||CHR(10)||'Error message: '||v_err_msg||CHR(10)||'Execute_Host: '||v_host_name; v_fail_text := '主机:'||v_host_ip||' 表空间监控失败! 请检查是否网络故障或其他原因。'||CHR(10)||CHR(10)||v_fail_text; v_mail_title := '表空间监控失败--请速检查!('||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||')'; PRO_SENDEMAIL(v_fail_text,v_mail_title,v_mail_from,v_mail_to_dba||';'||v_mail_to); END; END; END LOOP; CLOSE cur_tablespace_info; IF v_tablespace_info_all IS NOT NULL THEN PRO_SENDEMAIL(v_tablespace_info_all,'表空间报警!',v_mail_from,v_mail_to_dba); END IF;EXCEPTION WHEN OTHERS THEN -- 关闭尚未关闭的游标 IF cur_tablespace_info%ISOPEN THEN close cur_tablespace_info; END IF; v_err_code := SQLCODE; v_err_msg := SUBSTR(SQLERRM, 1, 200); v_mail_title := '存储过程(表空间监控: pro_get_tbs_info)执行出错!('||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||')'; v_host_name := sys_context('userenv','host'); v_fail_text := 'Error code: '||v_err_code||CHR(10)||'Error message: '||v_err_msg||CHR(10)||'Execute_Host: '||v_host_name; PRO_SENDEMAIL(v_fail_text,v_mail_title,v_mail_from,v_mail_to_dba); END; /exec pro_get_tbs_info;---------------------------------------------------------------------------- -- *2). 创建 Job 定时监控 -- job 1.1 -- 每30分钟执行一次-- yktdadvdg2 variable job_get_tbs_info1 number; begin dbms_job.submit(:job_get_tbs_info1,'pro_get_tbs_info;',TRUNC(SYSDATE,'HH24')+5/1440,'TRUNC(SYSDATE,''HH24'')+5/1440+1/24'); end; /
--
GRANT SELECT ON DBA_FREE_SPACE TO DATA_MONITOR;
GRANT SELECT ON DBA_DATA_FILES TO DATA_MONITOR;
GRANT CREATE VIEW TO DATA_MONITOR;CREATE OR REPLACE VIEW data_monitor.tablespace_info
AS
SELECT a.tablespace_name as tablespace_name,
to_char(b.total/1024/1024,999999.99) as Total,
to_char((b.total-a.free)/1024/1024,'9999990D99') as Used,
to_char(a.free/1024/1024,'9999990D99') as Free,
to_char(round((total-free)/total,4)*100,'9999990D99')||'%' as Used_Rate
FROM (SELECT tablespace_name, sum(bytes) free FROM SYS.DBA_FREE_SPACE GROUP BY tablespace_name) a,
(SELECT tablespace_name, sum(bytes) total FROM SYS.DBA_DATA_FILES GROUP BY tablespace_name ) b
WHERE a.tablespace_name=b.tablespace_name
AND round((total-free)/total,4)*100 > 90
ORDER BY round((total-free)/total,4) DESC;----------------------------------------------------------------------------
-- *1). 创建存储过程用以监控各表空间的使用情况create or replace PROCEDURE pro_get_tbs_info
AS
/******************************************************************************
** 功能:监控Oracle数据库服务器
** 10.103.23.102 ---- oracle(操作系统用户)
** 10.103.23.103 ---- oraadv(操作系统用户)
** 10.106.23.166 ---- oraweb(操作系统用户)
** 10.106.23.167 ---- oraadv(操作系统用户)
** 各表空间的使用情况,如果其占用空间超过总分配空间的90%,将邮件报警!
** 创建者:罗友谋
** 创建时间:2013.12.21
****************************************************************************/
v_tablespace_info varchar2(4000);
v_tablespace_info_all varchar2(4000);
v_title varchar2(400); v_host_ip tb_monitor_info.host_ip%type;
v_host_name varchar2(100);
v_monitor_type tb_monitor_info.monitor_type%type;
v_db_link_name tb_monitor_info.db_link_name%type;
v_tb_name tb_monitor_info.tb_name%type; v_monitor_times tb_monitor_log.monitor_times%type;
v_error_times tb_monitor_log.error_times%type;
v_date_id tb_monitor_log.date_id%type;
v_last_date tb_monitor_log.last_date%type;
v_last_text tb_monitor_log.last_text%type; v_mail_info VARCHAR2(4000);
v_mail_title VARCHAR2(200);
v_mail_from varchar2(50);
v_mail_to tb_monitor_info.mail_to%type;
v_mail_to_dba varchar2(50);
v_fail_text varchar2(4000); v_err_code NUMBER;
v_err_msg VARCHAR2(200);
v_err_info VARCHAR2(400); v_sql1 varchar2(4000);
v_sql2 varchar2(4000);
v_cnt number(18,0); cursor cur_tablespace_info IS
SELECT t1.host_ip, t1.monitor_type, t1.db_link_name, t1.tb_name, t1.mail_to,
to_number(to_char(sysdate,'YYYYMMDD')) as date_id, sysdate as last_date,
decode(t2.monitor_times,null,1,t2.monitor_times+1) as monitor_times,
nvl(t2.error_times,0) as error_times,
nvl(last_text,'') as last_text,
'SELECT COUNT(1) AS cnt FROM '||t1.tb_name||'@'||t1.db_link_name as sqls
FROM tb_monitor_info t1 left join tb_monitor_log t2 on t1.host_ip=t2.host_ip and t1.monitor_type=t2.monitor_type and t2.date_id=to_number(to_char(sysdate,'YYYYMMDD'))
WHERE t1.monitor_type='tablespace_used'
AND t1.status=1; rec_tablespace_info cur_tablespace_info%rowtype;
BEGIN v_tablespace_info := '';
v_tablespace_info_all := '';
v_fail_text := ''; select data_monitor.fun_mail_from, data_monitor.fun_mail_to_dba
into v_mail_from, v_mail_to_dba
from dual; OPEN cur_tablespace_info;
LOOP
FETCH cur_tablespace_info INTO rec_tablespace_info;
EXIT WHEN cur_tablespace_info%NOTFOUND; v_host_ip := rec_tablespace_info.host_ip;
v_monitor_type := rec_tablespace_info.monitor_type;
v_db_link_name := rec_tablespace_info.db_link_name;
v_tb_name := rec_tablespace_info.tb_name;
v_mail_to := rec_tablespace_info.mail_to;
v_date_id := rec_tablespace_info.date_id;
v_last_date := rec_tablespace_info.last_date;
v_monitor_times := rec_tablespace_info.monitor_times;
v_error_times := rec_tablespace_info.error_times;
v_last_text := rec_tablespace_info.last_text;
v_sql1 := rec_tablespace_info.sqls; BEGIN
EXECUTE IMMEDIATE v_sql1 INTO v_cnt; IF v_cnt >0 THEN
BEGIN
EXECUTE IMMEDIATE 'SELECT listagg(lpad(tablespace_name,30,'' '')||chr(9)||lpad(total,11,'' '')||chr(9)||lpad(used,11,'' '')||chr(9)||lpad(free,11,'' '')||chr(9)||lpad(used_rate,12,'' ''),chr(10)) within group(order by rownum) as tbs_info FROM '||v_tb_name||'@'||v_db_link_name INTO v_tablespace_info;
IF v_error_times < 4 THEN
SELECT 'Tablespace_Info For '||v_host_ip||' Date: '||to_char(v_last_date,'YYYY-MM-DD HH24:MI:SS')||chr(10)||
lpad(' ',120,'-')||chr(10)||
lpad('Tablespace_Name',30,' ')||chr(9)||
lpad('Total(M)',11,' ')||chr(9)||
lpad('Used(M)',11,' ')||chr(9)||
lpad('Free(M)',11,' ')||chr(9)||
lpad('Used_Rate',12,' ')||chr(10) INTO v_title
FROM dual;
v_mail_info := v_title||v_tablespace_info;
v_tablespace_info_all := v_tablespace_info_all||v_mail_info||chr(10)||chr(10);
v_mail_title := '表空间报警!('||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||')';
PRO_SENDEMAIL(v_mail_info,v_mail_title,v_mail_from,v_mail_to);
END IF;
END;
ELSE
v_tablespace_info := 'OK';
END IF; merge into tb_monitor_log t1
using (select v_host_ip as host_ip, v_monitor_type as monitor_type, v_date_id as date_id, v_last_date as last_date,
v_monitor_times as monitor_times, decode(v_tablespace_info,'OK',0,v_error_times+1) as error_times,
v_tablespace_info as last_text
from dual) t2
on (t1.host_ip=t2.host_ip and t1.monitor_type=t2.monitor_type and t1.date_id=t2.date_id)
when matched then
update set t1.last_date=t2.last_date, t1.monitor_times=t2.monitor_times, t1.error_times=t2.error_times, t1.last_text=t2.last_text
when not matched then
insert (host_ip,monitor_type,date_id,last_date,monitor_times,error_times,last_text)
values (t2.host_ip,t2.monitor_type,t2.date_id,t2.last_date,t2.monitor_times,t2.error_times,t2.last_text); COMMIT; EXCEPTION WHEN OTHERS THEN
BEGIN
NULL;
v_err_code := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 200);
v_fail_text := 'Error code: '||v_err_code||CHR(10)||'Error message: '||v_err_msg||CHR(10)||'Execute_Host: '||v_host_name;
v_fail_text := '主机:'||v_host_ip||' 表空间监控失败! 请检查是否网络故障或其他原因。'||CHR(10)||CHR(10)||v_fail_text;
v_mail_title := '表空间监控失败--请速检查!('||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||')';
PRO_SENDEMAIL(v_fail_text,v_mail_title,v_mail_from,v_mail_to_dba||';'||v_mail_to);
END; END;
END LOOP;
CLOSE cur_tablespace_info; IF v_tablespace_info_all IS NOT NULL THEN
PRO_SENDEMAIL(v_tablespace_info_all,'表空间报警!',v_mail_from,v_mail_to_dba);
END IF;EXCEPTION
WHEN OTHERS THEN
-- 关闭尚未关闭的游标
IF cur_tablespace_info%ISOPEN THEN
close cur_tablespace_info;
END IF;
v_err_code := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 200);
v_mail_title := '存储过程(表空间监控: pro_get_tbs_info)执行出错!('||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||')';
v_host_name := sys_context('userenv','host');
v_fail_text := 'Error code: '||v_err_code||CHR(10)||'Error message: '||v_err_msg||CHR(10)||'Execute_Host: '||v_host_name;
PRO_SENDEMAIL(v_fail_text,v_mail_title,v_mail_from,v_mail_to_dba);
END;
/exec pro_get_tbs_info;----------------------------------------------------------------------------
-- *2). 创建 Job 定时监控
-- job 1.1 -- 每30分钟执行一次-- yktdadvdg2
variable job_get_tbs_info1 number;
begin
dbms_job.submit(:job_get_tbs_info1,'pro_get_tbs_info;',TRUNC(SYSDATE,'HH24')+5/1440,'TRUNC(SYSDATE,''HH24'')+5/1440+1/24');
end;
/
1个过程 发送邮箱
job 定时调度