--创建临时表用来存放系统错误信息create table superflow(cust_id number(10),cust_name varchar2(100),d varchar(50),error_in
--创建临时表用来存放系统错误信息
create table superflow(cust_id number(10),cust_name varchar2(100),d varchar(50),error_info varchar2(500),client_ip varchar2(50),protocol varchar2(100),t number(20),sj date) tablespace db_data;
--drop table superflow
--创建数据写入路径 
create directory db_dir as '/home/oracle';
--把目录授权给用户
grant read,write on directory db_dir to db;
监控系统错误日志 
过程完成任务有
 把抽取出来错误日志插入到superflow
错误日志会写入操作系统/home/oracle/sql.txt文件下面
create or replace procedure pro_superflow as
 pro_name  varchar2(50);
 err_info  varchar2(1000);
 v_sql    varchar2(1000);
 sj        varchar2(20);
 jl        number(10);
  --cursor record_t_sms_customer_errlog is select c.cust_id 客户id,cu.cust_name 客户名称,c.d 状态,c.error_info 错误信息,c.client_ip 客户ip,c.protocol 协议,c.t 记录数 from (select cust_id,case action when '1' then '登入' else '下行' end d,error_info,client_ip,protocol,count(errlog_id) t from t_sms_customer_errlog where create_time > sysdate - interval '1' hour and err_code=8 group by cust_id, action, error_info,client_ip, protocol) c，t_sms_customer cu where c.cust_id=cu.cust_id and t>5;
cursor record_t_sms_customer_errlog is select c.cust_id 客户id,cu.cust_name 客户名称,c.d 状态,c.error_info 错误信息,c.client_ip 客户ip,c.protocol 协议,c.t 记录数 from (select cust_id,case action when '1' then '登入' else '下行' end d,error_info,client_ip,protocol,count(errlog_id) t from t_sms_customer_errlog where err_code=8  group by cust_id, action, error_info,client_ip, protocol) c，t_sms_customer cu where c.cust_id=cu.cust_id;
--all_record t_sms_customer_errlog%rowtype;
  type table_type is record( cust_id_1 number(10),cust_name_1 varchar2(100),d varchar2(20),error_info_1 varchar2(1000),client_ip_1 varchar2(50),protocol_1 varchar2(1000),t_1 number(10));
all_record table_type;
vsfile  utl_file.file_type;
 begin
  select count(1)into jl from superflow;
  if jl >10000 then
delete from superflow where sj    
    end if;
    vsfile :=                   
        utl_file.fopen ('db_dir',  --->使用fopen打开文件，定义了文件路径，文件名，读写方式以及每一行字符的最大长度，缺省为1024
                        'sql.txt',
                        'w');
  for all_record in record_t_sms_customer_errlog
      loop
insert into superflow values(all_record.客户id,all_record.客户名称,all_record.状态,all_record.错误信息,all_record.客户ip,all_record.协议,all_record.记录数,sysdate);
    commit;
      utl_file.put_line (vsfile, all_record.客户id);
      utl_file.put_line (vsfile, all_record.客户名称);
      utl_file.put_line (vsfile, all_record.状态);
      utl_file.put_line (vsfile, all_record.错误信息);
      utl_file.put_line (vsfile, all_record.客户ip);
      --utl_file.put_line (vsfile, all_record.协议)
      utl_file.put_line (vsfile, all_record.记录数);
      utl_file.put_line (vsfile, sysdate);
      -- 把输入内容写入文件
    end loop;
    utl_file.fflush (vsfile);
    utl_file.fclose (vsfile);
    exception
  when others then
    --sg_log_err('manage_partition',sqlerrm);
    pro_name :='manage_partition';
    err_info :=sqlerrm;
    select sysdate into sj from dual;
    v_sql := 'insert into err_log values('||'''pro_superflow'''||','''||err_info||''','''||sj||''')';
    execute immediate v_sql;
    commit;
dbms_output.put_line(sqlcode||sqlerrm);
    end pro_superflow;
把错误日志以邮件形式发出去
echo '监控日志'|mail -s '系统日志' [email protected] /oracle/sql.txt
本文永久更新链接地址：
，