1) Create a external table for the alert log content.
How to create external table for alert log.
2) Create a view from this external table like...
---------------------------------------------------------
CREATE OR REPLACE FORCE VIEW DBA_MAIN.VW_ALERT_LOG
(
NO_OF_LINE,
TEXT
)
AS
SELECT NO_of_line, text
FROM ( SELECT ROWNUM NO_of_line, text
FROM dba_main.alert_log_external
ORDER BY ROWNUM DESC)
WHERE UPPER (text) LIKE UPPER ('%ERROR%')
/
-----------------------------------------------------------
3) Create a table from this above view like....
-------------------------------------------------------------
CREATE TABLE DBA_MAIN.TBL_ALERT_LOG_ERROR
AS
SELECT No_of_line, text, 'Y' send_flg FROM DBA_MAIN.VW_ALERT_LOG;
---------------------------------------------------------------
3) Create a email address table and insert some email addresses like below...
------------------------------------------------------------
CREATE TABLE DBA_MAIN.EMAIL_ADDRESS
(
ID NUMBER,
NAME VARCHAR2(50 BYTE),
EMAIL VARCHAR2(100 BYTE),
ACT_FLAG VARCHAR2(1 BYTE) DEFAULT 'Y'
) ;
Insert into DBA_MAIN.EMAIL_ADDRESS
(ID, NAME, EMAIL, ACT_FLAG)
Values
(1, 'Abdul Halim', 'halim@test.com', 'Y');
Insert into DBA_MAIN.EMAIL_ADDRESS
(ID, NAME, EMAIL, ACT_FLAG)
Values
(2, 'user2', 'test@test.com', 'N');
Insert into DBA_MAIN.EMAIL_ADDRESS
(ID, NAME, EMAIL, ACT_FLAG)
Values
(3, 'user1', 'test2@test.com', 'N');
COMMIT;
4) Create a package for sending email from the oracle database
(In this package, i created two procedures , one for sending email with attachment
another is sending email without attachment ment. I am using first one here)
-------------------------------------------------------------
SEND_EMAIL_FROM_PL_SQL.send_email_with_attachment
-----------------------------------------------------------
4) create belows trigger on "DBA_MAIN.TBL_ALERT_LOG_ERROR" this table..
--------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER DBA_MAIN.TRG_ALERT_LOG_ERROR_EMAIL
before INSERT ON DBA_MAIN.TBL_ALERT_LOG_ERROR
REFERENCING NEW AS new
FOR EACH ROW
WHEN (
new.send_flg='N'
)
begin
/*
SEND_EMAIL_FROM_PL_SQL.send_email_with_attachment
( sender_email_address varchar2, -- Must be single E-mail address
recipient_email_addresses varchar2,
email_subject varchar2,
email_body_text varchar2,
email_attachment_text clob, -- No restriction on size of text
email_attachment_file_name varchar2
)
*/
for i in (select email from dba_main.email_address where act_flag='Y') loop
SEND_EMAIL_FROM_PL_SQL.send_email_with_attachment
( 'halim@test.com', -- Must be single E-mail address
i.email, 'Test Email from Database!',
'Plz check the Alert for more info.... ' || :new.text,
:new.text, -- No restriction on size of text
'Attention'
) ;
end loop;
end;
/
-------------------------------------------------------------------------
5) create a procedure...for job....
---------------------------------------------------------
CREATE OR REPLACE PROCEDURE dpr_alter_log_error_marge
IS
BEGIN
MERGE INTO TBL_ALERT_LOG_ERROR f_table
USING DBA_MAIN.VW_ALERT_LOG v_table
ON (v_table.text = f_table.text) --- and UPPER (v_table.text) LIKE UPPER ('%ERROR%') )
WHEN MATCHED
THEN
UPDATE SET f_table.SEND_FLG = 'Y'
WHEN NOT MATCHED
THEN
INSERT (NO_OF_LINE, TEXT, SEND_FLG)
VALUES (ROWNUM, v_table.text, 'N');
END;
----------------------------------------------------------
6) create a job like belows ( it will check alert log error and if found then send it email to DBA..
-----------------------------------------------------------
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'DBA_MAIN.DPR_ALTER_LOG_ERROR_MARGE;'
,next_date => to_date('20/12/2013 10:55:34','dd/mm/yyyy hh24:mi:ss')
,interval => 'SYSDATE+60/1440 '
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
------------------------------------------------------------ 
Everything Changes
2 weeks ago