首先创建目录

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)) 
      organization external( 
      type oracle_loader 
      default directory bdump 
      access parameters( 
       records delimited by newline 
       nobadfile 
       nodiscardfile 
       nologfile 
   ) 
    location('alert_orcl.log') 
    )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行。