A reader, June 03, 2021 - 5:20 am UTC
Mike, June 03, 2021 - 7:30 am UTC
We were hoping for a solution that didn't require extra configuration outside of our application, but it does seem like that would be our only option. Is there a valid reason why Oracle doesn't provide a GV$DIAG_ALERT_EXT?
June 08, 2021 - 2:30 am UTC
I'll ask around internally and post any findings.
Possible Alternative
Victor Torres, June 03, 2021 - 11:40 am UTC
I think to remember this used to work, but I have not try it for a while and don't remember from where I got it from... so please test it first (no that we should mentioned that, right?)
set lines 500
set pages 50
col originating_timestamp for a40
col rownum for 999999
col Error for a150
-- SET PAUSE ON
-- SET PAUSE 'Press Return to Continue'
select inst_id, rownum "Line",TO_CHAR(originating_timestamp,'DD-MON-YYYY HH24:MI:SS'), message_text "Error"
from TABLE(gv$(cursor(select inst_id, originating_timestamp, message_text
from v$diag_alert_ext
where originating_timestamp >= (sysdate - 1)
AND regexp_like(message_text, '(ORA-|error)'))))
order by originating_timestamp asc;
June 08, 2021 - 2:34 am UTC
Nice touch. I'd forgotten about the GV$ function.