使用Oracle的外部表查詢警告日志文件

　　從Oraclei開始Oracle的外部表技術（Oracle External Tables）被極大的增強通過外部表訪問外部數據增強了Oracle數據庫和外部數據源進行數據交互的能力對於數據倉庫和ETL來說這些增強極大的方便了數據訪問

　　對於DBA來說最常見一個例子是可以使用外部表來訪問警告日志文件或其他跟蹤文件
以下一個例子用來說明外部表的用途

　　首先需要創建一個Directory

[oracle@jumper oracle]$ sqlplus / as sysdba

　　SQL*Plus: Release Production on Sun Oct ::

　　Copyright (c) Oracle Corporation All rights reserved

　　Connected to:
Oraclei Enterprise Edition Release Production
With the Partitioning option
JServer Release Production

　　SQL> create or replace directory bdump
as /opt/oracle/admin/eygle/bdump;

　　Directory created

　　SQL> col DIRECTORY_PATH for a
SQL> col owner for a
SQL> select * from dba_directories;

　　OWNER DIRECTORY_NAME DIRECTORY_PATH

SYS BDUMP /opt/oracle/admin/eygle/bdump

　　然後創建一個外部表:

SQL> create table alert_log ( text varchar() )
organization external (
type oracle_loader
default directory BDUMP
access parameters (
records delimited by newline
nobadfile
nodiscardfile
nologfile
)
location(alert_eyglelog)
)
reject limit unlimited
/

　　Table created

然後我們就可以通過外部表進行查詢警告日志的內容:

SQL> select * from alert_log where rownum < ;

　　TEXT

Mon Jun ::
Starting ORACLE instance (normal)
Mon Jun ::
WARNING: EINVAL creating segment of size xc
fix shm parameters in /etc/system or equivalent
LICENSE_MAX_SESSION =
LICENSE_SESSIONS_WARNING =
SCN scheme
Using log_archive_dest parameter default value
LICENSE_MAX_USERS =
SYS auditing is disabled
Starting up ORACLE RDBMS Version:
System parameters with nondefault values:
processes =
timed_statistics = TRUE
shared_pool_size =
large_pool_size =
java_pool_size =
control_files = /opt/oracle/oradata/eygle/controlctl
db_block_size =
db_cache_size =
db_cache_advice = ON
compatible =
db_file_multiblock_read_count=
fast_start_mttr_target =
log_checkpoints_to_alert = TRUE
undo_management = AUTO
undo_tablespace = UNDOTBS
undo_retention =
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = eygle
job_queue_processes =
hash_join_enabled = TRUE
background_dump_dest = /opt/oracle/admin/eygle/bdump
user_dump_dest = /opt/oracle/admin/eygle/udump
core_dump_dest = /opt/oracle/admin/eygle/cdump
sort_area_size =
db_name = eygle
open_cursors =
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target =
aq_tm_processes =
PMON started with pid=
DBW started with pid=
LGWR started with pid=
CKPT started with pid=
SMON started with pid=
RECO started with pid=

　　 rows selected

　　SQL>

　　如果我們需要查看數據庫中曾經出現過的ORA錯誤那麼可以執行如下查詢:

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

　　TEXT

ORA: unable to extend temp segment by in tablespace TEMP
ORA signalled during: alter database open
ORA signalled during: alter database datafile online
ORA: scumnt: unable to lock file
ORA signalled during: ALTER DATABASE MOUNT
ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/usersdbf
ORA: unable to obtain file status
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/usersdbf

　　TEXT

ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/eygledbf
ORA: unable to obtain file status
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/eygledbf
ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/eygledbf
ORA: unable to obtain file status
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/eygledbf

　　TEXT

ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA signalled during: alter database open
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/eygledbf
ORA: unable to obtain file status
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/eygledbf
ORA signalled during: alter database open
ORA signalled during: alter database open
ORA signalled during: ALTER DATABASE RECOVER database
ORA signalled during: alter database open

　　TEXT

ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA signalled during: ALTER DATABASE OPEN
ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA signalled during: ALTER DATABASE OPEN
ORA signalled during: CREATE CONTROLFILE REUSE DATABASE EYGLE NORESETL
ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA signalled during: ALTER DATABASE MOUNT
ORA: data file : /opt/oracle/oradata/eygle/eygledbf
ORA: IO error reading block from file (block # )
ORA: skgfdisp: attempt to do I/O beyond the range of the file
ORA: database file failed verification check

　　TEXT

ORA: data file : /opt/oracle/oradata/eygle/systemdbf
ORA: file is more recent than controlfile old controlfile
ORA signalled during: alter database open
ORA signalled during: ALTER DATABASE RECOVER database using backup cont
ORA: data file : /opt/oracle/oradata/eygle/eygledbf
ORA: IO error reading block from file (block # )
ORA: skgfdisp: attempt to do I/O beyond the range of the file
ORA: file needs more recovery to be consistent
ORA: data file : /opt/oracle/oradata/eygle/systemdbf
ORA signalled during: alter database open resetlogs
ORA signalled during: ALTER DATABASE RECOVER datafile

　　TEXT

ORA signalled during: ALTER DATABASE RECOVER database using backup cont
ORA: data file : /opt/oracle/oradata/eygle/eygledbf
ORA: IO error reading block from file (block # )
ORA: skgfdisp: attempt to do I/O beyond the range of the file
ORA: file needs more recovery to be consistent
ORA: data file : /opt/oracle/oradata/eygle/systemdbf
ORA signalled during: alter database open resetlogs
ORA signalled during: ALTER DATABASE RECOVER datafile
ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA signalled during: ALTER DATABASE OPEN
ORA: data file : /opt/oracle/oradata/eygle/eygledbf

　　TEXT

ORA: IO error reading block from file (block # )
ORA: skgfdisp: attempt to do I/O beyond the range of the file
ORA: file needs more recovery to be consistent
ORA: data file : /opt/oracle/oradata/eygle/systemdbf
ORA signalled during: alter database open resetlogs
ORA signalled during: alter database close
ORA signalled during: CREATE CONTROLFILE REUSE DATABASE EYGLE NORESETL
ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA signalled during: alter database open
ORA: controlfile: /opt/oracle/oradata/eygle/controlctl
ORA: unable to obtain file status

　　TEXT

ORA signalled during: ALTER DATABASE MOUNT
ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA: CREATE DATABASE failed
ORA: error in opening file ?/rdbms/admin/sqlbsq
ORA: sftopn: fopen error
ORA: error in opening file
ORA signalled during: CREATE DATABASE eygle
ORA signalled during: ALTER DATABASE MOUNT
ORA signalled during: alter database open
ORA signalled during: alter database mount
ORA signalled during: ALTER DATABASE CLOSE NORMAL

　　TEXT

ORA signalled during: ALTER DATABASE MOUNT
ORA signalled during: alter database mount
ORA signalled during: ALTER DATABASE OPEN
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/eygledbf
ORA: unable to obtain file status
ORA signalled during: ALTER DATABASE OPEN
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/eygledbf
ORA: unable to obtain file status
ORA signalled during: alter database open

　　TEXT

ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/eygledbf
ORA: unable to obtain file status
ORA signalled during: ALTER DATABASE RECOVER database
ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA signalled during: alter database mount
ORA signalled during: alter database create datafile /opt/oracle/produc
ORA signalled during: alter database create datafile /opt/oracle/oradat
ORA signalled during: ALTER DATABASE MOUNT
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/eygledbf

　　TEXT

ORA: unable to obtain file status
ORA signalled during: ALTER DATABASE RECOVER database
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/eygledbf
ORA: unable to obtain file status
ORA signalled during: ALTER DATABASE RECOVER database using backup cont
ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA signalled during: ALTER DATABASE MOUNT
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/usersdbf
ORA: unable to obtain file status

　　TEXT

ORA signalled during: ALTER DATABASE RECOVER database
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/usersdbf
ORA: unable to obtain file status
ORA signalled during: ALTER DATABASE RECOVER database using backup cont
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/usersdbf
ORA: unable to obtain file status
ORA signalled during: ALTER DATABASE RECOVER database using backup cont
ORA signalled during: ALTER DATABASE RECOVER database using backup cont
ORA signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT

　　TEXT

ORA signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT
ORA signalled during: ALTER DATABASE RECOVER CANCEL
ORA signalled during: alter database open
ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA signalled during: CREATE CONTROLFILE REUSE DATABASE EYGLE NORESETL
ORA signalled during: alter database create datafile /opt/oracle/produc
ORA signalled during: ALTER DATABASE MOUNT
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file : /opt/oracle/oradata/eygle/eygledbf
ORA: unable to obtain file status
ORA signalled during: alter database open

　　TEXT

ORA signalled during: alter database open
ORA signalled during: ALTER DATABASE MOUNT
ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA signalled during: alter database open
ORA signalled during: ALTER TABLESPACE TEMP DEFAULT STORAGE (INITIAL M
ORA signalled during: alter database close
ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA signalled during: alter database close normal
ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA signalled during: alter database dismount
ORA signalled during: alter database open

　　TEXT

ORA signalled during: alter database open
ORA signalled during: alter database open
ORA signalled during: alter database open
ORA signalled during: alter database open
ORA signalled during: alter database close
ORA signalled during: alter database close
ORA signalled during: ALTER DATABASE CLOSE NORMAL
ORA signalled during: alter database add logfile group
ORA signalled during: alter database drop logfile group

　　 rows selected

　　SQL>

　　The End

From:http://tw.wingwit.com/Article/program/Oracle/201311/17897.html