Starting with Oracle version 21c, in addition to the alert.log, a new file is delivered: the Attention.log. This file is intended to contain important information as a summary of the alert.log.
Well, I hope this gets improved a bit, but still, it would be useful to read the file using a SELECT statement. I was hoping someone had already done the work (Google, where are you when you need it...), but unfortunately, I found nothing.
Okay, then I’ll do it myself…
We give the objects to the SYSTEM user, but of course, you can also create another user for this purpose.
ALTER SESSION SET current_schema=system;
col adir new_value adir
col afile new_value afile
with diag as (select sys_context('userenv','PLATFORM_SLASH') as ps,value from v$diag_info
where name='Attention Log')
select
substr(value,1,instr(value,ps,-1)-1) as adir,
substr(value,instr(value,ps,-1)+1) as afile
from diag;
CREATE OR REPLACE DIRECTORY attention_dir as '&adir.';
GRANT READ ON DIRECTORY attention_dir to system;
CREATE OR REPLACE TYPE attention_type
AS OBJECT (
NOTIFICATION VARCHAR2(4000),
ERROR VARCHAR2(4000),
URGENCY VARCHAR2(4000),
INFO VARCHAR2(4000),
CAUSE VARCHAR2(4000),
ACTION VARCHAR2(4000),
CLASS VARCHAR2(4000),
TIME TIMESTAMP WITH TIME ZONE
);
/
CREATE OR REPLACE TYPE attention_tab_type AS TABLE OF attention_type;
/
CREATE OR REPLACE FUNCTION read_attention_log
RETURN attention_tab_type PIPELINED
IS
f_handle utl_file.file_type:=utl_file.fopen(
location=>'ATTENTION_DIR',
filename=>'&afile.',
open_mode=>'r',
max_linesize=>32767);
text varchar2(32767);
v_a_log attention_type:=attention_type(null,null,null,null,null,null,null,null);
v_i INT:=1;
BEGIN
LOOP
BEGIN
utl_file.get_line(f_handle,text); -- Neue Zeile lesen
IF substr(text,1,16)=' "NOTIFICATION"' THEN
v_a_log.NOTIFICATION:=rtrim(rtrim(substr(text,21),','),'"');
END IF;
IF substr(text,1,9)=' "ERROR"' THEN
v_a_log.ERROR:=rtrim(rtrim(substr(text,21),','),'"');
END IF;
IF substr(text,1,11)=' "URGENCY"' THEN
v_a_log.URGENCY:=rtrim(rtrim(substr(text,21),','),'"');
END IF;
IF substr(text,1,8)=' "INFO"' THEN
v_a_log.INFO:=rtrim(rtrim(substr(text,21),','),'"');
END IF;
IF substr(text,1,9)=' "CAUSE"' THEN
v_a_log.CAUSE:=rtrim(rtrim(substr(text,21),','),'"');
END IF;
IF substr(text,1,10)=' "ACTION"' THEN
v_a_log.ACTION:=rtrim(rtrim(substr(text,21),','),'"');
END IF;
IF substr(text,1,9)=' "CLASS"' THEN
v_a_log.CLASS:=rtrim(rtrim(substr(text,21),','),'"');
END IF;
IF substr(text,1,8)=' "TIME"' THEN
v_a_log.TIME:=to_timestamp_tz(rtrim(substr(text,21),'"'),'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM');
PIPE ROW (v_a_log);
v_a_log :=attention_type(null,null,null,null,null,null,null,null);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN EXIT; -- Keine Zeile im Attention.log mehr gefunden oder Fehler=> Schleife verlassen
WHEN OTHERS THEN RAISE;
END;
END LOOP;
utl_file.fclose(f_handle);
END;
/
And now you can read the attention.log via SQL...
SELECT * FROM table(read_attention_log);