用外部表的方式查詢alert日誌檔案中ora-錯誤資訊

1.建立外部表所需的目錄物件
BYS@ bys001>create or replace directory alert as '/home/oracle/' ;

Directory created.
BYS@ bys001>col directory_path for a15
BYS@ bys001>col owner for a10
BYS@ bys001>select * from dba_directories where directory_name like 'ALERT';

OWNER      DIRECTORY_NAME                 DIRECTORY_PATH
---------- ------------------------------ ---------------
SYS        ALERT                          /home/oracle/

2.建立外部表。

使用Oracle告警日誌檔案當作資料庫的一個外部資料來源來訪問，我這裡是把alert日誌複製到了/home/oracle目錄下。

然後用外部表的方式抽取alert日誌資料，然後使用SQL語句來檢索“ora-錯誤資訊”。

create table test_alert(content varchar2(4000))
  organization external
  (type oracle_loader
  default directory alert
  access parameters(
  records delimited by newline
  nobadfile
  nodiscardfile
  nologfile)
  location('alert_bys001.log'));
 
BYS@ bys001>select count(*) from test;

  COUNT(*)
----------
   1280000

3.查詢

BYS@ bys001>select * from test_alert where content like '%ORA-%' and rownum<10;
CONTENT
----------------------------------------------------------------------------------------------------
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 85.21% used
ORA-38701: Flashback database log 252 seq 1408 thread 1: "/backup/flashback_area/BYS001/flashback/o1_mf_8zdfsm83_.flb"

ORA-27072: File I/O error
ORA-38701: Flashback database log 252 seq 1409 thread 1: "/backup/flashback_area/BYS001/flashback/o1
_mf_8zdg3d06_.flb"

ORA-27072: File I/O error