You can request a lock of a specific mode, give it a unique name recognizable in another procedure in the same or another instance, change the lock mode, and release it. DBMS_LOCK is most efficient with a limit of a few hundred locks per session. Oracle strongly recommends that you develop a standard convention for using these locks in order to avoid conflicts among procedures trying to use the same locks. For example, include your company name as part of your lock names.

There might be operating system-specific limits on the maximum number of total locks available. This must be considered when using locks or making this package available to other users. Consider granting the EXECUTE privilege only to specific users or roles.DBMS_LOCK package contains two procedures and three functions.

DBMS_LOCK.ALLOCATE_UNIQUE

This procedure allocates a unique lock identifier (in the range of 1073741824 to 1999999999) given a lock name. Lock identifiers are used to enable applications to coordinate their use of locks. This is provided because it may be easier for applications to coordinate their use of locks based on lock names rather than lock numbers.

• Syntax: DBMS_LOCK.ALLOCATE_UNIQUE ( lockname IN  VARCHAR2, lockhandle       OUT VARCHAR2,expiration_secs  IN  INTEGER   DEFAULT 864000);
• Exceptions: ORA-20000, ORU-10003: Unable to find or insert lock <lockname> into catalog dbms_lock_allocated.

DBMS_LOCK.SLEEP

This procedure suspends the session for a given period of time. Amount of time, in seconds, to suspend the session should be passed as a parameter.

•  Syntax: DBMS_LOCK.SLEEP ( Seconds IN NUMBER);

DBMS_LOCK.REQUEST

This function requests a lock with a given mode. REQUEST is an overloaded function that accepts either a user-defined lock identifier, or the lock handle returned by the ALLOCATE_UNIQUE procedure.

• Syntax: DBMS_LOCK.REQUEST( id  IN  INTEGER ||  lockhandle     IN  VARCHAR2, lockmode       IN  INTEGER DEFAULT X_MODE, timeout       IN  INTEGER DEFAULT MAXWAIT,  release_on_commit  IN  BOOLEAN DEFAULT FALSE, ) RETURN INTEGER;

The current default values, such as X_MODE and MAXWAIT, are defined in   the DBMS_LOCK package specification. The return value can be 0-on success ,1-Timeout, 2-DeadLock, 3-Parameter error, 4-Already own lock specified by id or lockhandle , 5-Illegal lock handle  .

DBMS_LOCK.CONVERT  

This function converts a lock from one mode to another. CONVERT is an overloaded function that accepts either a user-defined lock identifier, or the lock handle returned by the ALLOCATE_UNIQUE procedure. 

•  Syntax: DBMS_LOCK.CONVERT(id  IN INTEGER || lockhandle IN VARCHAR2, lockmode   IN INTEGER,  timeout IN NUMBER DEFAULT MAXWAIT) RETURN INTEGER;
            This function also returns same values as above function.

DBMS_LOCK.RELEASE

This function explicitly releases a lock previously acquired using the REQUEST function. Locks are automatically released at the end of a session. RELEASE is an overloaded function that accepts either a user-defined lock identifier, or the lock handle returned by the ALLOCATE_UNIQUE procedure.

• Syntax: DBMS_LOCK.RELEASE ( id         IN INTEGER)  RETURN INTEGER
• Syntax: DBMS_LOCK.RELEASE ( lockhandle IN VARCHAR2) RETURN INTEGER;
• The return value can be 0-on success, 3-Parameter error, 4- Do not own lock specified by id or lockhandle, 5-Illegal lock handle.