-- 可以查詢 alert 日誌中的任何資訊

-- 建立 bdump 目錄
sys@DEMO> create directory b_dump_dir as 'D:\oracle\product\10.2.0\admin\demo\bdump';

Directory created.

--
sys@DEMO> select * from all_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
----- --------------- -------------------------------------------
SYS   B_DUMP_DIR      D:\oracle\product\10.2.0\admin\demo\bdump

1 rows selected.

-- 賦予使用者許可權
sys@DEMO> grant read, write on directory b_dump_dir to go;

Grant succeeded.

sys@DEMO> conn go/go

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

-- 執行查詢語句
select
    to_char(last_time,'yyyy-mm-dd hh24:mi') shutdown,
    to_char(start_time,'yyyy-mm-dd 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 %'
)
/

SHUTDOWN          STARTUP           MINS_DOWN DAYS_UP DAYS_STILL_UP
----------------- ----------------- ---------- ------- -------------
2011-07-24 15:10 2011-07-24 15:10           0
2011-07-24 15:31 2011-07-24 15:31         .65     .01
2011-07-24 15:36 2011-07-24 15:39        2.48       0
2011-07-24 20:08 2011-07-24 20:57       49.08     .19
2011-07-25 21:51 2011-07-25 21:51           0    1.04
2011-07-29 10:18 2011-07-29 10:19          .9    3.52
2011-07-29 10:20 2011-07-29 10:22           2       0
2011-07-29 10:25 2011-07-29 10:25          .1       0
2011-08-07 11:32 2011-08-07 11:32         .07    9.05
2011-08-07 11:35 2011-08-07 11:36         .05       0
2011-08-07 12:38 2011-08-07 12:38          .5     .04
2011-08-07 17:16 2011-08-07 17:16         .13     .19
2011-08-07 17:21 2011-08-07 17:26        5.18       0
2011-08-07 17:32 2011-08-07 17:32         .12       0
2011-08-08 21:14 2011-08-08 21:14           0    1.15
2011-08-08 22:06 2011-08-08 22:06         .35     .04
2011-08-12 21:39 2011-08-12 21:39           0    3.98
2011-08-12 21:40 2011-08-12 21:40         .05       0
2011-08-13 09:25 2011-08-13 09:25           0     .49          .17

19 rows selected.

用外部表實現Alert日誌的檢視

相關文章