Search Results dbms_application_info




Overview
Explore in AI Chat

Overview

SYS.DBMS_APPLICATION_INFO is an Oracle-supplied PL/SQL package that provides a standard interface for registering application context information with the database instance. It is not an EBS-specific package; rather, it is a core database utility that Oracle E-Business Suite depends upon heavily. Its principal role is to populate the V$SESSION and V$SESSION_LONGOPS dynamic performance views with meaningful, human-readable identifiers that describe what a given database session is doing at any moment.

Within Oracle EBS 12.1.1 and 12.2.2, this package allows administrators and developers to trace activity back to a specific responsibility, concurrent program, form, or module. Because EBS sessions frequently number in the thousands, the ability to label each session with its application-level purpose is essential for performance tuning, diagnosing locking and blocking, and monitoring long-running requests. The ETRM metadata confirms that DBMS_APPLICATION_INFO is owned by SYS, holds VALID status, and exposes six documented program units.

Key Procedures and Functions

  • SET_MODULE — Registers the name of the module currently executing in the session, typically the concurrent program or form name. This value surfaces in V$SESSION.MODULE.
  • SET_ACTION — Records the current action or operation within the module, providing finer-grained context than the module alone.
  • READ_MODULE — Retrieves the module and action values previously set for the session, allowing callers to save or inspect the current context.
  • SET_CLIENT_INFO — Stores client-level information, such as the user name, responsibility, or terminal identifier, in V$SESSION.CLIENT_INFO.
  • READ_CLIENT_INFO — Returns the client information value currently registered for the session.
  • SET_SESSION_LONGOPS — Populates the V$SESSION_LONGOPS view so that long-running operations can be tracked with estimated completion percentages and timing details.

Tables Accessed

The ETRM documentation for this object does not list any application tables accessed via APPS synonyms. This is consistent with the package's design: DBMS_APPLICATION_INFO does not manipulate business data. Instead, it writes to internal fixed views and the underlying X$ tables that populate V$SESSION and V$SESSION_LONGOPS. The metadata instead documents dependencies on STANDARD (the base PL/SQL package) within the SYS schema, confirming that the package relies on core kernel infrastructure rather than application-level schema objects.

Usage Notes

In Oracle EBS environments, DBMS_APPLICATION_INFO is typically invoked at the start of a unit of work. Concurrent program wrappers, for instance, call SET_MODULE to advertise the program short name and SET_ACTION to indicate the current phase. Oracle Forms sessions register the form and block context, enabling DBAs querying V$SESSION to rapidly identify the source of contention. The metadata records that the package is referenced by five other packages, including DBMS_BACKUP_RESTORE, DBMS_STATS, KUPM$MCP, KUPV$FT, and KUPW$WORKER — the latter being core components of Oracle Data Pump. This demonstrates that both standard database utilities and EBS-specific infrastructure (such as the KUP% Data Pump worker packages) inherit and extend the session instrumentation capability.

Custom code following EBS development standards should invoke DBMS_APPLICATION_INFO at the beginning of long-running procedures, call SET_SESSION_LONGOPS for operations expected to exceed a few seconds, and use SET_CLIENT_INFO to record the EBS user and responsibility. Because the package is PUBLIC and owned by SYS, no explicit grants are required for standard usage, though execution privileges should be confirmed in tightly secured environments.