alert log in xml format

The alert log is in xml format in Oracle 11g. If you want to parse the content of the file and use the XML functions to retrieve the data, you can use this way :

SQL> create or replace directory alert as
  2    '/app/oracle/diag/rdbms/lsc01/lsc01/alert';

Directory created.

SQL> var c clob
SQL> declare
  2     b bfile := bfilename('ALERT','log.xml');
  3  begin
  4     dbms_lob.open(b,dbms_lob.file_readonly);
  5     dbms_lob.createtemporary(:c,true);
  6     dbms_lob.loadfromfile(:c,b,dbms_lob.lobmaxsize);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select extractvalue(xmlparse(content :c),
  2    '/msg[@time="2008-03-30T01:01:13.704+01:00"]/txt/text()')
  3  from dual;
EXTRACTVALUE(XMLPARSE(CONTENT:C),'/MSG[@TIME="2008-03-30T01:01:1
----------------------------------------------------------------

Starting ORACLE instance (normal)

13 thoughts on “alert log in xml format”

  1. thanks, the idea of using bfile came by reading your site, however as it is not a wellformed document I could not use XMLTYPE(bfile) so I searched for something else :)

  2. I just activate the Live Preview on the comments!

    I mean I like

    xmltype(bfilename(…))

    but it works only with documents, so I could not write a zero plsql solution

    and xmltype(bfilename(),wellformed=>1) does not work with not well formed documents

  3. The problem is that the log file is officially not a wellformed xml document. A wellformed xml document needs a root element. Sorry for the delay, I just played second player in mario Galaxy.

    ;-)

    I send you an email with the internediate results…

  4. Pingback: sunny days of oracle dba » Blog Archive » Read alert log from SQL

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>