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
