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 %‘);

 

oracle 外部表查alter日志

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

本文系统来源：http://www.cnblogs.com/wolil/p/6641032.html

【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！