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 will be available on generic Linux platforms in January and soon on AIX and Windows

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