Oracle | DBMS

Oracle DBMS_HM Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

Purpose

Performs health checks on the following data dictionary tables.

ACCESS$	ICOL$	SUPEROBJ$
BOOTSTRAP$	ICOLDEP$	SYN$
CCOL$	IND$	SYSAUTH$
CDEF$	LOB$	TAB$
CLU$	NTAB$	TS$
COL$	OBJ$	TSQ$
COLTYPE$	OBJAUTH$	TYPED_VIEW$
CON$	OBJPRIV$	UET$
DEFROLE$	OPQTYPE$	UGROUP$
DEPENDENCY$	REFCON$	UNDO$
DUAL$	SEG$	USER$
ECOL$	SEQ$	VIEW$
FET$	SUBCOLTYPE$	VIEWCON$
FILE$

AUTHID DEFINER

Dependencies

Only Standard

Documented Partially (2 of 6 subprograms are documented): Packages and Types Reference

Exceptions

Error Code	Reason
ORA-00111	internal_error

First Available 11.1

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, READ_ONLY);

Security Model Owned by SYS with EXECUTE granted to the DBA role.

Source {ORACLE_HOME}/rdbms/admin/dbmshm.sql

Subprograms

CREATE_OFFLINE_DICTIONARY	DROP_SCHEMA	RUN_CHECK
CREATE_SCHEMA	GET_RUN_REPORT	RUN_DDE_ACTION

CREATE_OFFLINE_DICTIONARY

Creates LogMiner offline dictionary in ADR dbms_hm.create_offline_dictionary;

exec dbms_hm.create_offline_dictionary;

-- note that the directory {ORACLE_BASE}/diag/rdbms/<database_name>/<instance_name>/hm on a lab server
-- the directory created is /app/oracle/diag/rdbms/orabase/orabase/hm.

CREATE_SCHEMA

Creates HM Schema in ADR dbms_hm.create_schema;

exec dbms_hm.create_schema;

DROP_SCHEMA

Drops HM Schema in ADR dbms_hm.drop_schema(force IN BOOLEAN := FALSE);

exec dbms_hm.drop_schema(TRUE);

GET_RUN_REPORT

Returns the report for the specified checker run dbms_hm.get_run_report( run_name IN VARCHAR2, report_type IN VARCHAR2 := 'TEXT', -- 'XML', 'TEXT', 'HTML' report_level IN VARCHAR2 := 'BASIC') -- 'BASIC', 'DETAIL' RETURN CLOB;

desc gv$hm_run

      

      SELECT name
      FROM gv$hm_run;

      

      set long 1000000

      

      spool c:\temp\uwhm.xml

      

      SELECT dbms_hm.get_run_report('HM_RUN_1', 'XML', 'DETAIL');

      

      spool off

RUN_CHECK

Runs the specified checker with the given arguments. The run's report will be maintained persistently in database.

And a big thank you to Earl Apex for a correction.

dbms_hm.run_check(

      check_name   IN VARCHAR2,

      run_name     IN VARCHAR2 := NULL,

      timeout      IN NUMBER   := NULL,

      input_params IN VARCHAR2 := NULL);

      

      
        Checkers
        ASM Allocation Check
        CF Block Integrity Check
        DB Structure Integrity Check
        Data Block Integrity Check
        Dictionary Integrity Check
        Redo Integrity Check
        Transaction Integrity Check
        Undo Segment Integrity Check
      
      

      --
      Check names and their parameters can be accessed from the gv$hm_check and

      -- gv$hm_check_param. Users can run all checks not internal in nature for example:

      

      
        
          SELECT name

          FROM gv$hm_check

          WHERE internal_check = 'N';
        
      
      

      -- which retrieves the list of checks that can be run manually by users.

SELECT name FROM gv$hm_check;

      

      SELECT name FROM gv$hm_check

      WHERE internal_check = 'N';

      

      SELECT run_id
      FROM gv$hm_run;

      

      exec dbms_hm.run_check('Dictionary Integrity Check', 'Run 1', 60);

      

      col name format a12

      

      SELECT run_id, name, check_name, run_mode, status, src_incident, num_incident, error_number

      FROM gv$hm_run

      ORDER  BY 1;

      

      set linesize 121

      col description format a40

      col damage_description format a40

      

      SELECT finding_id, status, type, description, damage_description

      FROM gv$hm_finding

      WHERE  run_id = 221;

      

      col name format a10

      col repair_script format a60

      

      SELECT name, type, rank, status, repair_script

      FROM gv_$hm_recommendation

      WHERE  run_id = 21

      AND fdg_id = 22;

RUN_DDE_ACTION

Runs a DDE (user) action for HM checks ... appears to be intended solely for internal use dbms_hm.run_dde_action( incident_id IN NUMBER, directory_name IN VARCHAR2, check_name IN VARCHAR2, run_name IN VARCHAR2, timeout IN NUMBER, params IN VARCHAR2) RETURN BOOLEAN;

col check_name format a35

      

      SELECT run_id, num_incident, check_name, name, timeout

      FROM gv$hm_run

      WHERE start_time > SYSTIMESTAMP-95

      ORDER BY 1;

      

      DECLARE

       retVal BOOLEAN;

      BEGIN

        IF dbms_hm.run_dde_action(1, 'CTEMP', 'DB Structure Integrity Check', 'UWRUN', 10, NULL) THEN

          dbms_output.put_line('T');

        ELSE

          dbms_output.put_line('F');

        END IF;

      END;

      /

      T

      

      PL/SQL procedure successfully completed.

Related Queries

DBMS_HM Related Queries set linesize 125 col check_name format a30 col parameter_name format a18 col type format a15 col default_value format a15 col description format a40 SELECT c.name check_name, p.name parameter_name, p.type, p.default_value, p.description FROM v$hm_check_param p, v$hm_check c WHERE p.check_id = c.id and c.internal_check = 'N' ORDER BY c.name; CHECK_NAME PARAMETER_NAME TYPE DEFAULT_VALUE DESCRIPTION ------------------------ -------------- --------------- --------------- ------------------ ASM Allocation Check ASM_DISK_GRP_NAME DBKH_PARAM_TEXT ASM group name CF Block Integrity Check CF_BL_NUM DBKH_PARAM_UB4 Control file block number Data Block Integrity Check BLC_DF_NUM DBKH_PARAM_UB4 File number Data Block Integrity Check BLC_BL_NUM DBKH_PARAM_UB4 Block number Dictionary Integrity Check CHECK_MASK DBKH_PARAM_TEXT ALL Check mask Dictionary Integrity Check TABLE_NAME DBKH_PARAM_TEXT ALL_CORE_TABLES Table name Redo Integrity Check SCN_TEXT DBKH_PARAM_TEXT 0 SCN of the latest good redo (if known) Transaction Integrity Check TXN_ID DBKH_PARAM_TEXT Transaction ID Undo Segment Integrity Check USN_NUMBER DBKH_PARAM_TEXT Undo segment number