oracle11g:hm：健康监控器(Health Monitor)

Oracle 11g中引入了免费的数据库健康监控器，通过dbms_hm程序包来完成。健康监控器可以手动运行并检查下列项目：
DB结构完整性检查
数据块完整性检查
重做日志完整性检查
UNDO段完整性检查
事务完整性检查
数据字典完整性检查
使用dbms_hm程序包执行健康检查：
SQL> exec dbms_hm.run_check ('Dictionary Integrity Check', 'HM_TEST') ;
PL/SQL procedure successfully completed.
使用dbms_hm程序包的get_run_report函数可以获取健康检查报告，使用PL/SQL函数通过返回CLOB对象来提取报告。下面这个例子演示了如何通过SQL Plus，使用PL/SQL脚本提取报告到文件中。
执行生成报告的例子：
 

$ sqlplus sysorcl11g as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Oct 2 09:08:55 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
select DBMS_HM.GET_RUN_REPORT('HM_TEST')
from dual;
下面是一个报告的例子：
Basic Run Information
Run Name                   : HM_TEST
Run Id                     : 966
Check Name                 : Dictionary Integrity Check
Mode                       : MANUAL
Status                     : COMPLETED
Start Time                 : 2008-10-02 08:59:50.734000 -04:00
End Time                   : 2008-10-02 08:59:57.296000 -04:00
Error Encountered           : 0
Source Incident Id        : 0
Number of Incidents Created   : 0
Input Parameters for the Run
TABLE_NAME=ALL_CORE_TABLES
CHECK_MASK=ALL
Run Findings And Recommendations
Finding
Finding Name    : Dictionary Inconsistency
Finding ID      : 967
Type           : FAILURE
Status          : OPEN
Priority        : CRITICAL
Message         : SQL dictionary health check: dependency$.dobj# fk 126 on
object DEPENDENCY$ failed
Message         : Damaged rowid is AAAABnAABAAAO2GAB3 - description: No further
damage description available
Finding
Finding Name    : Dictionary Inconsistency
Finding ID      : 970
Type           : FAILURE
Status          : OPEN
Priority        : CRITICAL
Message         : SQL dictionary health check: dependency$.dobj# fk 126 on
object DEPENDENCY$ failed
Message         : Damaged rowid is AAAABnAABAAAQtpABQ - description: No further
damage description available
下面是一个脚本的例子：
-------------------------------------------------------------------------------
-- Script: run_health_check_report.sql
-------------------------------------------------------------------------------
-- Create a directory where we are going to write out report file to
create directory healthcheck as '&u_name';
-- Get Database Instance Name to build file name to be used to put report into
column database_name noprint new_value i_name
SELECT UPPER(name) || '_healthcheck_report.lst' database_name
FROM v$database ;
declare
v_rpt
 CLOB ;
buffer
 VARCHAR2(32767);
buffer_size
 CONSTANT BINARY_INTEGER := 32767;
amount
 BINARY_INTEGER;
offset
 NUMBER(38);
file_handle
 UTL_FILE.FILE_TYPE;
directory_name
 CONSTANT VARCHAR2(80) := 'HEALTHCHECK';
v_filename
 CONSTANT VARCHAR2(80) := '&i_name';
begin
-- Run the Report
v_rpt := dbms_hm.GET_RUN_REPORT ('&HNAME') ;
-- OPEN NEW FILE IN WRITE MODE
file_handle := UTL_FILE.FOPEN(
location
 => directory_name,
filename
 => v_filename,
open_mode
 => 'w',
max_linesize => buffer_size);
amount := buffer_size;
offset := 1;
WHILE amount >= buffer_size
LOOP
DBMS_LOB.READ(
lob_loc
 => v_rpt,
amount
 => amount,
offset
 => offset,
buffer
 => buffer);
offset := offset + amount;
UTL_FILE.PUT(file => file_handle, buffer
 => buffer);
UTL_FILE.FFLUSH(file => file_handle);
END LOOP;
UTL_FILE.FCLOSE(file => file_handle);
END;
/