Quering alert log using SQL query
Oracle 11g開始，提供了X$DBGALERTEXT，可以用來使用sql訪問資料庫alert日誌，在不方便直接訪問伺服器時，提供了另外的方式訪問資料庫alert日誌。X$DBGALERTEXT的定義如下：
SYS> desc 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如下(可以得到類似直接訪問文字格式alert日誌樣式的結果)：
SQL> select ORIGINATING_TIMESTAMP, MESSAGE_TEXT from X$DBGALERTEXT;
Finding Trace File Path using SQL
11g開始，Oracle提供了X$DBGDIREXT介面，可以用來檢視diagnostic_dest下的目錄和檔案。X$DBGDIREXT的定義如下：
SYS> desc X$DBGDIREXT;
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
PHYSICAL_PATH VARCHAR2(444)
LOGICAL_PATH VARCHAR2(444)
PHYSICAL_FILE VARCHAR2(68)
LOGICAL_FILE VARCHAR2(68)
CREATION_TIME TIMESTAMP(3) WITH TIME ZONE
MODIFY_TIME TIMESTAMP(3) WITH TIME ZONE
LVL NUMBER
TYPE NUMBER
一個非常handy的用法：有時候不熟悉Oracle的人，我們需要它幫忙把alert日誌取出來的時候，由於11g的alert是放在diagnostic中的，描述日誌的路徑每次都要費很大的勁，現在我們可以用下面sql來直接獲取到alert日誌的路徑了：
SELECT PHYSICAL_PATH || CHR(47) || PHYSICAL_FILE
FROM X$DBGDIREXT
WHERE 1 = 1
AND PHYSICAL_FILE LIKE `alert_%`
AND PHYSICAL_PATH LIKE `%rdbms%`
/