--把目录授权给用户
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 < sysdate - interval '7' day;
? ?
? ? 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;