--创建目录指向 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博客

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

最新文章