郵件告警中心
-- Create table
告警中心功能有
1.傳送時間
2.郵件傳送次數
3.批次傳送郵件
4.可以檢視已經傳送次數
這個參數列
create table WARNING_PARAMETER
(
WARNING_ID NUMBER not null, --告警id
WARNING_SQL VARCHAR2(4000), --獲取告警SQL
NOTE VARCHAR2(2000), --告警標題
EXE_NUMBER NUMBER(8), --可以執行次數，如果設定5只執行5次以後每天只能傳送5條郵件
CURRENT_EXEC NUMBER(10) default 0, --當天傳送郵件次數，自動獲取預設0不要手動插入
EXE_STARTIME NUMBER(8), --開始發郵件時間00-24點
EXE_ENDTIME NUMBER(8) default 24, --結束髮送郵件時間00-24點預設24點
WARNING_LEVEL NUMBER, --告警級別數字 例如：1、2、3、4等......
LAST_TIME DATE default sysdate, --最後修改時間
WARNING_TITLE VARCHAR2(1000), --告警級別 例如：高階告警、低階告警、中級高階
SEND_ADDR VARCHAR2(500) --接收人郵箱地址如果多個人有半形逗號隔開 例如：26314@qq.com,13525@139.com
)
tablespace SMSDB_DATA;
-- Create/Recreate primary, unique and foreign key constraints
alter table WARNING_PARAMETER
add constraint PK_WARNING_SQL primary key (WARNING_ID)
using index
tablespace SMSDB_DATA;
模板資料
insert into warning_parameter (WARNING_ID, WARNING_SQL, NOTE, EXE_NUMBER, CURRENT_EXEC, EXE_STARTIME, EXE_ENDTIME, WARNING_LEVEL, LAST_TIME, WARNING_TITLE, SEND_ADDR)
values (1, 'select ''表空間:''||a.tablespace_name||'' 總空間(GB):''||round(a.bytes/1024/1024/1024,2)||'' 使用空間(GB):''||round((a.bytes-
b.bytes)/1024/1024/1024,2)
||'' 空閒空間(GB):''||round(b.bytes/1024/1024/1024,2)||'' 使用空間佔比(%):''||round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
and round(((a.bytes-b.bytes)/a.bytes)*100,2)>60
order by ((a.bytes-b.bytes)/a.bytes) desc', '通道告警', 0, 0, 8, 24,3, to_date('28-12-2015', 'dd-mm-yyyy'), '低階告警', '15960752378@139.com');
收集告警資訊表
-- Create table
create table WARNING_INFO
(
ID NUMBER not null, --告警id呼叫序列
WARNING_ID NUMBER, --告警id跟WARNING_PARAMETER id進行關聯查詢資料
WARNING_CONTENT LONG, --告警內容
INSERT_TIME DATE default sysdate, --獲取告警時間
STATUS CHAR(1) default 0, --0未傳送 1已經傳送
SEND_TIME DATE -- 傳送郵件時間
)
tablespace SMSDB_DATA;
-- Create/Recreate primary, unique and foreign key constraints
alter table WARNING_INFO
add constraint PK_WARNING_INFO primary key (ID)
using index
tablespace SMSDB_DATA;
資訊表ID序列
create sequence info_id
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocycle
cache 20;
select * from warning_parameter;
select * from warning_info;
收集告警資訊過程
create or replace procedure warning_parameter_1 as
type t_row is record(r_sql long);
v_all_row warning_parameter%rowtype;
type a_row is table of t_row index by binary_integer;
v_sqltxt a_row;
v_content long;
v_sql varchar(5000);
dd varchar(10);
cursor c_all_row is select * from warning_parameter;
time_now number;
begin
select cast(to_char(sysdate,'hh24') as number(8)) into time_now from dual;
for v_all_row in c_all_row
loop
--if v_all_row.exe_number > 0 then
--if time_now >= v_all_row.exe_startime or time_now <= v_all_row.exe_endtime then
if time_now >= v_all_row.exe_startime then
v_sql := v_all_row.warning_sql;
execute immediate v_sql bulk collect into v_sqltxt;
for m in 1..v_sqltxt.count
loop
v_content := v_content||chr(10)||v_sqltxt(m).r_sql;
--v_content := v_content||chr(10)||v_sqltxt(m);
end loop;
if length(v_content) > 0 then
insert into warning_info (ID,WARNING_ID,WARNING_CONTENT,INSERT_TIME,STATUS)values(info_id.nextval,v_all_row.warning_id,v_content,sysdate,'0');
commit;
end if;
--end if;
elsif time_now <= v_all_row.exe_startime then
v_sql := 'update warning_parameter set CURRENT_EXEC=0 where WARNING_ID='||v_all_row.warning_id;
execute immediate v_sql;
commit;
--dd :=100;
end if;
v_content :='';
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm);
sg_log_err('warning_parameter', sqlerrm);
end warning_parameter_1;
郵件傳送中心
create or replace procedure warning_center as
type t_row is record(id number(9),content long,status char(1),note varchar(200),exe number(10),last_time date,send_addr varchar(1000),a_id number(10),cur_num number(10));
v_sqltxt t_row;
--type a_row is table of t_row index by binary_integer;
--v_sqltxt a_row;
addr varchar(1000);
addr_2 varchar(1000);
all_addr varchar(1000);
exec_l number(10);
v_sql varchar(5000);
exec_num number(9);
dd varchar(10);
cursor c_all_row is select a.current_exec,a.exe_number,a.last_time,a.send_addr,b.warning_id,b.warning_content,b.status,a.note,b.id from warning_info b inner join warning_parameter a on a.warning_id=b.warning_id where b.status=0;
--cursor c_all_addr is select send_addr from warning_parameter;
--v_t_addr warning_parameter.send_addr%type;
time_now number;
begin
for v_sqltxt in c_all_row
loop
all_addr := length(v_sqltxt.send_addr);
addr_2 :=v_sqltxt.send_addr;
if trunc(v_sqltxt.last_time,'dd')>=trunc(sysdate,'dd') or trunc(v_sqltxt.last_time,'dd')<=trunc(sysdate,'dd') then
if v_sqltxt.exe_number>v_sqltxt.current_exec or v_sqltxt.exe_number=0 then
while length(addr_2) > 0
loop
select current_exec into exec_l from warning_parameter m where warning_id=v_sqltxt.warning_id;
if v_sqltxt.exe_number>exec_l or v_sqltxt.exe_number=0 then
if instr(addr_2,',',1) >0 then
addr := substr(addr_2,1,instr(addr_2,',',1)-1);
addr_2 := substr(addr_2,instr(addr_2,',',1)+1,all_addr);
else
addr := addr_2;
---addr_2 :='';
warning_sendmail(recipient1 => addr,subject => v_sqltxt.note,m_count => v_sqltxt.warning_content);
-- v_sql := 'update warning_info set status=1 where id='||v_sqltxt.id;
v_sql := 'update warning_info set status=1,SEND_TIME=SYSDATE where id='||v_sqltxt.id;
execute immediate v_sql;
commit;
v_sql :='update warning_parameter a set a.current_exec=a.current_exec+1,a.last_time=sysdate where a.warning_id='||v_sqltxt.warning_id;
execute immediate v_sql;
commit;
exit;
--while
end if;
warning_sendmail(recipient1 => addr,subject => v_sqltxt.note,m_count => v_sqltxt.warning_content);
-- v_sql := 'update warning_info set status=1 where id='||v_sqltxt.id;
v_sql := 'update warning_info set status=1,SEND_TIME=SYSDATE where id='||v_sqltxt.id;
execute immediate v_sql;
commit;
v_sql :='update warning_parameter a set a.current_exec=a.current_exec+1,a.last_time=sysdate where a.warning_id='||v_sqltxt.warning_id;
execute immediate v_sql;
commit;
else
exit;
end if;
end loop;
else
v_sql := 'update warning_info set status=1 where id='||v_sqltxt.id;
execute immediate v_sql;
commit;
end if;
end if;
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm);
end warning_center;
郵件傳送過程
CREATE OR REPLACE PROCEDURE warning_sendmail(sender IN VARCHAR2 DEFAULT 'mxq@139.cn', --郵件使用者
recipient1 IN VARCHAR2 DEFAULT '15960752378@139.com', --接收人
/*recipient2 IN VARCHAR2 DEFAULT '2880089709@qq.com',
recipient3 IN VARCHAR2 DEFAULT '2880329180@qq.com',
recipient4 IN VARCHAR2 DEFAULT '2880329185@qq.com',
recipient5 IN VARCHAR2 DEFAULT '2880089704@qq.com',*/
subject IN VARCHAR2 DEFAULT '超流和餘額監控83', --標題
m_count IN VARCHAR2 DEFAULT '名次'
) IS
/*sender varchar2(30) := '13515028432@139.com';
subject varchar2(30) := '超流監控83';
recipient1 varchar2(30) := '282263179@qq.com';
recipient2 varchar2(30) := '282263179@qq.com';
recipient3 varchar2(30) := '282263179@qq.com';
recipient4 varchar2(30) := '282263179@qq.com';*/
mailhost VARCHAR2(30) := 'smtp.qiye.163.com'; --郵件伺服器地址
--receiver VARCHAR2(30) :='監控管理員';
mail_passwd varchar(20) :='123456'; --郵件密碼
connter_addr utl_smtp.connection;
msg_content long;
BEGIN
--insert into superflow values(all_record.客戶id,all_record.客戶名稱,all_record.狀態,all_record.錯誤資訊,all_record.客戶ip,all_record.協議,all_record.記錄數,sysdate);
-- commit;
/* msg_content := 'Date: ' || to_char(SYSDATE , 'yyyy-mm-dd hh24:mi:ss') ||
utl_tcp.crlf || 'From: ' || utl_tcp.crlf ||
'subject: ' || subject || utl_tcp.crlf || 'To: '>;;;' || utl_tcp.crlf || 'Cc: recipient5 || '>' || utl_tcp.crlf || '' || utl_tcp.crlf ||m_count;*/
msg_content := 'Date: ' || to_char(SYSDATE , 'yyyy-mm-dd hh24:mi:ss') ||
utl_tcp.crlf || 'From: '|| utl_tcp.crlf ||
'subject: ' || subject || utl_tcp.crlf || 'To: ' ||utl_tcp.crlf || '' || utl_tcp.crlf ||m_count;
--dbms_output.put_line(all_record.客戶id);
connter_addr := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(connter_addr, mailhost);
utl_smtp.command(connter_addr, 'auth login');
utl_smtp.command(connter_addr,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(sender))));
utl_smtp.command(connter_addr,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(mail_passwd))));
utl_smtp.mail(connter_addr, sender);
utl_smtp.rcpt(connter_addr, recipient1);
/*utl_smtp.rcpt(connter_addr, recipient2);
utl_smtp.rcpt(connter_addr, recipient3);
utl_smtp.rcpt(connter_addr, recipient4);
utl_smtp.rcpt(connter_addr, recipient5);*/
utl_smtp.open_data(connter_addr);
utl_smtp.write_raw_data(connter_addr, utl_raw.cast_to_raw(msg_content));
utl_smtp.close_data(connter_addr);
utl_smtp.quit(connter_addr);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm);
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);
END warning_sendmail;
在建立兩個job自動執行過程
這個設定排程時間waring_parameter要比warning_center快，因為waring_paramete過程獲取資訊warning_center通知傳送郵件過程傳送郵件
var job varchar2(10)
begin
sys.dbms_job.submit(job => :job,
what => 'warning_center;',
next_date => to_date('31-12-2015 16:05:49', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate + interval ''20'' minute');
commit;
end;
/
var job varchar2(10)
begin
sys.dbms_job.submit(job => :job,
what => 'warning_parameter_1;',
next_date => to_date('31-12-2015 15:54:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate + interval ''5'' minute');
commit;
end;
/
CREATE OR REPLACE PROCEDURE warning_sendmail(sender IN VARCHAR2 DEFAULT 'py@py86.cn',
recipient1 IN VARCHAR2 DEFAULT '15960752378@139.com',
/*recipient2 IN VARCHAR2 DEFAULT '2880089709@qq.com',
recipient3 IN VARCHAR2 DEFAULT '2880329180@qq.com',
recipient4 IN VARCHAR2 DEFAULT '2880329185@qq.com',
recipient5 IN VARCHAR2 DEFAULT '2880089704@qq.com',*/
subject IN VARCHAR2 DEFAULT '超流和餘額監控83',
m_count IN VARCHAR2 DEFAULT '名次'
) IS
/*sender varchar2(30) := '13515028432@139.com';
subject varchar2(30) := '超流監控83';
recipient1 varchar2(30) := '282263179@qq.com';
recipient2 varchar2(30) := '282263179@qq.com';
recipient3 varchar2(30) := '282263179@qq.com';
recipient4 varchar2(30) := '282263179@qq.com';*/
mailhost VARCHAR2(30) := 'smtp.qiye.163.com';
--receiver VARCHAR2(30) :='監控管理員';
mail_passwd varchar(20) :='pyserver@67888';
connter_addr utl_smtp.connection;
msg_content long;
BEGIN
--insert into superflow values(all_record.客戶id,all_record.客戶名稱,all_record.狀態,all_record.錯誤資訊,all_record.客戶ip,all_record.協議,all_record.記錄數,sysdate);
-- commit;
/* msg_content := 'Date: ' || to_char(SYSDATE , 'yyyy-mm-dd hh24:mi:ss') ||
utl_tcp.crlf || 'From: ' || utl_tcp.crlf ||
'subject: ' || subject || utl_tcp.crlf || 'To: '>;;;' || utl_tcp.crlf || 'Cc: recipient5 || '>' || utl_tcp.crlf || '' || utl_tcp.crlf ||m_count;*/
msg_content := 'Date: ' || to_char(SYSDATE , 'yyyy-mm-dd hh24:mi:ss') ||
utl_tcp.crlf || 'From: '|| utl_tcp.crlf ||
'subject: ' || subject || utl_tcp.crlf || 'To: ' ||utl_tcp.crlf || '' || utl_tcp.crlf ||m_count;
--dbms_output.put_line(all_record.客戶id);
connter_addr := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(connter_addr, mailhost);
utl_smtp.command(connter_addr, 'auth login');
utl_smtp.command(connter_addr,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(sender))));
utl_smtp.command(connter_addr,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(mail_passwd))));
utl_smtp.mail(connter_addr, sender);
utl_smtp.rcpt(connter_addr, recipient1);
/*utl_smtp.rcpt(connter_addr, recipient2);
utl_smtp.rcpt(connter_addr, recipient3);
utl_smtp.rcpt(connter_addr, recipient4);
utl_smtp.rcpt(connter_addr, recipient5);*/
utl_smtp.open_data(connter_addr);
utl_smtp.write_raw_data(connter_addr, utl_raw.cast_to_raw(msg_content));
utl_smtp.close_data(connter_addr);
utl_smtp.quit(connter_addr);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm);
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);
END warning_sendmail;
告警中心功能有
1.傳送時間
2.郵件傳送次數
3.批次傳送郵件
4.可以檢視已經傳送次數
這個參數列
create table WARNING_PARAMETER
(
WARNING_ID NUMBER not null, --告警id
WARNING_SQL VARCHAR2(4000), --獲取告警SQL
NOTE VARCHAR2(2000), --告警標題
EXE_NUMBER NUMBER(8), --可以執行次數，如果設定5只執行5次以後每天只能傳送5條郵件
CURRENT_EXEC NUMBER(10) default 0, --當天傳送郵件次數，自動獲取預設0不要手動插入
EXE_STARTIME NUMBER(8), --開始發郵件時間00-24點
EXE_ENDTIME NUMBER(8) default 24, --結束髮送郵件時間00-24點預設24點
WARNING_LEVEL NUMBER, --告警級別數字 例如：1、2、3、4等......
LAST_TIME DATE default sysdate, --最後修改時間
WARNING_TITLE VARCHAR2(1000), --告警級別 例如：高階告警、低階告警、中級高階
SEND_ADDR VARCHAR2(500) --接收人郵箱地址如果多個人有半形逗號隔開 例如：26314@qq.com,13525@139.com
)
tablespace SMSDB_DATA;
-- Create/Recreate primary, unique and foreign key constraints
alter table WARNING_PARAMETER
add constraint PK_WARNING_SQL primary key (WARNING_ID)
using index
tablespace SMSDB_DATA;
模板資料
insert into warning_parameter (WARNING_ID, WARNING_SQL, NOTE, EXE_NUMBER, CURRENT_EXEC, EXE_STARTIME, EXE_ENDTIME, WARNING_LEVEL, LAST_TIME, WARNING_TITLE, SEND_ADDR)
values (1, 'select ''表空間:''||a.tablespace_name||'' 總空間(GB):''||round(a.bytes/1024/1024/1024,2)||'' 使用空間(GB):''||round((a.bytes-
b.bytes)/1024/1024/1024,2)
||'' 空閒空間(GB):''||round(b.bytes/1024/1024/1024,2)||'' 使用空間佔比(%):''||round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
and round(((a.bytes-b.bytes)/a.bytes)*100,2)>60
order by ((a.bytes-b.bytes)/a.bytes) desc', '通道告警', 0, 0, 8, 24,3, to_date('28-12-2015', 'dd-mm-yyyy'), '低階告警', '15960752378@139.com');
收集告警資訊表
-- Create table
create table WARNING_INFO
(
ID NUMBER not null, --告警id呼叫序列
WARNING_ID NUMBER, --告警id跟WARNING_PARAMETER id進行關聯查詢資料
WARNING_CONTENT LONG, --告警內容
INSERT_TIME DATE default sysdate, --獲取告警時間
STATUS CHAR(1) default 0, --0未傳送 1已經傳送
SEND_TIME DATE -- 傳送郵件時間
)
tablespace SMSDB_DATA;
-- Create/Recreate primary, unique and foreign key constraints
alter table WARNING_INFO
add constraint PK_WARNING_INFO primary key (ID)
using index
tablespace SMSDB_DATA;
資訊表ID序列
create sequence info_id
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocycle
cache 20;
select * from warning_parameter;
select * from warning_info;
收集告警資訊過程
create or replace procedure warning_parameter_1 as
type t_row is record(r_sql long);
v_all_row warning_parameter%rowtype;
type a_row is table of t_row index by binary_integer;
v_sqltxt a_row;
v_content long;
v_sql varchar(5000);
dd varchar(10);
cursor c_all_row is select * from warning_parameter;
time_now number;
begin
select cast(to_char(sysdate,'hh24') as number(8)) into time_now from dual;
for v_all_row in c_all_row
loop
--if v_all_row.exe_number > 0 then
--if time_now >= v_all_row.exe_startime or time_now <= v_all_row.exe_endtime then
if time_now >= v_all_row.exe_startime then
v_sql := v_all_row.warning_sql;
execute immediate v_sql bulk collect into v_sqltxt;
for m in 1..v_sqltxt.count
loop
v_content := v_content||chr(10)||v_sqltxt(m).r_sql;
--v_content := v_content||chr(10)||v_sqltxt(m);
end loop;
if length(v_content) > 0 then
insert into warning_info (ID,WARNING_ID,WARNING_CONTENT,INSERT_TIME,STATUS)values(info_id.nextval,v_all_row.warning_id,v_content,sysdate,'0');
commit;
end if;
--end if;
elsif time_now <= v_all_row.exe_startime then
v_sql := 'update warning_parameter set CURRENT_EXEC=0 where WARNING_ID='||v_all_row.warning_id;
execute immediate v_sql;
commit;
--dd :=100;
end if;
v_content :='';
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm);
sg_log_err('warning_parameter', sqlerrm);
end warning_parameter_1;
郵件傳送中心
create or replace procedure warning_center as
type t_row is record(id number(9),content long,status char(1),note varchar(200),exe number(10),last_time date,send_addr varchar(1000),a_id number(10),cur_num number(10));
v_sqltxt t_row;
--type a_row is table of t_row index by binary_integer;
--v_sqltxt a_row;
addr varchar(1000);
addr_2 varchar(1000);
all_addr varchar(1000);
exec_l number(10);
v_sql varchar(5000);
exec_num number(9);
dd varchar(10);
cursor c_all_row is select a.current_exec,a.exe_number,a.last_time,a.send_addr,b.warning_id,b.warning_content,b.status,a.note,b.id from warning_info b inner join warning_parameter a on a.warning_id=b.warning_id where b.status=0;
--cursor c_all_addr is select send_addr from warning_parameter;
--v_t_addr warning_parameter.send_addr%type;
time_now number;
begin
for v_sqltxt in c_all_row
loop
all_addr := length(v_sqltxt.send_addr);
addr_2 :=v_sqltxt.send_addr;
if trunc(v_sqltxt.last_time,'dd')>=trunc(sysdate,'dd') or trunc(v_sqltxt.last_time,'dd')<=trunc(sysdate,'dd') then
if v_sqltxt.exe_number>v_sqltxt.current_exec or v_sqltxt.exe_number=0 then
while length(addr_2) > 0
loop
select current_exec into exec_l from warning_parameter m where warning_id=v_sqltxt.warning_id;
if v_sqltxt.exe_number>exec_l or v_sqltxt.exe_number=0 then
if instr(addr_2,',',1) >0 then
addr := substr(addr_2,1,instr(addr_2,',',1)-1);
addr_2 := substr(addr_2,instr(addr_2,',',1)+1,all_addr);
else
addr := addr_2;
---addr_2 :='';
warning_sendmail(recipient1 => addr,subject => v_sqltxt.note,m_count => v_sqltxt.warning_content);
-- v_sql := 'update warning_info set status=1 where id='||v_sqltxt.id;
v_sql := 'update warning_info set status=1,SEND_TIME=SYSDATE where id='||v_sqltxt.id;
execute immediate v_sql;
commit;
v_sql :='update warning_parameter a set a.current_exec=a.current_exec+1,a.last_time=sysdate where a.warning_id='||v_sqltxt.warning_id;
execute immediate v_sql;
commit;
exit;
--while
end if;
warning_sendmail(recipient1 => addr,subject => v_sqltxt.note,m_count => v_sqltxt.warning_content);
-- v_sql := 'update warning_info set status=1 where id='||v_sqltxt.id;
v_sql := 'update warning_info set status=1,SEND_TIME=SYSDATE where id='||v_sqltxt.id;
execute immediate v_sql;
commit;
v_sql :='update warning_parameter a set a.current_exec=a.current_exec+1,a.last_time=sysdate where a.warning_id='||v_sqltxt.warning_id;
execute immediate v_sql;
commit;
else
exit;
end if;
end loop;
else
v_sql := 'update warning_info set status=1 where id='||v_sqltxt.id;
execute immediate v_sql;
commit;
end if;
end if;
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm);
end warning_center;
郵件傳送過程
CREATE OR REPLACE PROCEDURE warning_sendmail(sender IN VARCHAR2 DEFAULT 'mxq@139.cn', --郵件使用者
recipient1 IN VARCHAR2 DEFAULT '15960752378@139.com', --接收人
/*recipient2 IN VARCHAR2 DEFAULT '2880089709@qq.com',
recipient3 IN VARCHAR2 DEFAULT '2880329180@qq.com',
recipient4 IN VARCHAR2 DEFAULT '2880329185@qq.com',
recipient5 IN VARCHAR2 DEFAULT '2880089704@qq.com',*/
subject IN VARCHAR2 DEFAULT '超流和餘額監控83', --標題
m_count IN VARCHAR2 DEFAULT '名次'
) IS
/*sender varchar2(30) := '13515028432@139.com';
subject varchar2(30) := '超流監控83';
recipient1 varchar2(30) := '282263179@qq.com';
recipient2 varchar2(30) := '282263179@qq.com';
recipient3 varchar2(30) := '282263179@qq.com';
recipient4 varchar2(30) := '282263179@qq.com';*/
mailhost VARCHAR2(30) := 'smtp.qiye.163.com'; --郵件伺服器地址
--receiver VARCHAR2(30) :='監控管理員';
mail_passwd varchar(20) :='123456'; --郵件密碼
connter_addr utl_smtp.connection;
msg_content long;
BEGIN
--insert into superflow values(all_record.客戶id,all_record.客戶名稱,all_record.狀態,all_record.錯誤資訊,all_record.客戶ip,all_record.協議,all_record.記錄數,sysdate);
-- commit;
/* msg_content := 'Date: ' || to_char(SYSDATE , 'yyyy-mm-dd hh24:mi:ss') ||
utl_tcp.crlf || 'From: ' || utl_tcp.crlf ||
'subject: ' || subject || utl_tcp.crlf || 'To: '>;;;' || utl_tcp.crlf || 'Cc: recipient5 || '>' || utl_tcp.crlf || '' || utl_tcp.crlf ||m_count;*/
msg_content := 'Date: ' || to_char(SYSDATE , 'yyyy-mm-dd hh24:mi:ss') ||
utl_tcp.crlf || 'From: '|| utl_tcp.crlf ||
'subject: ' || subject || utl_tcp.crlf || 'To: ' ||utl_tcp.crlf || '' || utl_tcp.crlf ||m_count;
--dbms_output.put_line(all_record.客戶id);
connter_addr := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(connter_addr, mailhost);
utl_smtp.command(connter_addr, 'auth login');
utl_smtp.command(connter_addr,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(sender))));
utl_smtp.command(connter_addr,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(mail_passwd))));
utl_smtp.mail(connter_addr, sender);
utl_smtp.rcpt(connter_addr, recipient1);
/*utl_smtp.rcpt(connter_addr, recipient2);
utl_smtp.rcpt(connter_addr, recipient3);
utl_smtp.rcpt(connter_addr, recipient4);
utl_smtp.rcpt(connter_addr, recipient5);*/
utl_smtp.open_data(connter_addr);
utl_smtp.write_raw_data(connter_addr, utl_raw.cast_to_raw(msg_content));
utl_smtp.close_data(connter_addr);
utl_smtp.quit(connter_addr);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm);
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);
END warning_sendmail;
在建立兩個job自動執行過程
這個設定排程時間waring_parameter要比warning_center快，因為waring_paramete過程獲取資訊warning_center通知傳送郵件過程傳送郵件
var job varchar2(10)
begin
sys.dbms_job.submit(job => :job,
what => 'warning_center;',
next_date => to_date('31-12-2015 16:05:49', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate + interval ''20'' minute');
commit;
end;
/
var job varchar2(10)
begin
sys.dbms_job.submit(job => :job,
what => 'warning_parameter_1;',
next_date => to_date('31-12-2015 15:54:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate + interval ''5'' minute');
commit;
end;
/
CREATE OR REPLACE PROCEDURE warning_sendmail(sender IN VARCHAR2 DEFAULT 'py@py86.cn',
recipient1 IN VARCHAR2 DEFAULT '15960752378@139.com',
/*recipient2 IN VARCHAR2 DEFAULT '2880089709@qq.com',
recipient3 IN VARCHAR2 DEFAULT '2880329180@qq.com',
recipient4 IN VARCHAR2 DEFAULT '2880329185@qq.com',
recipient5 IN VARCHAR2 DEFAULT '2880089704@qq.com',*/
subject IN VARCHAR2 DEFAULT '超流和餘額監控83',
m_count IN VARCHAR2 DEFAULT '名次'
) IS
/*sender varchar2(30) := '13515028432@139.com';
subject varchar2(30) := '超流監控83';
recipient1 varchar2(30) := '282263179@qq.com';
recipient2 varchar2(30) := '282263179@qq.com';
recipient3 varchar2(30) := '282263179@qq.com';
recipient4 varchar2(30) := '282263179@qq.com';*/
mailhost VARCHAR2(30) := 'smtp.qiye.163.com';
--receiver VARCHAR2(30) :='監控管理員';
mail_passwd varchar(20) :='pyserver@67888';
connter_addr utl_smtp.connection;
msg_content long;
BEGIN
--insert into superflow values(all_record.客戶id,all_record.客戶名稱,all_record.狀態,all_record.錯誤資訊,all_record.客戶ip,all_record.協議,all_record.記錄數,sysdate);
-- commit;
/* msg_content := 'Date: ' || to_char(SYSDATE , 'yyyy-mm-dd hh24:mi:ss') ||
utl_tcp.crlf || 'From: ' || utl_tcp.crlf ||
'subject: ' || subject || utl_tcp.crlf || 'To: '>;;;' || utl_tcp.crlf || 'Cc: recipient5 || '>' || utl_tcp.crlf || '' || utl_tcp.crlf ||m_count;*/
msg_content := 'Date: ' || to_char(SYSDATE , 'yyyy-mm-dd hh24:mi:ss') ||
utl_tcp.crlf || 'From: '|| utl_tcp.crlf ||
'subject: ' || subject || utl_tcp.crlf || 'To: ' ||utl_tcp.crlf || '' || utl_tcp.crlf ||m_count;
--dbms_output.put_line(all_record.客戶id);
connter_addr := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(connter_addr, mailhost);
utl_smtp.command(connter_addr, 'auth login');
utl_smtp.command(connter_addr,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(sender))));
utl_smtp.command(connter_addr,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(mail_passwd))));
utl_smtp.mail(connter_addr, sender);
utl_smtp.rcpt(connter_addr, recipient1);
/*utl_smtp.rcpt(connter_addr, recipient2);
utl_smtp.rcpt(connter_addr, recipient3);
utl_smtp.rcpt(connter_addr, recipient4);
utl_smtp.rcpt(connter_addr, recipient5);*/
utl_smtp.open_data(connter_addr);
utl_smtp.write_raw_data(connter_addr, utl_raw.cast_to_raw(msg_content));
utl_smtp.close_data(connter_addr);
utl_smtp.quit(connter_addr);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm);
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);
END warning_sendmail;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29065182/viewspace-1814658/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Alertmanager傳送告警郵件配置
- shell -5 告警系統郵件
- 告警系統郵件引擎 執行告警系統
- 【Grafana】告警配置併傳送郵件Grafana
- EM配置Oracle郵件自動告警Oracle
- 郵件告警還能這麼玩？！
- Prometheus alertmanager郵件傳送+grafana告警展示PrometheusGrafana
- Oracle的OEM 郵件告警通知設定Oracle
- 如何實現告警的郵件通知方式？
- docker部署Prometheus+AlertManager實現郵件告警DockerPrometheus
- 郵件營銷用純文字郵件還是html郵件HTML
- 郵件開發：接收解析郵件
- python實現自動監控網站併傳送郵件告警薦Python網站
- 蘋果郵件蘋果
- 使用python傳送郵件和接收郵件Python
- postfix郵件系統之郵件客戶端無法收郵件問題解析客戶端
- shell埠監聽異常郵箱告警
- 使用zabbix實現郵箱/釘釘告警
- Java實現QQ郵件傳送郵件工具類Java
- 郵件開發：複雜郵件的一個示例
- 郵件傳送
- Laravel 郵件配置Laravel
- 群發郵件
- 傳送郵件
- Java郵件(JavaMail)JavaAI
- shell發郵件
- sql 郵件配置SQL
- oracle 發郵件Oracle
- 電子郵件協議及GO傳送QQ郵件協議Go
- 亞馬遜郵件營銷，如何做好郵件模板和回覆？亞馬遜
- golang傳送郵件（qq郵箱）Golang
- 告警運維中心｜構建高效精準的告警協同處理體系運維
- win10郵件怎麼用qq郵箱 win10郵件如何使用qq郵箱Win10
- 一次性解決python smtp 傳送outlook郵件,163郵件,qq郵件等等.Python
- 全網伺服器資料備份方案（模擬生產環境容災同步）+郵件告警伺服器
- SpringBoot整合Mail傳送郵件&傳送模板郵件Spring BootAI
- SQLServer郵件預警SQLServer
- EDM郵件製作