oracle 外部表查alter日志

--创建文件夹,路径是alter日志的路径
create or replace directory data_dir as
'/u01/app/oracle/diag/rdbms/orcl/orcl/trace';

--创建外部表
create table alert_log(
       text_line varchar2(225)
)
organization external
(
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY DATA_DIR
       ACCESS PARAMETERS
       (
              RECORDS DELIMITED BY NEWLINE
              FIELDS
       )
       LOCATION
       (
              'alert_orcl.log'
       )
);

--查询数据库启动时间sql
select to_char(LAST_TIME) shutdown,
       to_char(start_time) starup,
       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',
                       'NLS_DATE_LANGUAGE = American') LAST_TIME,
               to_date(start_time,
                       'Dy Mon DD HH24:MI:SS YYYY',
                       'NLS_DATE_LANGUAGE = American') 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 %');

 

转载于:https://www.cnblogs.com/wolil/p/6641032.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值