Laurent Schneider

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)

This entry was posted on Saturday, April 12th, 2008 at 19:11 and is filed under oracle, sql, dba, xml. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

7 Responses to “alert log in xml format”

Marco Gralike Says:
April 15th, 2008 at 20:06
Damn, you were faster then me (but as always your stuff is more fancy) !

Grz

Marco
Laurent Schneider Says:
April 15th, 2008 at 20:09
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
Marco Gralike Says:
April 15th, 2008 at 20:53
I got the idea - again - after readin Christo’s http://www.pythian.com/blogs/902/how-to-make-an-in-database-listenerlog-file

But stressed for time I didn’t came to it…

What about a nested
```
xmltype('&lt;root&gt;'||xmlparse(xxx)||'&lt;/root&gt;')
```
and /or using xmlconact instead of ||

and / or have a look at

http://forums.oracle.com/forums/thread.jspa?threadID=640934&tstart=0

but be aware of Mark’s last remark.
Marco Gralike Says:
April 15th, 2008 at 20:55
the ‘ ‘ should be [root] and a [/root], but then not squared brackets but the bigger and smaller signs used in tags.
Laurent Schneider Says:
April 15th, 2008 at 21:32
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
Marco Gralike Says:
April 15th, 2008 at 23:25
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…
Laurent Schneider Says:
April 15th, 2008 at 23:27
:)
I just beaten final boss in medium level in guitar heroes…

Use <code> and </code> to post code

alert log in xml format

7 Responses to “alert log in xml format”

Leave a Reply