【原创】用外部表的方式查询当天数据库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