报错:关于利用外部表实现SQL查询Oracle告警日志Alert.log

ltl_hit

通过执行
--drop table alert_log_view;
--drop directory bdump;

declare
  path_bdump varchar2(4000);
  name_alert varchar2(4000);
  ins_name   varchar2(200);
begin

  select value
    into path_bdump
    from sys.v_$parameter
   where name = 'background_dump_dest';
  select 'alert_' || value || '.log'
    into name_alert
    from sys.v_$parameter
   where name = 'instance_name';

  select value
    into ins_name
    from sys.v_$parameter
   where name = 'instance_number';

  if ins_name = '0' then
    ins_name := '';
  end if;

  execute immediate 'create or replace directory bdump'||ins_name||' as ''' || path_bdump || '''';
  execute immediate 'create table ALERT_LOG_VIEW' || ins_name ||
                    '  (MSG_line varchar2(4000)   ) ' ||
                    ' organization external ' || ' (type oracle_loader ' ||
                    ' default directory bdump' || ins_name ||
                    ' 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 (''' || name_alert || ''') )' ||
                    ' reject limit unlimited ' ||
                    ' noparallel nomonitoring ';

end;
/

col lineno noprint
col ora_error noprint
col msg_line format a132
set pages 0 lines 300 trimspool on trim on
alter session set nls_date_language = 'american';
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
alter session set sql_trace=false;
break on thedate
prompt
prompt ERROR IN ALERT LOG FILE - LAST 3 DAYS
prompt =====================================
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-%') 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 msg_line like
                                              '___ ___ __ __:__:__ ____' then
                                          to_date(msg_line,
                                                  'Dy Mon DD hh24:mi:ss yyyy')
                                         else
                                          null
                                       end thedate
                                  from ALERT_LOG_VIEW))))
where ora_error is not null
   and thedate >= (trunc(sysdate) - 3)
order by thedate


执行完之后报错误,内容为:

select "LINENO", "THEDATE", "ORA_ERROR", "MSG_LINE"
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-01846: not a valid day of the week
ORA-06512: at "SYS.ORACLE_LOADER", line 52


问题可能出在日期格式那?但怎么解决呢

Maclean Liu(刘相兵

select 1 from ALERT_LOG_VIEW where rownum<=2;

ltl_hit

而且有ORA-和PLS的日志,但是查不出来

ltl_hit

thedate >= (trunc(sysdate) - 3)的问题

ltl_hit

Maclean Liu(刘相兵 发表于 2013-6-6 15:20
select 1 from ALERT_LOG_VIEW where rownum

16:05:17 sys@REPDB>select 1 from ALERT_LOG_VIEW where rownum<=2;

         1
----------
         1
         1
这样是有记录的,只要加上这条件就报错

ltl_hit

ltl_hit 发表于 2013-6-6 16:05
16:05:17 sys@REPDB>select 1 from ALERT_LOG_VIEW where rownum

   and thedate >= (sysdate - 3)

ltl_hit

Fri Dec 02 21:08:17 EAT 2011(也是thedate字段的内容)这是ALERT里面的日期格式
怎么与SYSDATE进行比较呢？