Search Results create_schema

Overview

SYS.DBMS_HM is the Oracle Health Monitor package, a server-side PL/SQL API introduced with the Automatic Diagnostic Repository (ADR) infrastructure in Oracle Database 11g and retained through the releases underpinning Oracle E-Business Suite 12.1.1 and 12.2.2. Its business function is diagnostic rather than transactional: it executes predefined health checks against the database instance, the Automatic Storage Management instance, and selected database components, then surfaces the findings as structured reports. In an EBS context the package is used to validate the integrity of the database tier that hosts the APPLSYS, APPS, and product schemas — for example, verifying redo log integrity, data block corruption, dictionary consistency, and interconnect or cluster health.

Although owned by SYS and classified in ETRM as OTHER, the package is exposed to the EBS application tier through the standard APPS synonym and grant model, which allows DBAs to invoke health checks without connecting as SYSDBA from an application session. Note that the metadata records zero referenced-by relationships: no shipped EBS product package depends on DBMS_HM, so its use is entirely operational.

Key Procedures and Functions

ETRM documents six entry points for this package:

• RUN_CHECK — Executes a named health check. Callers supply a check name, an optional run name to uniquely identify the execution, an optional timeout in seconds, and an optional text representation of input parameters. The run name is the handle used later to retrieve results.
• GET_RUN_REPORT — Returns the report for a completed run, rendered as TEXT, HTML, or XML and at either BASIC or DETAILED report level.
• CREATE_SCHEMA — Creates the repository schema that Health Monitor uses to persist check definitions and run results. This is the object associated with the search term create_schema; it is invoked during initial Health Monitor setup rather than during routine operation.
• DROP_SCHEMA — Removes the Health Monitor repository schema, typically used during cleanup or migration of the repository to a different tablespace or database.
• RUN_DDE_ACTION — Invokes a diagnostic action associated with the Diagnostic Data Extractor framework, allowing targeted capture of diagnostic data for a specified problem.
• CREATE_OFFLINE_DICTIONARY — Produces an offline copy of the data dictionary, used for consistency validation and forensic analysis when the live dictionary cannot be fully trusted or cannot be queried interactively.

Tables Accessed

The ETRM metadata for this object does not enumerate referenced tables and records no access through APPS synonyms. In practice, DBMS_HM reads the dynamic performance views underlying the health-check framework and writes run definitions and results into the Health Monitor repository tables created by CREATE_SCHEMA. Because that repository is a SYS-owned structure, it falls outside the standard EBS schema grant set and is not reversible through the usual APPS synonym conventions.

Usage Notes

Health Monitor is not wired into any shipped EBS form, concurrent program, or workflow. It is invoked from the database tier by a DBA using SQL*Plus or a similar tool, either directly through DBMS_HM or through the HM command-line interface in adrci. In EBS 12.1.1 and 12.2.2 environments the most common use cases are pre-upgrade database validation, investigation of ORA-01578 or ORA-00600 errors, and routine corruption checks ahead of an online patching cycle.

Two cautions apply. First, setup procedures such as CREATE_SCHEMA and DROP_SCHEMA must be run as a privileged user and should never be scheduled concurrently with a running patch or online patching session. Second, checks executed with RUN_CHECK consume instance resources and can hold internal locks; scheduling them during peak EBS transaction windows is inadvisable. Custom code in the EBS application tier should not call this package, since it resides in SYS and is not part of the supported EBS API surface.