從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
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 = 524288
db_name = eygle
open_cursors = 500
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 52428800
aq_tm_processes = 0
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
50 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 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>
6．select較新的資料
SQL> col rowxx noprint
SQL>select * from (select rownum rowxx,a.* from alert_log a) where rowxx > 41350;
7. 建立NormalTables: alert_log_auto
Create table alert_log_auto(line_no number,Text varchar2(400),sys_date date);
8. 創建過程,用來將數據從alert_log寫入alert_log_auto
create or replace procedure auto_record is
begin
insert into alert_log_auto(line_no,Text,sys_date)
select b.*,sysdate from (
select rownum line_no,text from alert_log) b
where not exists (select 'x' from alert_log_auto a where b.line_No=a.line_no );
commit;
exception
when others then
rollback;
end;
/
9. 設定Job,用來執行過程auto_record ,Job 間隔時間暫設為1小時(可根據個人狀況調整)10. 對表alert_log_auto中的數據進行分析,篩選儅有ora-錯誤或其它關鍵字時,觸發另外事件, 寫入DBA會隨時檢視的表中
-The End-