hcheck.sql - Script to Check Data Dictionary for Known Problems (Doc ID 136697.1)

Last updated on NOVEMBER 19, 2024

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

Purpose

To provide a single anonymous PL/SQL block to check for common Data Dictionary problems.
The script checks consistency of selected dictionary relationships and looks for certain known issues - some reported "problems" will be normal and expected.
The script can be used with Oracle Release 9i onwards. hcheck8i.sql includes 8.1.
It is a lightweight "read-only" script with no impact.
The script is for use mainly under the guidance of Oracle Support.

Current Versions

For Oracle 10.2.0.5 onwards, use version 04Aug23 in file hcheck.sql
For Oracle 9i to 10.2.0.4, use version 07MAY18 in file hcheck_10204.sql
For Oracle 8i create package hOut and use version 2.02 in hcheck8i.sql
Starting on Oracle Database Jan 2024 Release Update (DB RU) 19.22.0.0.240116, use PLSQL DBMS_DICTIONARY_CHECK

Note: Although this script has been tested internally by Oracle Support and Development, it should be run (and resolve any issues) in a development or test environment before running it in production.