oracle 外部表格查alter日誌

標籤：varchar2   sql   ace   direct   create   資料   rdbms   to_date   建立   

--建立檔案夾，路徑是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‘       ));--查詢資料庫啟動時間sqlselect 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日誌