oracle 外部表查alter日志
时间：2021-07-01 10:21:17
帮助过：20人阅读
创建文件夹，路径是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 %‘);
oracle 外部表查alter日志
标签：varchar2 sql ace direct create 数据 rdbms to_date 创建
