As of Oracle 11g, the contents of Oracle alert log can be queried directly via a fixed table X$DBGALERTEXT.

desc X$DBGALERTEXT
 ADDR                                               RAW(8)
INDX NUMBER
INST_ID NUMBER
ORIGINATING_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
NORMALIZED_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
ORGANIZATION_ID VARCHAR2(64)
COMPONENT_ID VARCHAR2(64)
HOST_ID VARCHAR2(64)
HOST_ADDRESS VARCHAR2(46)
MESSAGE_TYPE NUMBER
MESSAGE_LEVEL NUMBER
MESSAGE_ID VARCHAR2(64)
MESSAGE_GROUP VARCHAR2(64)
CLIENT_ID VARCHAR2(64)
MODULE_ID VARCHAR2(64)
PROCESS_ID VARCHAR2(32)
THREAD_ID VARCHAR2(64)
USER_ID VARCHAR2(64)
INSTANCE_ID VARCHAR2(64)
DETAILED_LOCATION VARCHAR2(160)
PROBLEM_KEY VARCHAR2(64)
UPSTREAM_COMP_ID VARCHAR2(100)
DOWNSTREAM_COMP_ID VARCHAR2(100)
EXECUTION_CONTEXT_ID VARCHAR2(100)
EXECUTION_CONTEXT_SEQUENCE NUMBER
ERROR_INSTANCE_ID NUMBER
ERROR_INSTANCE_SEQUENCE NUMBER
VERSION NUMBER
MESSAGE_TEXT VARCHAR2(2048)
MESSAGE_ARGUMENTS VARCHAR2(128)
SUPPLEMENTAL_ATTRIBUTES VARCHAR2(128)
SUPPLEMENTAL_DETAILS VARCHAR2(128)
PARTITION NUMBER
RECORD_ID NUMBER


The PROBLEM_KEY column gives the ORA- error code.

However, being a fixed table, it means that the table is only accessible by SYS, and not viewable in SYS's catalog.

A quick and dirty fix can be to create a V$ public synonym for the view, and grant SELECT to the view to whoever has to access it.

CREATE VIEW SYS.V_$ALERT_LOG
AS SELECT * FROM x$dbgalertext;

CREATE OR REPLACE PUBLIC SYNONYM V$ALERT_LOG FOR SYS.V_$ALERT_LOG;

GRANT SELECT ON SYS.V_$ALERT_LOG TO MYDBUSER;


You can then query the view for interesting details.

For example:

SELECT * FROM V$ALERT_LOG WHERE MESSAGE_LEVEL <> 16 ORDER BY ORIGINATING_TIMESTAMP DESC;


Happy computing

Drop me a line and hang out on the sidebar links if this note has been useful to you.

Nixman