Oracle技術：使用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 < 51;

TEXT

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

Mon Jun 26 12:00:24 2006

Starting ORACLE instance (normal)

Mon Jun 26 12:00:25 2006

WARNING: EINVAL creating segment of size 0x0000000008c00000

fix shm parameters in /etc/system or equivalent

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

SCN scheme 2

Using log_archive_dest parameter default value

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up ORACLE RDBMS Version: 9.2.0.4.0.

System parameters with non-default values:

processes = 150

timed_statistics = TRUE

shared_pool_size = 104857600

large_pool_size = 0

java_pool_size = 0

control_files = /opt/oracle/oradata/eygle/control01.ctl

db_block_size = 8192

db_cache_size = 16777216

db_cache_advice = ON

compatible = 9.2.0.0.0

db_file_multiblock_read_count= 16

fast_start_mttr_target = 300

log_checkpoints_to_alert = TRUE

undo_management = AUTO

undo_tablespace = UNDOTBS1

undo_retention = 10800

remote_login_passwordfile= EXCLUSIVE

db_domain =

instance_name = eygle

job_queue_processes = 10

hash_join_enabled = TRUE