Database Reference
In-Depth Information
EODA@ORA12CR1> CREATE TABLE alert_log
2 (
3 text_line varchar2(4000)
4 )
5 ORGANIZATION EXTERNAL
6 (
7 TYPE ORACLE_LOADER
8 DEFAULT DIRECTORY data_dir
9 ACCESS PARAMETERS
10 (
11 records delimited by newline
12 fields
13 )
14 LOCATION
15 (
16 'alert_ora12cr1.log'
17 )
18 )
19 reject limit unlimited
20 /
Table created.
We can now query that information anytime:
EODA@ORA12CR1> 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 /
