谁能给我一个oracle数据库表空间监控工具啊？求表空间监控工具...急！急！！急！急！急！ 在线等啊
求一个oracle数据库表空间监控程序或工具，能及时监控到表空间超出等情况，最好能发邮件或短信提醒的，谢谢，公司给安排的工作，今天必须完成，希望大神帮帮我啊！可以发我邮箱 296580677@qq.com
Oracle 数据库 监控工具
[解决办法]
-- 我是用 存储过程 + job循环调度 + 139邮箱实时接收雉通知实现的。
--
[解决办法]
-- 下面是我的实现代码(当然，在写过程前，你得有你们的邮件服务器，且在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;
/
