DBA Alert Log

➜ Back to Library
XLSExcel example XMLXML import file

Description

Categories
Enginatics
Repository
GitHub
V$DIAG_ALERT_EXT shows the contents of the XML-based alert log in the Automatic Diagnostic Repository (ADR) for the current container (PDB).
You could schedule it, for example, in incremental mode to send an email with errors that occured since the last scheduled report run.

SQL 12 lines

select
decode(vdae.message_type,1,'Unknown',2,'Incident_Error',3,'Error',4,'Warning',5,'Notification',6,'Trace',vdae.message_type) message_type_desc,
decode(vdae.message_level,1,'Critical',2,'Severe',8,'Important',16,'Normal',vdae.message_level) message_level_desc,
xxen_util.module_type(vdae.module_id) module_type,
xxen_util.module_name(vdae.module_id) module_name,
vdae.*
from
v$diag_alert_ext vdae
where
1=1
order by
vdae.record_id

Parameters 7

ParameterTypeList of values SQL
Message Text includesChar
vdae.message_text like '%'||:message_text_includes||'%'
Message TypeLOV
vdae.message_type=decode(:message_type,'Unknown',1,'Incident_Error',2,'Error',3,'Warning',4,'Notification',5,'Trace',6,:message_type)
Message LevelLOV
vdae.message_level=decode(:message_level,'Critical',1,'Severe',2,'Important',8,'Normal',16,:message_level)
History DaysNumber
vdae.originating_timestamp>=sysdate-:history_days
Date FromDate
vdae.originating_timestamp>=:date_from
Exceptions and ORA-% onlyLOV
(
vdae.message_text like 'Exception%' or
vdae.message_text like '%ORA-%' and
vdae.message_text not like 'Non critical error ORA-48913%' and
vdae.message_text not like  '%Result = ORA-31%' and
vdae.message_text not in ('  Result = ORA-0'||chr(10),'  Result = ORA-30')
)
Incremental ModeLOV
vdae.originating_timestamp>=
(
select
fcr0.actual_start_date
from
fnd_concurrent_requests fcr,
fnd_concurrent_requests fcr0
where
fcr.request_id=fnd_global.conc_request_id and
fcr.parent_request_id=fcr0.request_id
)