ITPub博客

首页 > 数据库 > Oracle > 用外部表实现Alert日志的查看

用外部表实现Alert日志的查看

原创 Oracle 作者:leon830216 时间:2014-03-01 18:58:35 0 删除 编辑
-- 可以查询 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.

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

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

注册时间:2009-09-18

  • 博文量
    164
  • 访问量
    324074