Search Results get_id_range




Overview
Explore in AI Chat

Overview

AD_PARALLEL_UPDATES_PKG is a server-side PL/SQL package body owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its primary role is to coordinate the parallel execution of large data update operations against database tables, particularly those involved in Applications DBA (AD) administration activities such as patching, table reorganization, and maintenance tasks. Rather than executing an entire table update in a single serial transaction, the package partitions the target data into discrete rowid or ID ranges and assigns each range to a worker. This design allows long-running update operations to be split across multiple concurrent workers, which significantly reduces overall elapsed time while providing a mechanism to track which partitions have completed.

The package maintains its own bookkeeping entities, AD_PARALLEL_UPDATES, AD_PARALLEL_UPDATE_UNITS, and AD_PARALLEL_WORKERS, so that administrators can monitor progress, retry failed ranges, and safely purge completed work. Because the package relies on DBMS_LOCK, DBMS_ROWID, and DBMS_SYSTEM, it also enforces concurrency control and performs low-level rowid arithmetic to define unit boundaries.

Key Procedures and Functions

The documented API exposes eleven procedures and functions. Together they form a complete lifecycle for a parallel update job:

  • INITIALIZE_ROWID_RANGE — Divides a target table into logical ranges based on rowid values, registering the units that workers will later process.
  • INITIALIZE_ID_RANGE — Performs the analogous partitioning on the basis of a numeric ID column rather than a rowid.
  • PROCESSED_ROWID_RANGE — Marks a previously assigned rowid range as complete, updating the tracking tables.
  • PROCESSED_ID_RANGE — Marks an ID-based range as complete.
  • GET_ROWID_RANGE_WRAPPER — A wrapper routine that encapsulates the logic for retrieving the next available rowid range, shielding callers from the underlying query.
  • GET_ROWID_RANGE — Returns the next unprocessed rowid range to a requesting worker.
  • GET_ID_RANGE — Returns the next unprocessed ID range to a requesting worker.
  • PURGE_PROCESSED_UNITS — Removes unit records that have already been processed, preventing unbounded growth of the tracking tables.
  • DELETE_UPDATE_INFORMATION — Cleans up all update metadata for a completed or aborted parallel update job.
  • REINITIALIZE_AFTER_TABLE_REORG — Rebuilds range information after a table has been reorganized, ensuring stale rowid references are discarded.

Tables Accessed

The package reads and writes several APPS-owned tables through synonyms:

  • AD_PARALLEL_UPDATES — Top-level header records describing each parallel update job.
  • AD_PARALLEL_UPDATE_UNITS — Individual range units that workers claim and complete.
  • AD_PARALLEL_WORKERS — Registration and status of concurrent workers participating in a job.
  • AD_PARALLEL_UPDATES_S — Sequence used to generate identifiers for update jobs.

Supporting database objects include DBMS_LOCK for serializing critical sections, DBMS_ROWID for rowid range computations, DBMS_SYSTEM for session-level information, V$SESSION to observe active worker sessions, and dictionary views such as OBJ$, TAB$, and USER$ used to resolve and validate target objects. AD_EXTENTS is consulted to understand storage layout, which is relevant when defining ranges or handling reorgs.

Usage Notes

AD_PARALLEL_UPDATES_PKG is not intended as an end-user or developer-facing API. It is invoked internally by Oracle Applications DBA utilities, patching drivers, and administrative concurrent programs that must apply updates to very large tables. In 12.1.1 and 12.2.2 the package is referenced by 55 other database objects, confirming its role as a shared infrastructure component. Custom code should avoid calling these routines directly unless replicating the same parallel update model; a misconfigured range or missing purge/progress calls can leave orphaned unit records. Administrators monitoring parallel patch or maintenance jobs can inspect AD_PARALLEL_UPDATES and AD_PARALLEL_WORKERS to verify progress, and use DELETE_UPDATE_INFORMATION or PURGE_PROCESSED_UNITS to clean up after completion or failure.