ORACLE提供了2个有意思的内部表：
X$DBGALERTEXT
X$DIAG_ALERT_EXT
这2个表分别用来显示ALERT日志信息和监听器的日志信息。
以前只能用外部表实现的功能，现在已经没那么麻烦了。
SQL> select MESSAGE_TEXT from X$DBGALERTEXT where rownum<=20;
MESSAGE_TEXT
--------------------------------------------------------------------------------
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Cleared LOG_ARCHIVE_DEST_1 parameter default value
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile /u01/app/oracle/product/db11gr2/d
bs/spfilehuateng.ora
System parameters with non-default values:
processes = 150
event = "10246 trace name context forever,level 1"
shared_pool_size = 128M
_trace_events = "10000-10999:255:ALL"
nls_language = "SIMPLIFIED CHINESE"
nls_territory = "CHINA"
20 rows selected.
这个表里还有其他很多重要的列信息。
SQL> desc X$DBGALERTEXT
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
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> select message_text,filename from X$diag_alert_ext where rownum<=20;
MESSAGE_TEXT FILENAME
---------------------------------------- ----------------------------------------
14-AUG-2013 22:54:11 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 22:54:41 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 22:55:11 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 22:55:41 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 22:55:44 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 22:55:59 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 22:56:44 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 22:57:14 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 22:57:44 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 22:58:14 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 22:58:44 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 22:59:14 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 22:59:44 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 23:00:14 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 23:00:44 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 23:01:14 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 23:01:44 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 23:02:14 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 23:02:35 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
14-AUG-2013 23:02:44 * service_update * /u01/app/oracle/diag/tnslsnr/db2server/l
huateng * 0 istener/alert/log.xml
20 rows selected.
SQL> desc X$DIAG_ALERT_EXT
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
ADR_PATH_IDX VARCHAR2(445)
ADR_HOME VARCHAR2(445)
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(67)
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(131)
SUPPLEMENTAL_ATTRIBUTES VARCHAR2(131)
SUPPLEMENTAL_DETAILS VARCHAR2(131)
PARTITION NUMBER
RECORD_ID NUMBER
FILENAME VARCHAR2(515)
PROBLEM_KEY VARCHAR2(67)
VERSION NUMBER