快捷搜索:  www.ymwears.cn

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

从Oracle9i开始,Oracle的外部表技巧(Oracle External Tables)被极大年夜的增强,经由过程外部表造访外部数据增强了Oracle数据库和外部数据源进行数据交互的能力,对付数据仓库和ETL来说,这些增强极大年夜的方便了数据造访。

对付DBA来说,最常见一个例子是可以应用外部表来造访警告日志文件或其他跟踪文件.

以下一个例子用来阐明外部表的用途。

首先必要创建一个Directory:

[oracle@jumper oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Sun Oct 15 21:42:28 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning option

JServer Release 9.2.0.4.0 - Production

SQL> create or replace directory bdump

2 as '/opt/oracle/admin/eygle/bdump';

Directory created.

SQL> col DIRECTORY_PATH for a30

SQL> col owner for a10

SQL> 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_loader

4 default directory BDUMP

5 access parameters (

6 records delimited by newline

7 nobadfile

8 nodiscardfile

9 nologfile

10 )

11 location('alert_eygle.log')

12 )

13 reject limit unlimited

14 /

Table created.

然后我们就可以经由过程外部表进行查询警告日志的内容:

SQL> select * from alert_log where rownum

假如我们必要查看数据库中曾经呈现过的ORA-差错,那么可以履行如下查询:

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

TEXT

-----------------------------------------------------------------------------------

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

ORA-1113 signalled during: alter database open...

ORA-1113 signalled during: alter database datafile 3 online...

ORA-09968: scumnt: unable to lock file

ORA-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 file

ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'

ORA-27037: unable to obtain file status

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'

TEXT

-----------------------------------------------------------------------------------

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'

ORA-27037: unable to obtain file status

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-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 file

ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'

ORA-27037: unable to obtain file status

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-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 file

ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'

ORA-27037: unable to obtain file status

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-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 file

ORA-01122: database file 1 failed verification check

TEXT

-----------------------------------------------------------------------------------

ORA-01110: data file 1: '/opt/oracle/oradata/eygle/system01.dbf'

ORA-01207: file is more recent than controlfile - old controlfile

ORA-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 file

ORA-01194: file 1 needs more recovery to be consistent

ORA-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 file

ORA-01194: file 1 needs more recovery to be consistent

ORA-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 file

ORA-01194: file 1 needs more recovery to be consistent

ORA-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 status

TEXT

-----------------------------------------------------------------------------------

ORA-205 signalled during: ALTER DATABASE MOUNT...

ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...

ORA-01501: CREATE DATABASE failed

ORA-01526: error in opening file '?/rdbms/admin/sql.bsq'

ORA-07391: sftopn: fopen error

ORA-01526: error in opening file ''

ORA-1092 signalled during: CREATE DATABASE eygle

ORA-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 file

ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'

ORA-27037: unable to obtain file status

ORA-1113 signalled during: ALTER DATABASE OPEN...

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'

ORA-27037: unable to obtain file status

ORA-1113 signalled during: alter database open...

TEXT

-----------------------------------------------------------------------------------

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'

ORA-27037: unable to obtain file status

ORA-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 file

ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'

TEXT

-----------------------------------------------------------------------------------

ORA-27037: unable to obtain file status

ORA-283 signalled during: ALTER DATABASE RECOVER database ...

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'

ORA-27037: unable to obtain file status

ORA-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 file

ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'

ORA-27037: unable to obtain file status

TEXT

-----------------------------------------------------------------------------------

ORA-283 signalled during: ALTER DATABASE RECOVER database ...

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'

ORA-27037: unable to obtain file status

ORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont...

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'

ORA-27037: unable to obtain file status

ORA-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 file

ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'

ORA-27037: unable to obtain file status

ORA-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 6

ORA-350 signalled during: alter database drop logfile group 3...

163 rows selected.

SQL>

-The End-

原文地址:http://www.eygle.com/archives/2006/10/use_oracle_external_table.html

您可能还会对下面的文章感兴趣: