于监控数据库情况代码,PLSQL代码库,德仔网

代码语言
代码分类
【PLSQL】于监控数据库情况
作者:昕楠 / 发布于2014/11/18/ 655

	-- 在以上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数据库服务器
	 ** 各表空间的使用情况，如果其占用空间超过总分配空间的90%，将邮件报警!
	 ****************************************************************************/
	 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;
试试其它关键字
　监控数据库　
同语言下
可能有用的
昕楠贡献的其它代码(33)