Quite a while ago I wrote about how to setup the alert log as an external table. Since then 11g has been introduced and is now widely used. It of course changes the location and makes the alert log an xml file.

While it is possible to select from it using xml functions like Laurent Schneider does here, it is still a bit cumbersome.

Tanel Poder (@TanelPoder) found a nicer way by using X$DBGALERTEXT which does a really nice job of parsing the xml-file into a lot of different columns. A friend at work, Daniel Ekberg (@dan_ekb), let me know about it a while back. I just never got around to looking into it until today.

It is very nice but there is one slight issue with it. It is X$ and thereby only available to SYSDBA. That can of course be solved with a view and proper grants and synonyms. However, views on X$ can cause some issues during upgrades so such views should probably be dropped before upgrading.

While not documented, this is a neat way to gain access to a lot of information from the xml.log (alert-log).