How to Identify the Users of DBMS_LOCK (also known as UL) locks
(Doc ID 1913830.1)
Last updated on NOVEMBER 06, 2019
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Oracle's built-in DBMS_LOCK package allows the application developer to request, convert and release locks which are independent from the locks which are provided automatically by Oracle. These routines are meant for synchronization within the application and augment the locking which is already there in the database. They are not meant to replace the existing locking which Oracle provides.
Such user locks are obtained by calls to DBMS_LOCK.ALLOCATE_UNIQUE. This procedure takes a user-supplied lock name and optionally an expiration interval and returns a unique lock handle. It always does an implicit commit. Deadlock detection is performed on these locks, and these locks are automatically released when the session terminates. It is up to the clients to agree on the use of these locks.
When DBMS_LOCK.ALLOCATE_UNIQUE is used, a unique lock handle is returned (in the range of 1073741824 to 1999999999) and associated to the user-supplied lock name.
With user defined locking in place, locks may be allocated with DBMS_LOCK and are denoted in the database as UL locks (user-defined locks).
How can we monitor such locks which were obtained from DBMS_LOCK calls and determine who is using them?
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document