Alert log x$dbgalertext tips

Prior to Oracle 11g, the DBA had to define the Oracle alert log as an external table in order to query the alert log with native SQL.

• SQL against the alert log - You can define the alert log file as an external table and detect messages with SQL.
   
• Scripts - Write your own OS shell scripts to detect alert log messages.
   
• PL/SQL - You can write a SQL*Plus procedure using utl_file to read the alert log.
• In 11g and beyond, you can directly query the alert log with SQL, using x$dbgalertext.

Now in 11g, Oracle gives us an x$ fixed table that maps to the alert log, allowing for native SQL queries against the alert log.

We see that the x$dbgalertext appears as a table, a direct mapping that allows us to specify columns, just as-if the alert log flat file was an Oracle table.  Here are the columns in the x$dbgalertext fixed table:

SQL> desc X$DBGALERTEXT

           Name                            Null?    Type

           ------------------------------- -------- -------------------------

    1      ADDR                                     RAW(4)

    2      INDX                                     NUMBER

    3      INST_ID                                  NUMBER

    4      ORIGINATING_TIMESTAMP                    TIMESTAMP(3) WITH TIME ZONE

    5      NORMALIZED_TIMESTAMP                     TIMESTAMP(3) WITH TIME ZONE

    6      ORGANIZATION_ID                          VARCHAR2(64)

    7      COMPONENT_ID                             VARCHAR2(64)

    8      HOST_ID                                  VARCHAR2(64)

    9      HOST_ADDRESS                             VARCHAR2(16)

   10      MESSAGE_TYPE                             NUMBER

   11      MESSAGE_LEVEL                            NUMBER

   12      MESSAGE_ID                               VARCHAR2(64)

   13      MESSAGE_GROUP                            VARCHAR2(64)

   14      CLIENT_ID                                VARCHAR2(64)

   15      MODULE_ID                                VARCHAR2(64)

   16      PROCESS_ID                               VARCHAR2(32)

   17      THREAD_ID                                VARCHAR2(64)

   18      USER_ID                                  VARCHAR2(64)

   19      INSTANCE_ID                              VARCHAR2(64)

   20      DETAILED_LOCATION                        VARCHAR2(160)

   21      PROBLEM_KEY                              VARCHAR2(64)

   22      UPSTREAM_COMP_ID                         VARCHAR2(100)

   23      DOWNSTREAM_COMP_ID                       VARCHAR2(100)

   24      EXECUTION_CONTEXT_ID                     VARCHAR2(100)

   25      EXECUTION_CONTEXT_SEQUENCE               NUMBER

   26      ERROR_INSTANCE_ID                        NUMBER

   27      ERROR_INSTANCE_SEQUENCE                  NUMBER

   28      VERSION                                  NUMBER

   29      MESSAGE_TEXT                             VARCHAR2(2048)

   30      MESSAGE_ARGUMENTS                        VARCHAR2(128)

   31      SUPPLEMENTAL_ATTRIBUTES                  VARCHAR2(128)

   32      SUPPLEMENTAL_DETAILS                     VARCHAR2(128)

   33      PARTITION                                NUMBER

   34      RECORD_ID                                NUMBER

For more 11g new features, see the great book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain and Brian Carr.

---