如何使用Oracle的外部表查询警告日志文件

从Oracle9i版本开始，Oracle的外部表技术（Oracle External Tables）有了很大的增强，通过外部表访问外部数据增强了Oracle数据库和外部数据源进行数据交互能力，对于数据仓库和ETL来说，这些增强极大的方便了数据访问。

数据库管理员经常见到的一个例子就是可以使用外部表来访问警告日志文件或其他跟踪文件.

下面的这个例子用来说明外部表的用途。

第一步,创建一个Directory：

[oracle@jumper oracle]$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.4.0 - Production on Sun Oct 15 21:42:28 2006Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning optionJServer Release 9.2.0.4.0 - ProductionSQL> create or replace directory bdump 2 as '/opt/oracle/admin/eygle/bdump';Directory created.SQL> col DIRECTORY_PATH for a30SQL> col owner for a10SQL> select * from dba_directories;OWNER DIRECTORY_NAME DIRECTORY_PATH---------- ------------------------------ SYS BDUMP /opt/oracle/admin/eygle/bdump

第二步,创建一个外部表:

SQL> create table alert_log ( text varchar2(400) )2 organization external (3 type oracle_loader4 default directory BDUMP5 access parameters (6 records delimited by newline7 nobadfile8 nodiscardfile9 nologfile10 )11 location('alert_eygle.log')12 )13 reject limit unlimited14 /Table created.

现在,大家可以通过外部表进行查询警告日志的内容:

SQL> select * from alert_log where rownum < 51;TEXT--------------------------------------Mon Jun 26 12:00:24 2006Starting ORACLE instance (normal)Mon Jun 26 12:00:25 2006WARNING: EINVAL creating segment of size 0x0000000008c00000fix shm parameters in /etc/system or equivalentLICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0SCN scheme 2Using log_archive_dest parameter default valueLICENSE_MAX_USERS = 0SYS auditing is disabledStarting up ORACLE RDBMS Version: 9.2.0.4.0.System parameters with non-default values:processes = 150timed_statistics = TRUEshared_pool_size = 104857600large_pool_size = 0java_pool_size = 0control_files = /opt/oracle/oradata/eygle/control01.ctldb_block_size = 8192db_cache_size = 16777216db_cache_advice = ONcompatible = 9.2.0.0.0db_file_multiblock_read_count= 16fast_start_mttr_target = 300log_checkpoints_to_alert = TRUEundo_management = AUTOundo_tablespace = UNDOTBS1undo_retention = 10800remote_login_passwordfile= EXCLUSIVEdb_domain =instance_name = eyglejob_queue_processes = 10hash_join_enabled = TRUEbackground_dump_dest = /opt/oracle/admin/eygle/bdumpuser_dump_dest = /opt/oracle/admin/eygle/udumpcore_dump_dest = /opt/oracle/admin/eygle/cdumpsort_area_size = 524288db_name = eygleopen_cursors = 500star_transformation_enabled= FALSEquery_rewrite_enabled = FALSEpga_aggregate_target = 52428800aq_tm_processes = 0PMON started with pid=2DBW0 started with pid=3LGWR started with pid=4CKPT started with pid=5SMON started with pid=6RECO started with pid=750 rows selected.SQL>

假如您需要查看数据库中曾经出现过的ORA-错误，那么你可以执行下面的查询:

SQL> select * from alert_log where text like 'ORA-%';TEXT-----------------------------------------------------------------ORA-1652: unable to extend temp segment by 128 in tablespace TEMPORA-1113 signalled during: alter database open...ORA-1113 signalled during: alter database datafile 3 online...ORA-09968: scumnt: unable to lock fileORA-1102 signalled during: ALTER DATABASE MOUNT...ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-01157: cannot identify/lock data file 3 - see DBWR trace fileORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'ORA-27037: unable to obtain file statusORA-01157: cannot identify/lock data file 3 - see DBWR trace fileORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'TEXT---------------------------------------------------------------------------ORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'ORA-27037: unable to obtain file statusORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'ORA-27037: unable to obtain file statusORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'TEXT-------------------------------------------------------------------------ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-1113 signalled during: alter database open...ORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'ORA-27037: unable to obtain file statusORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'ORA-1113 signalled during: alter database open...ORA-1122 signalled during: alter database open...ORA-283 signalled during: ALTER DATABASE RECOVER database ...ORA-1122 signalled during: alter database open...TEXT-------------------------------------------------------------------------ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-1113 signalled during: ALTER DATABASE OPEN...ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-1122 signalled during: ALTER DATABASE OPEN...ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "EYGLE" NORESETL...ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-1991 signalled during: ALTER DATABASE MOUNT...ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'ORA-01115: IO error reading block from file 4 (block # 1)ORA-27069: skgfdisp: attempt to do I/O beyond the range of the fileORA-01122: database file 1 failed verification checkTEXT----------------------------------------------------------------------ORA-01110: data file 1: '/opt/oracle/oradata/eygle/system01.dbf'ORA-01207: file is more recent than controlfile - old controlfileORA-1122 signalled during: alter database open...ORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont...ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'ORA-01115: IO error reading block from file 4 (block # 1)ORA-27069: skgfdisp: attempt to do I/O beyond the range of the fileORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '/opt/oracle/oradata/eygle/system01.dbf'ORA-1194 signalled during: alter database open resetlogs...ORA-283 signalled during: ALTER DATABASE RECOVER datafile 1 ...TEXT-------------------------------------------------------------------------ORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont...ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'ORA-01115: IO error reading block from file 4 (block # 1)ORA-27069: skgfdisp: attempt to do I/O beyond the range of the fileORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '/opt/oracle/oradata/eygle/system01.dbf'ORA-1194 signalled during: alter database open resetlogs...ORA-283 signalled during: ALTER DATABASE RECOVER datafile 1 ...ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-1589 signalled during: ALTER DATABASE OPEN...ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'TEXT-----------------------------------------------------------------------------------ORA-01115: IO error reading block from file 4 (block # 1)ORA-27069: skgfdisp: attempt to do I/O beyond the range of the fileORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '/opt/oracle/oradata/eygle/system01.dbf'ORA-1194 signalled during: alter database open resetlogs...ORA-1109 signalled during: alter database close...ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "EYGLE" NORESETL...ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-1113 signalled during: alter database open...ORA-00202: controlfile: '/opt/oracle/oradata/eygle/control01.ctl'ORA-27037: unable to obtain file statusTEXT---------------------------------------------------------------------ORA-205 signalled during: ALTER DATABASE MOUNT...ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-01501: CREATE DATABASE failedORA-01526: error in opening file '?/rdbms/admin/sql.bsq'ORA-07391: sftopn: fopen errorORA-01526: error in opening file ''ORA-1092 signalled during: CREATE DATABASE eygleORA-1079 signalled during: ALTER DATABASE MOUNT...ORA-1507 signalled during: alter database open...ORA-214 signalled during: alter database mount...ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...TEXT------------------------------------------------------------------------ORA-214 signalled during: ALTER DATABASE MOUNT...ORA-214 signalled during: alter database mount...ORA-1113 signalled during: ALTER DATABASE OPEN...ORA-01157: cannot identify/lock data file 3 - see DBWR trace fileORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'ORA-27037: unable to obtain file statusORA-1113 signalled during: ALTER DATABASE OPEN...ORA-01157: cannot identify/lock data file 3 - see DBWR trace fileORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'ORA-27037: unable to obtain file statusORA-1113 signalled during: alter database open...TEXT-----------------------------------------------------------------------------------ORA-01157: cannot identify/lock data file 3 - see DBWR trace fileORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'ORA-27037: unable to obtain file statusORA-283 signalled during: ALTER DATABASE RECOVER database ...ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-1100 signalled during: alter database mount...ORA-1178 signalled during: alter database create datafile '/opt/oracle/produc...ORA-1516 signalled during: alter database create datafile '/opt/oracle/oradat...ORA-1991 signalled during: ALTER DATABASE MOUNT...ORA-01157: cannot identify/lock data file 3 - see DBWR trace fileORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'TEXT---------------------------------------------------------------------ORA-27037: unable to obtain file statusORA-283 signalled during: ALTER DATABASE RECOVER database ...ORA-01157: cannot identify/lock data file 3 - see DBWR trace fileORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'ORA-27037: unable to obtain file statusORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont...ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-1991 signalled during: ALTER DATABASE MOUNT...ORA-01157: cannot identify/lock data file 3 - see DBWR trace fileORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'ORA-27037: unable to obtain file statusTEXT--------------------------------------------------------------------------ORA-283 signalled during: ALTER DATABASE RECOVER database ...ORA-01157: cannot identify/lock data file 3 - see DBWR trace fileORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'ORA-27037: unable to obtain file statusORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont...ORA-01157: cannot identify/lock data file 3 - see DBWR trace fileORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'ORA-27037: unable to obtain file statusORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont...ORA-279 signalled during: ALTER DATABASE RECOVER database using backup cont...ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...TEXT-------------------------------------------------------------------------ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...ORA-1589 signalled during: alter database open...ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "EYGLE" NORESETL...ORA-1178 signalled during: alter database create datafile '/opt/oracle/produc...ORA-1991 signalled during: ALTER DATABASE MOUNT...ORA-01157: cannot identify/lock data file 3 - see DBWR trace fileORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'ORA-27037: unable to obtain file statusORA-1157 signalled during: alter database open...TEXT-----------------------------------------------------------------------------------ORA-1113 signalled during: alter database open...ORA-1991 signalled during: ALTER DATABASE MOUNT...ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-1031 signalled during: alter database open...ORA-3217 signalled during: ALTER TABLESPACE TEMP DEFAULT STORAGE (INITIAL 10M...ORA-1507 signalled during: alter database close...ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-1507 signalled during: alter database close normal...ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-1106 signalled during: alter database dismount...ORA-1531 signalled during: alter database open...TEXT-----------------------------------------------ORA-1531 signalled during: alter database open...ORA-1531 signalled during: alter database open...ORA-1531 signalled during: alter database open...ORA-1531 signalled during: alter database open...ORA-1109 signalled during: alter database close...ORA-1507 signalled during: alter database close...ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-1185 signalled during: alter database add logfile group 6ORA-350 signalled during: alter database drop logfile group 3...163 rows selected.SQL>