11G externalized alert log table

Alert log can be monitored through SQL using "sys.x$dbgalertext".

SQL> desc sys.x$dbgalertext
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 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


SQL> col MESSAGE_TEXT for a120
SQL> select * from sys.x$dbgalertext where ORIGINATING_TIMESTAMP > sysdate -1/24
  2  /

no rows selected

SQL> create tablespace tbl_failed datafile '/root/not_able_to_create.dbf' size 10M;
create tablespace tbl_failed datafile '/root/not_able_to_create.dbf' size 10M
*
ERROR at line 1:
ORA-01119: error in creating database file '/root/not_able_to_create.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 13: Permission denied
Additional information: 1


SQL> select * from sys.x$dbgalertext where ORIGINATING_TIMESTAMP > sysdate -1/24;

ADDR               INDX    INST_ID
---------------- ---------- ----------
ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
NORMALIZED_TIMESTAMP
---------------------------------------------------------------------------
ORGANIZATION_ID
----------------------------------------------------------------
COMPONENT_ID
----------------------------------------------------------------
HOST_ID
----------------------------------------------------------------
HOST_ADDRESS                       MESSAGE_TYPE MESSAGE_LEVEL
---------------------------------------------- ------------ -------------
MESSAGE_ID
----------------------------------------------------------------
MESSAGE_GROUP
----------------------------------------------------------------
CLIENT_ID
----------------------------------------------------------------
MODULE_ID
----------------------------------------------------------------
PROCESS_ID
--------------------------------
THREAD_ID
----------------------------------------------------------------
USER_ID
----------------------------------------------------------------
INSTANCE_ID
----------------------------------------------------------------
DETAILED_LOCATION
--------------------------------------------------------------------------------
PROBLEM_KEY
----------------------------------------------------------------
UPSTREAM_COMP_ID
--------------------------------------------------------------------------------
DOWNSTREAM_COMP_ID
--------------------------------------------------------------------------------
EXECUTION_CONTEXT_ID
--------------------------------------------------------------------------------
EXECUTION_CONTEXT_SEQUENCE ERROR_INSTANCE_ID ERROR_INSTANCE_SEQUENCE    VERSION
-------------------------- ----------------- ----------------------- ----------
MESSAGE_TEXT
--------------------------------------------------------------------------------
MESSAGE_ARGUMENTS
--------------------------------------------------------------------------------
SUPPLEMENTAL_ATTRIBUTES
--------------------------------------------------------------------------------
SUPPLEMENTAL_DETAILS
--------------------------------------------------------------------------------
 PARTITION  RECORD_ID
---------- ----------
00007FAA553E5840       3110         1
06-MAY-12 11.42.14.357 PM +08:00

oracle
rdbms
ncsdb02.ncs.edu.sg
192.168.251.141                       5           16
opiexe:3025:4222364190
admin_ddl

sqlplus@ncsdb02.ncs.edu.sg (TNS V1-V3)
17422








             0           0               0          0
create tablespace tbl_failed datafile '/root/not_able_to_create.dbf' size 10M



     1     3111

00007FAA553E5840       3111         1
06-MAY-12 11.42.14.384 PM +08:00

oracle
rdbms
ncsdb02.ncs.edu.sg
192.168.251.141                       5           16
opiexe:3087:2780954927
admin_ddl

sqlplus@ncsdb02.ncs.edu.sg (TNS V1-V3)
17422








             0           0               0          0
ORA-1119 signalled during: create tablespace tbl_failed datafile '/root/not_able
_to_create.dbf' size 10M...



     1     3112