oracle怎么看alter日志,oracle 外部表查alter日志

创建文件夹，路径是alter日志的路径

create or replace directory data_dir as

‘/u01/app/oracle/diag/rdbms/orcl/orcl/trace‘;--创建外部表

create tablealert_log(

text_linevarchar2(225)

)

organization external

(

TYPE ORACLE_LOADERDEFAULTDIRECTORY DATA_DIR

ACCESS PARAMETERS

(

RECORDS DELIMITEDBYNEWLINE

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)enddays_still_upfrom (selectr,

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_timefrom (selectr,

text_line,

lag(text_line,1) over(order byr) start_time,

lag(text_line,2) over(order byr) last_timefrom (selectrownum r, text_linefromalert_logwhere text_line like ‘___ ___ __ __:__:__ 20__‘

or text_line like ‘Starting ORACLE instance %‘))where text_line like ‘Starting ORACLE instance %‘);

oracle 外部表查alter日志

标签：varchar2   sql   ace   direct   create   数据   rdbms   to_date   创建

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：http://www.cnblogs.com/wolil/p/6641032.html