oracle11g清空alert_log,oracle11g:通过sql语句读取alert log 信息

在11g中，oracle对数据库的诊断结果进行了一些改进，下面将讨论通过sql语句获取警告日志信息：

在oracle11g以前，如果需要通过sql语句来读取警告日志，一般通过外部表或者使用utl_file管道函数方式。在读取文本信息之后需要解析然后导出自己需要的信息。

从11g开始，oracle已经替你做了这些工作。它提供了一个固态表x$dbgalertext，当你查询的时候oracle从alert目录读取log.xml(包含警告日志中的所有信息)，解析之，然后按行返回给用户：

SQL> set pagesize 9999

SQL> select message_text from x$dbgalertext where rownum<=20;

MESSAGE_TEXT

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

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Shared memory segment for instance monitoring created

Picked latch-free SCN scheme 2

Using LOG_ARCHIVE_DEST_1 parameter default value as D:\app\hongsy\product\11.1.0\db_1\RDBMS

Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =18

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up ORACLE RDBMS Version: 11.1.0.6.0.

Using parameter settings in client-side pfile D:\APP\HONGSY\ADMIN\ORCL\PFILE\INIT.ORA on machine HH

System parameters with non-default values:

processes                = 150

nls_language             = "SIMPLIFIED CHINESE"

nls_territory            = "CHINA"

sga_target               = 460M

control_files            = "D:\APP\HONGSY\ORCL\CONTROL01.CTL"

20 rows selected.

以下是x$dbgalertext的字段信息：

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(16)

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

这里同样有个固态表X$DBGDIREXT,它包含diag目录下所有文件和目录信息：

SQL> select lpad(' ',lvl,' ')||logical_file file_name

2  from X$DBGDIREXT

3  where rownum <=20;

FILE_NAME

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

asm

clients

user_hongsy

host_2986972688_11

alert

log.xml

cdump

incident

incpkg

lck

am_1096102193_3488045378.lck

am_1096102262_3454819329.lck

am_3216668543_3129272988.lck

metadata

adr_control.ams

inc_meter_impt_def.ams

inc_meter_pk_impts.ams

stage

sweep

trace

20 rows selected.

如果你自己构建了一些监控警告日志的程序，那么从11g开始，这些X$表将会帮助你。这对没有操作系统用户权限时，可以通过数据库权限来查阅。但是如果数据库实例没有运行的话，将不能使用此方式来查看警告日志信息了。