使用外部表来查看数据库的日志信息

SYS@PROD1> create or replace directory LOG_FILE_DIR as '/u01/app/oracle/diag/rdbms/prod1/PROD1/trace/';  --指定日志文件路径为字典路径

Directory created.

SYS@PROD1> grant read,write on directory LOG_FILE_DIR to SYS;

Grant succeeded.

SYS@PROD1> create table log_exttb (text varchar2(1000))
  2  organization external
  3  (
  4  type oracle_loader
  5  default directory LOG_FILE_DIR
  6  location ('alert_PROD1.log')
  7  );

Table created.

SYS@PROD1> 
SYS@PROD1> select count(*) from log_exttb;

  COUNT(*)
----------
       584
	   
SYS@PROD1> select * from log_exttb where text like '%ORA-%';

TEXT
----------------------------------------------------------------------------------------------------
ORA-12012: error on auto execute of job "SYS"."REBUILD_JOB"
ORA-06550: line 3

大神优化版参考：http://www.askmaclean.com/archives/%E5%88%A9%E7%94%A8%E5%A4%96%E9%83%A8%E8%A1%A8%E5%AE%9E%E7%8E%B0sql%E6%9F%A5%E8%AF%A2oracle%E5%91%8A%E8%AD%A6%E6%97%A5%E5%BF%97alert-log.html