external table + alert_sid.log

创建 directory

– create alert directory
select *
from dba_directories dd
where 1=1
and dd.directory_name=’ALERT_DIR’
;

create or replace directory alert_dir as ‘/oracle/admin/orcl/bdump’

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
)

备注: md 导入后sql行缩进就消失了，需要大家手动格式化下。