ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 通过外部表来查询管理警告日志

通过外部表来查询管理警告日志

原创 Linux操作系统 作者:阿日 时间:2008-08-15 17:27:44 0 删除 编辑
--创建目录指向 alert_.log警告日志的目录
create or replace directory data_dir as 'e:\oracle\product\10.2.0\admin\orcl\bdump';


--创建外部表
create table alert_log
(
text_line varchar2(255)
)
organization external
(
  type oracle_loader default directory data_dir
  access parameters
  (
   records delimited by newline
   fields
   reject rows with all null fields
  )
  location
  (
    'alert_orcl.log'
  )
)
reject limit unlimited
/


--弄始查看信息
select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
round((start_time-last_time)*24*60,2) mins_down,
round((last_time-lag(start_time) over (order by r)),2) days_up,
case when (lead(r) over (order by r) is null )
then round((sysdate-start_time),2)
end days_still_up
from (
select r,
 to_date(last_time, 'Dy Mon DD HH24:MI:SS YYYY') last_time,
 to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
 from (
 select r,
 text_line,
 lag(text_line,1) over (order by r) start_time,
 lag(text_line,2) over (order by r) last_time
 from (
 select rownum r, text_line
 from alert_log
 where text_line like '___ ___ __ __:__:__ 20__'
 or text_line like 'Starting ORACLE instance %'
 )
 )
 where text_line like 'Starting ORACLE instance %'
 )

ORA-01843: 无效的月份
ORA-01846: 无效的周

alter session set nls_date_language='american';
--测试库上的结果:
SQL> select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
  2  to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
  3  round((start_time-last_time)*24*60,2) mins_down,
  4  round((last_time-lag(start_time) over (order by r)),2) days_up,
  5  case when (lead(r) over (order by r) is null )
  6  then round((sysdate-start_time),2)
  7  end days_still_up
  8  from (
  9  select r,
 10   to_date(last_time, 'Dy Mon DD HH24:MI:SS YYYY') last_time,
 11   to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
 12   from (
 13   select r,
 14   text_line,
 15   lag(text_line,1) over (order by r) start_time,
 16   lag(text_line,2) over (order by r) last_time
 17   from (
 18   select rownum r, text_line
 19   from alert_log
 20   where text_line like '___ ___ __ __:__:__ 20__'
 21   or text_line like 'Starting ORACLE instance %'
 22   )
 23   )
 24   where text_line like 'Starting ORACLE instance %'
 25   )
 26  /

SHUTDOWN             STARTUP               MINS_DOWN    DAYS_UP DAYS_STILL_UP
-------------------- -------------------- ---------- ---------- -------------
                     19-jun-2008 15:40
22-jun-2008 10:15    22-jun-2008 10:15             0       2.77
22-jun-2008 14:44    22-jun-2008 14:45           .98        .19
22-jun-2008 15:04    22-jun-2008 15:04            .2        .01
22-jun-2008 15:20    22-jun-2008 15:20           .33        .01
22-jun-2008 15:21    22-jun-2008 15:21           .25          0
22-jun-2008 15:24    22-jun-2008 15:25            .5          0
22-jun-2008 15:46    22-jun-2008 15:46           .15        .01
22-jun-2008 15:55    22-jun-2008 15:56            .7        .01
22-jun-2008 15:57    22-jun-2008 15:57           .25          0
22-jun-2008 15:58    22-jun-2008 15:59           .57          0

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/67668/viewspace-429442/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-26

  • 博文量
    35
  • 访问量
    144628