Search Results dbms_index_utl




Overview
Explore in AI Chat

Overview

SYS.DBMS_INDEX_UTL is a server-side PL/SQL package owned by the SYS schema and shipped as part of the Oracle database kernel that underpins every Oracle E-Business Suite 12.1.1 and 12.2.2 environment. Its role is assisting the creation and rebuild of indexes at scale, particularly the partitioned and globally partitioned indexes that Oracle EBS relies on for the large transactional tables found in the Oracle Applications tablespaces. The package provides a programmatic alternative to issuing individual DDL statements, allowing DBAs and utilities to construct indexes across an entire schema or across a specific table in a controlled, resumable manner. The object is documented in ETRM with a status of VALID and is classified under the generic API category OTHER, reflecting that it is an internal database utility rather than an EBS Application Object Library (AOL) API.

Key Procedures and Functions

The documented interface of DBMS_INDEX_UTL consists of twelve callable units. The metadata records the following named entry points:

  • BUILD_SCHEMA_INDEXES — drives index creation or rebuild for all eligible indexes belonging to a given schema. It is the broadest scope offered by the package and is useful during post-clone index regeneration or mass reorganization.
  • BUILD_TABLE_INDEXES — performs the same operation limited to all indexes defined on a single table, allowing targeted maintenance on high-volume EBS tables without touching the remainder of the schema.
  • BUILD_INDEXES — the general entry point for building one or more indexes; the higher-level schema and table routines delegate to this logic.
  • BUILD_INDEX_COMPONENTS — handles construction of the individual components that make up a partitioned or composite index, supporting piecewise index building.
  • BUILD_TABLE_COMPONENT_INDEXES — restricts component-level index building to the indexes of a specified table.
  • MULTI_LEVEL_BUILD — coordinates builds across multiple levels of a partitioned index hierarchy, enabling large indexes to be created one partition or subpartition at a time.

Parameter lists are not reproduced here because the ETRM extract does not document them; exact signatures should be confirmed against the database before use.

Tables Accessed

The ETRM extract documents no tables referenced through APPS synonyms. This is consistent with the package's nature: DBMS_INDEX_UTL operates against data dictionary views and index segments rather than against application tables. Its documented dependency list is limited to SYS and the STANDARD package, confirming that it is a database-layer utility with no direct dependency on EBS application schema objects.

Usage Notes

DBMS_INDEX_UTL is referenced by two dependent objects: PUBLIC.DBMS_INDEX_UTL and SYS.DBMS_INDEX_UTL, and it is additionally referenced by DBMS_SNAPSHOT, meaning the materialized view subsystem relies on it when building and maintaining indexes on materialized views and their logs. Because the package is granted to PUBLIC, it can be invoked from any EBS schema, including APPS, without an explicit synonym. In practice it is called from ad hoc DBA scripts during index rebuild operations, during tablespace reorganization, or after a rapid clone when indexes must be rebuilt outside the standard AutoConfig and AD utilities. It is not exposed through an EBS form or concurrent program in the standard product; any use from concurrent processing or custom PL/SQL constitutes a custom extension. Because the procedures execute DDL, callers require the appropriate privileges on the underlying tables and sufficient space in the target tablespaces, and the operations should be scheduled during maintenance windows to avoid contention with online EBS users.