8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

Read the Alert Log From SQL (X$DBGALERTEXT and V$DIAG_ALERT_EXT)

This article describes how to read the database alert log from SQL using the X$DBGALERTEXT table, introduced in Oracle 11g.

Related articles.

The X$DBGALERTEXT Table

Prior to 11g, if we needed access to the alert log from SQL, we would create an external table to read it. From 11g onward we have the X$DBGALERTEXT table.

The X$DBGALERTEXT table presents the XML version of the alert log from the Automatic Diagnostic Repository (ADR) as a table, which we can query from SQL. The table structure looks like this.

SQL> desc x$dbgalertext
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CON_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(128)
 INSTANCE_ID                                        VARCHAR2(64)
 DETAILED_LOCATION                                  VARCHAR2(160)
 PROBLEM_KEY                                        VARCHAR2(550)
 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(512)
 SUPPLEMENTAL_ATTRIBUTES                            VARCHAR2(512)
 SUPPLEMENTAL_DETAILS                               VARCHAR2(4000)
 PARTITION                                          NUMBER
 RECORD_ID                                          NUMBER
 CON_UID                                            NUMBER
 CONTAINER_NAME                                     VARCHAR2(64)
 ATTENTION_ID                                       NUMBER
 ID_SUFFIX                                          VARCHAR2(64)
 OPERATION_ID                                       VARCHAR2(64)
 CAUSE_TEXT                                         VARCHAR2(2048)
 ACTION_TEXT                                        VARCHAR2(2048)
 ORACLE_PROCESS_ID                                  NUMBER
 DATABASE_ID                                        VARCHAR2(30)
 SQL_ID                                             VARCHAR2(13)
 SESSION_ID                                         VARCHAR2(16)
 IMPACT_ID                                          VARCHAR2(32)
 IMPACT_SCOPE                                       VARCHAR2(32)
 TARGET_USER                                        VARCHAR2(32)
 CALL_STACK                                         VARCHAR2(1024)
 FLAGS                                              NUMBER
 URGENCY                                            VARCHAR2(16)
 START_VERSION                                      VARCHAR2(16)

SQL>

We can query it like any other table. The following query displays critical events.

select message_text
from   x$dbgalertext
where  message_level = 1;

Of course, you can filter based on any of the column to looks for specific messages that you are interested it.

The V$DIAG_ALERT_EXT View (12.2 Update)

From Oracle 12.2 onward we can use the V$DIAG_ALERT_EXT view to access the XML formatted alert log from the Automatic Diagnostic Repository (ADR). It presents most of the columns from the X$DBGALERTEXT table.

SQL> desc v$diag_alert_ext
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORIGINATING_TIMESTAMP                              TIMESTAMP(9) WITH TIME ZONE
 NORMALIZED_TIMESTAMP                               TIMESTAMP(9) WITH TIME ZONE
 ORGANIZATION_ID                                    VARCHAR2(67)
 COMPONENT_ID                                       VARCHAR2(67)
 HOST_ID                                            VARCHAR2(67)
 HOST_ADDRESS                                       VARCHAR2(49)
 MESSAGE_TYPE                                       NUMBER
 MESSAGE_LEVEL                                      NUMBER
 MESSAGE_ID                                         VARCHAR2(67)
 MESSAGE_GROUP                                      VARCHAR2(67)
 CLIENT_ID                                          VARCHAR2(67)
 MODULE_ID                                          VARCHAR2(67)
 PROCESS_ID                                         VARCHAR2(35)
 THREAD_ID                                          VARCHAR2(67)
 USER_ID                                            VARCHAR2(131)
 INSTANCE_ID                                        VARCHAR2(67)
 DETAILED_LOCATION                                  VARCHAR2(163)
 UPSTREAM_COMP_ID                                   VARCHAR2(103)
 DOWNSTREAM_COMP_ID                                 VARCHAR2(103)
 EXECUTION_CONTEXT_ID                               VARCHAR2(103)
 EXECUTION_CONTEXT_SEQUENCE                         NUMBER
 ERROR_INSTANCE_ID                                  NUMBER
 ERROR_INSTANCE_SEQUENCE                            NUMBER
 MESSAGE_TEXT                                       VARCHAR2(2051)
 MESSAGE_ARGUMENTS                                  VARCHAR2(515)
 SUPPLEMENTAL_ATTRIBUTES                            VARCHAR2(515)
 SUPPLEMENTAL_DETAILS                               VARCHAR2(515)
 PARTITION                                          NUMBER
 RECORD_ID                                          NUMBER
 FILENAME                                           VARCHAR2(515)
 LOG_NAME                                           VARCHAR2(67)
 PROBLEM_KEY                                        VARCHAR2(553)
 VERSION                                            NUMBER
 CON_UID                                            NUMBER
 CONTAINER_ID                                       NUMBER
 CONTAINER_NAME                                     VARCHAR2(67)
 CON_ID                                             NUMBER

SQL>

We can query the information like any other view. The following example returns the messages of a critical nature.

-- MESSAGE_LEVEL - 1: CRITICAL
select message_text
from   v$diag_alert_ext 
where  message_level = 1;

-- MESSAGE_TYPE - 2: INCIDENT_ERROR, 3: ERROR
select message_type, message_level, message_text
from   v$diag_alert_ext
where  message_type in (2, 3);

The V$ALERT_LOG View

Prior to the introduction of the V$DIAG_ALERT_EXT view in Oracle 12.2, people would often use create their own version of a similar view like this.

create or replace view sys.v_$alert_log as select * from x$dbgalertext;
create public synonym v$alert_log for sys.v_$alert_log;

We can now grant access to this view.

grant select on v$alert_log to testuser1;

Creating your own objects in the SYS schema is considered bad practice.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.