使用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 - ProductionWith the Partitioning optionJServer 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 a30SQL col owner for a10SQL 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_loader4 default directory BDUMP5 access parameters (6 records delimited by newline7 nobadfile8 nodiscardfile9 nologfile10 )11 location(‘alert_eygle.log‘)12 )13 reject limit unlimited14 /
　　Table created.
　　然後我們就可以通過外部表進行查詢警告日誌的內容:
　　SQL select * from alert_log where rownum < 51;
　　TEXT-----------------------------------------------------------------------------------------Mon Jun 26 12:00:24 2006Starting ORACLE instance (normal)Mon Jun 26 12:00:25 2006WARNING: EINVAL creating segment of size 0x0000000008c00000fix shm parameters in /etc/system or equivalentLICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0SCN scheme 2Using log_archive_dest parameter default valueLICENSE_MAX_USERS = 0SYS auditing is disabledStarting up ORACLE RDBMS Version: 9.2.0.4.0.System parameters with non-default values:processes = 150timed_statistics = TRUEshared_pool_size = 104857600large_pool_size = 0java_pool_size = 0control_files = /opt/oracle/oradata/eygle/control01.ctldb_block_size = 8192db_cache_size = 16777216db_cache_advice = Ncompatible = 9.2.0.0.0db_file_multiblock_read_count= 16fast_start_mttr_target = 300log_checkpoints_to_alert = TRUEundo_management = AUTOundo_tablespace = UNDOTBS1undo_retention = 10800remote_login_passwordfile= EXCLUSIVEdb_domain =instance_name = eyglejob_queue_processes = 10hash_join_enabled = TRUEbackground_dump_dest = /opt/oracle/admin/eygle/bdumpuser_dump_dest = /opt/oracle/admin/eygle/udumpcore_dump_dest = /opt/oracle/admin/eygle/cdumpsort_area_size = 524288db_name = eygleopen_cursors = 500star_transformation_enabled= FALSEquery_rewrite_enabled = FALSEpga_aggregate_target = 52428800aq_tm_processes = 0PMON started with pid=2DBW0 started with pid=3LGWR started with pid=4CKPT started with pid=5SMON started with pid=6RECO 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 TEMPORA-1113 signalled during: alter database open...ORA-1113 signalled during: alter database datafile 3 online...ORA-09968: scumnt: unable to lock fileORA-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 fileORA-01110: data file 3: ‘/opt/oracle/oradata/eygle/users01.dbf‘ORA-27037: unable to obtain file statusORA-01157: cannot identify/lock data file 3 - see DBWR trace fileORA-01110: data file 3: ‘/opt/oracle/oradata/eygle/users01.dbf‘
　　TEXT-----------------------------------------------------------------------------------ORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-01110: data file 4: ‘/opt/oracle/oradata/eygle/eygle01.dbf‘ORA-27037: unable to obtain file statusORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-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 fileORA-01110: data file 4: ‘/opt/oracle/oradata/eygle/eygle01.dbf‘ORA-27037: unable to obtain file statusORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-01110: data file 4: ‘/opt/oracle/oradata/eygle/eygle01.dbf‘

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23757700/viewspace-697161/，如需轉載，請註明出處，否則將追究法律責任。