通过外部表查看Alert日志

1.找到Alert日志所在的目录
show parameter background_dump_dest;
SQL> show parameter background_dump_dest;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /oracle/app/oracle/admin/orarwa/bdump
2.查到alert文件的名称（alert文件命名原则：alert_dbname.log)
SQL> select lower(name)  from v$database;

LOWER(NAM
---------
orarwa
通过查询得到alert文件名为：
alert_orarwa.log
3.创建directory
create directory ext_alert_tab as '/oracle/app/oracle/admin/orarwa/bdump/' ;
4.创建外部表
create table alert_log ( text varchar2(4000) )
organization external (
type oracle_loader
default directory ext_alert_tab
Access parameters (
records delimited by newline
nobadfile
nodiscardfile
nologfile
)
location('alert_orarwa.log')
)
reject limit unlimited   -- 注意这点，否则在查询时会报错 ora-30653
;
5.查看alert信息
select * from alert_log t
where t.text like '%ORA-%'
;
---------------------------------------------------------------------------------