【原創】用外部表的方式查詢當天資料庫alert日誌檔案中當天所有的ora-錯誤資訊

外部表：表中的資料以作業系統檔案的方式來存放，現在表中的資料不是放在資料庫中了而是放在作業系統上面，Oracle提供了一種直接讀取外部資料的機制。

外部表好處：1.資料二次開發

           2.大資料量遷移

           3.充分利用作業系統空間

           4.不佔用資料庫空間

           5.支援標準SQL條件檢索

外部表也需要目錄物件的支援，透過目錄物件可以知道從哪個目錄讀取文字資料

LEO1@LEO1>create directory alert as'/u02/app/oracle/diag/rdbms/leo1/LEO1/trace';

Directory created.

這是Oracle 11g 告警日誌目錄

grant read,write on directory alert topublic;            對這個目錄物件授予讀/寫許可權，並授予所有使用者

LEO1@LEO1>select * from dba_directories;

OWNER         DIRECTORY_NAME                 DIRECTORY_PATH

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

SYS             EXP_DUMP                      /home/oracle/exp_dump

SYS            XMLDIR                         /u02/app/oracle/product/11.2.0/db_1/rdbms/xml

SYS             ALERT                         /u02/app/oracle/diag/rdbms/leo1/LEO1/trace

SYS             DATA_PUMP_DIR                /u02/app/oracle/admin/LEO1/dpdump/

SYS             ORACLE_OCM_CONFIG_DIR       /u02/app/oracle/product/11.2.0/db_1/ccr/state

我們下面就是Oracle告警日誌檔案當作資料庫的一個外部資料來源來訪問，我們使用外部表的方式抽取alert日誌資料，然後使用標準SQL語句來檢索“ora-錯誤資訊”。

下面我們就來建立一個外部表

LEO1@LEO1>create table leo_alert(content varchar2(4000))     alert日誌資料量多因此字串設定的大一點

organization external

(

type oracle_loader                                       如果你設定的是oracle_datapump請修改為loader

default directory alert

access parameters (

records delimited by newline                               每條記錄用換行區分

nobadfile                                               沒有壞檔案，丟棄檔案，日誌檔案

nodiscardfile

nologfile

)

location ('alert_LEO1.log')                                  載入告警日誌檔案內容

); 2    3    4   5    6    7   8    9   10  11   12   13  

LEO1@LEO1>select count(*) fromleo_alert;                   一共7198條

  COUNT(*)

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

     7198

我們抽取其中10條ORA-開頭的錯誤記錄顯示出來

LEO1@LEO1>select * from leo_alert wherecontent like '%ORA-%' and rownum<=10;

CONTENT

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

ORA-210 signalled during: create tablespacetest datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextendoff...

ORA-00210: cannot open the specifiedcontrol file

ORA-00202: control file:'/u02/app/oracle/oradata/LEO1/control01.ctl'

ORA-27041: unable to open file

ORA-00210: cannot open the specifiedcontrol file

ORA-00202: control file:'/u02/app/oracle/oradata/LEO1/control01.ctl'

ORA-27041: unable to open file

ORA-00210: cannot open the specifiedcontrol file

ORA-00202: control file:'/u02/app/oracle/oradata/LEO1/control01.ctl'

ORA-27037: unable to obtain file status

10 rows selected.

小結：這裡需要注意幾個問題，我們在建立外部表的時候需要設定沒有壞檔案，丟棄檔案，日誌檔案引數否則會報錯ORA-29913: error in executing ODCIEXTTABLEOPEN callout。

sql*loader     exp/imp       expdp/impdp       organization_external       direct

2013.6.22
北京&summer
分享技術~成就夢想
Blog：