external table + alert_sid.log详解数据库

alert_dir 需要根据 oracle 版本不同而不同
– grant

grant read,write on directory ALERT_DIR to system 

; 

– Create table

create table ALERT_LOG_VIEW_ORCL 

 MSG_LINE VARCHAR2(4000) 

organization external 

 type ORACLE_LOADER 

 default directory ALERT_DIR 

 access parameters 

 records delimited by newline 

 nobadfile 

 nologfile 

 nodiscardfile skip 0 

 READSIZE 10485760 FIELDS LDRTRIM 

 REJECT ROWS WITH ALL NULL FIELDS 

 (MSG_LINE (1:1000) CHAR(1000)) 

 location (ALERT_DIR:alert_orcl.log) 

reject limit UNLIMITED; 

select 

 LINENO, 

 THEDATE, 

 ORA_ERROR, 

 MSG_LINE 

from ( 

 select LINENO, 

 THEDATE, 

 ORA_ERROR, 

 MSG_LINE 

 from (select * 

 from ( 

 select lineno, 

 msg_line, 

 thedate, 

 max(case 

 when ( ora_error like %ORA-% 

 or ora_error like %PLS-% 

 or ora_error like %TNS-% 

 or ora_error like %WARNING% 

 ) then 

 rtrim(substr(ora_error, 1, instr(ora_error,  ) - 1), 

 :) 

 else 

 null 

 end) over(partition by thedate) ora_error 

 from ( 

 select lineno, 

 msg_line, 

 max(thedate) over(order by lineno) thedate, 

 lead(msg_line) over(order by lineno) ora_error 

 from ( select rownum lineno, 

 substr(msg_line, 1, 132) msg_line, 

 case 

 when replace(msg_line,CST ,) like ___ ___ __ __:__:__ ____ 

 then to_date(replace(msg_line,CST ,),Dy Mon DD hh24:mi:ss yyyy) 

 else null 

 end thedate 

 from alert_log_view_orcl 

 where 1=1 

 and ora_error is not null 

 and thedate = (sysdate - 1) 

 order by thedate 

) 

原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/3909.html