Search Results dbms_lock_allocated

Overview

SYS.DBMS_LOCK is the standard Oracle-supplied package body that provides an application-visible interface to the Oracle Lock Management services built into the database kernel. In Oracle E-Business Suite 12.1.1 and 12.2.2, the package functions as a low-level serialization primitive rather than a business API. It allows a session to define its own named locks, request them in one of several modes, convert a held lock from one mode to another, release it explicitly, or suspend execution for a specified interval. Because the lock manager is implemented below the SQL layer, these locks are held independently of transactions and are not subject to commit or rollback, which makes the package suitable for coordinating concurrent work that cannot be protected by ordinary row locking.

Within the EBS technical stack the object resides in the SYS schema with a status of VALID and is classified by ETRM as OTHER. Although it belongs to the database rather than to the APPS schema, it is exposed to application code through the standard public synonym and execute grant. The ETRM dependency record confirms that the body is self-contained: its internal references are limited to SYS-owned objects, including DBMS_LOCK itself, DBMS_LOCK_ALLOCATED, DBMS_LOCK_ID_V2, DBMS_SYS_ERROR, DUAL, and STANDARD. It is not referenced by any other database object, but it is itself referenced by 55 other packages, which illustrates its role as a shared dependency across the EBS code base.

Key Procedures and Functions

ETRM documents nine procedures and functions in the package. These include:

• ALLOCATE_UNIQUE — Obtains a unique lock identifier for a user-supplied lock name, storing the association so that independent sessions can arrive at the same numeric handle.
• ALLOCATE_UNIQUE_AUTONOMOUS — Performs the same name-to-identifier allocation using an autonomous transaction, so the mapping is committed regardless of the caller's transaction outcome.
• REQUEST — Requests a lock on a specified identifier in a chosen mode, optionally waiting for a defined period before returning a timeout status.
• CONVERT — Changes an already-held lock from its current mode to a different mode, either immediately or after a bounded wait.
• RELEASE — Releases a lock previously obtained by the session, returning it to the pool.
• SLEEP — Suspends the calling session for a specified number of seconds.

The remaining documented routines cover supporting lock-management operations such as querying lock state and internal allocation bookkeeping. Because the package is owned by SYS, execute privileges are typically restricted to DBAs and trusted application schemas rather than granted broadly.

Tables Accessed

The package body does not read or write application tables. Its documented dependencies are internal dictionary structures and system views used to persist name-to-identifier mappings and to report lock status, notably DBMS_LOCK_ALLOCATED and DBMS_LOCK_ID_V2. These objects are owned by SYS and are not exposed through APPS synonyms, so EBS reports and forms do not query them directly.

Usage Notes

DBMS_LOCK is most often invoked from custom PL/SQL, concurrent program logic, and interface or conversion routines that must ensure only one session performs a given activity at a time. Typical patterns include serializing a batch job, guarding a shared temporary resource, or pacing a process with SLEEP. In Oracle 12.1.1 and 12.2.2 the familiar CALL-style invocation remains available, while later database releases add the DBMS_SESSION.SLEEP alternative. Because locks are named and session-owned, code must always pair REQUEST or CONVERT with RELEASE, and developers should note that the package requires explicit EXECUTE grants from SYS. The heavy reference count of 55 dependent packages confirms that DBMS_LOCK is a foundational dependency for many EBS components.