Search Results drop_action

Overview

SYS.DBMS_REPAIR is the Oracle-supplied database package that provides block-level and segment-level data corruption detection and repair utilities. Within the context of Oracle E-Business Suite 12.1.1 and 12.2.2, the package is not an application-facing API and is not referenced by any registered EBS product code. It is a SYS-owned kernel utility that DBAs invoke manually when physical or logical block corruption is detected in an EBS tablespace — for example, in the APPLSYSPUB, APPS_TS_TX_DATA, or SYSTEM tablespaces that underpin the EBS schema. Execution privilege on the package is not granted to other users, which reinforces its designation as a privileged maintenance tool rather than a component of the application's functional flow.

The package exposes enumeration constants — TABLE_OBJECT, INDEX_OBJECT, CLUSTER_OBJECT — along with supporting constants such as SKIP_FLAG, NOSKIP_FLAG, and the admin action set CREATE_ACTION, PURGE_ACTION, and DROP_ACTION. These constants classify the objects and operations that the repair routines act upon.

Key Procedures and Functions

The documented procedures span the full corruption-handling lifecycle:

• ADMIN_TABLES — Creates, purges, or drops the administrative tables required before repair operations can proceed. It accepts an admin action such as CREATE_ACTION to build the repair or orphan repository tables in a chosen tablespace.
• CHECK_OBJECT — Scans a named schema object for corruption and populates the repair/orphan tables with the results of the check level selected.
• FIX_CORRUPT_BLOCKS — Applies the repair directives recorded in the repair table to the affected segments, typically used after corruption has been confirmed and a recovery strategy is in place.
• SKIP_CORRUPT_BLOCKS — Marks segments so that Oracle bypasses known corrupt blocks during full table scans, enabling continued access to intact data.
• SEGMENT_FIX_STATUS — Reports or updates the repair status of a segment, complementing SKIP_CORRUPT_BLOCKS by exposing fixability metadata.
• DUMP_ORPHAN_KEYS — Extracts index keys that have no corresponding table row, supporting orphan key analysis.
• REBUILD_FREELISTS — Reconstructs segment free lists as part of post-repair cleanup.
• REBUILD_SHC_INDEX — Rebuilds an index associated with skip-corrupt handling.
• ONLINE_INDEX_CLEAN — Performs online index cleanup to reconcile structure after repair.
• REPAIR_CLUSTER_INDEX_KEYCOUNT — Corrects the key count metadata for cluster index keys.

Tables Accessed

The ETRM metadata records no APPS synonym tables referenced by DBMS_REPAIR. Functionally, the package reads and writes its own administrative repository tables — historically DBMS_REPAIR.REPAIR_TABLE and DBMS_REPAIR.ORPHAN_KEY_TABLE — which are created and managed through ADMIN_TABLES. When CKPT or a user runs ADMIN_TABLES with CREATE_ACTION, one of these tables is materialized. CHECK_OBJECT writes findings into these tables, FIX_CORRUPT_BLOCKS consumes them, and DUMP_ORPHAN_KEYS reads orphan key entries. These tables are internal to the SYS-owned DBMS_REPAIR schema.

Usage Notes

In EBS environments, SYS.DBMS_REPAIR is not invoked from Forms, Oracle Application Object Library, or standard concurrent programs. The "create_action" term that surfaces in administrator searches corresponds to the CREATE_ACTION constant supplied to ADMIN_TABLES when preparing the repair repository. Typical invocation is by a DBA through SQL*Plus or a privileged scripting session, commonly after alert log entries or RMAN reports indicate block corruption. Because execution privilege is restricted to SYS, custom code deployed under APPS cannot call the package directly and must run through a privileged session. Repair operations should be performed with the affected segments quiesced, a verified backup available, and tablespace monitoring in place.