使用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