使用外部表访问告警日志文件！！！

首先创建目录

SQL> show user
USER 为 "SYS"
SQL> create or replace directory bdump as '/u01/app/oracle/admin/orcl/bdump';

目录已创建。

然后创建外部表

SQL> create table alert_log(text varchar2(400))
  2  organization external(
  3  type oracle_loader
  4  default directory bdump
  5  access parameters(
  6   records delimited by newline
  7   nobadfile
  8   nodiscardfile
  9   nologfile
 10  )
 11  location('alert_orcl.log')
 12  )reject limit unlimited;

表已创建。

查询告警日志文件

SQL> select count(*) from alert_log;

  COUNT(*)
----------
      5793

SQL> select * from alert_log where rownum < 10;

TEXT
----------------------------------------------------------------------------------------------------
Wed Sep 14 01:01:35 2011
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR

已选择9行。

查看数据库中曾经出现过ORA-错误

SQL> select * from alert_log where text like '%ORA-%';

TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-00942: 表或视图不存在
ORA-12012: 自动执行作业 1 出错
ORA-01435: 用户不存在
ORA-12012: 自动执行作业 21 出错
ORA-01435: 用户不存在
ORA-12012: 自动执行作业 21 出错
ORA-01435: 用户不存在
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-19504: 无法创建文件"/u01/app/oracle/archivelog/1_52_761792499.dbf"
ORA-27040: 文件创建错误
ORA-16038: 日志 1 sequence# 52 无法归档
ORA-19504: 无法创建文件""
ORA-00312: 联机日志 1 线程 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-16014: 日志 1 sequence# 52 未归档
ORA-00312: 联机日志 1 线程 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-16014: 日志 1 sequence# 52 未归档
ORA-00312: 联机日志 1 线程 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-16014: 日志 1 sequence# 52 未归档
ORA-00312: 联机日志 1 线程 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-16014: 日志 1 sequence# 52 未归档
ORA-00312: 联机日志 1 线程 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-1509 signalled during: alter database test rename file '/u01/app/oracle/test01.dbf' to '/u01/app/oracle/oradata/orcl/test01.dbf'...
ORA-1525 signalled during: alter tablespace test rename datafile '/u01/app/oracle/test01.dbf' to '/u01/app/oracle/oradata/orcl/test01.dbf'...
ORA-1525 signalled during: alter tablespace test rename datafile '/u01/app/oracle/test01.dbf' to '/u01/app/oracle/oradata/orcl/test001.dbf'...
ORA-1525 signalled during: alter tablespace test rename datafile '/u01/app/oracle/test01.dbf' to '/u01/app/oracle/oradata/orcl/test001.dbf'...
ORA-959 signalled during: alter database default tablespace test...
ORA-959 signalled during: alter database default tablespace test1...
ORA-3217 signalled during: alter tablespace temp offline...

已选择28行。