DBMS_LOCK , 2 of 6

ALLOCATE_UNIQUE Procedure

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.

If you choose to identify locks by name, you can use ALLOCATE_UNIQUE to generate a unique lock identification number for these named locks.

The first session to call ALLOCATE_UNIQUE with a new lock name causes a unique lock ID to be generated and stored in the dbms_lock_allocated table. Subsequent calls (usually by other sessions) return the lock ID previously generated.

A lock name is associated with the returned lock ID for at least expiration_secs (defaults to 10 days) past the last call to ALLOCATE_UNIQUE with the given lock name. After this time, the row in the dbms_lock_allocated table for this lock name may be deleted in order to recover space. ALLOCATE_UNIQUE performs a commit.

Caution:

Named user locks may be less efficient, because Oracle uses SQL to determine the lock associated with a given name.

Syntax

DBMS_LOCK.ALLOCATE_UNIQUE (
   lockname         IN  VARCHAR2,
   lockhandle       OUT VARCHAR2,
   expiration_secs  IN  INTEGER   DEFAULT 864000);

Parameters

Table 23-3 ALLOCATE_UNIQUE Procedure Parameters

Parameter Description

Parameter	Description
lockname	Name of the lock for which you want to generate a unique ID. Do not use lock names beginning with `ORA$`; these are reserved for products supplied by Oracle Corporation.
lockhandle	Returns the handle to the lock ID generated by `ALLOCATE_UNIQUE`. You can use this handle in subsequent calls to `REQUEST`, `CONVERT`, and `RELEASE`. A handle is returned instead of the actual lock ID to reduce the chance that a programming error accidentally creates an incorrect, but valid, lock ID. This provides better isolation between different applications that are using this package. `LOCKHANDLE` can be up to `VARCHAR2` (128). All sessions using a lock handle returned by `ALLOCATE_UNIQUE` with the same lock name are referring to the same lock. Therefore, do not pass lock handles from one session to another.
expiration_specs	Number of seconds to wait after the last `ALLOCATE_UNIQUE` has been performed on a given lock, before permitting that lock to be deleted from the `DBMS_LOCK_ALLOCATED` table. The default waiting period is 10 days. You should not delete locks from this table. Subsequent calls to `ALLOCATE_UNIQUE` may delete expired locks to recover space.

lockname

Name of the lock for which you want to generate a unique ID.

Do not use lock names beginning with ORA$; these are reserved for products supplied by Oracle Corporation.

lockhandle

Returns the handle to the lock ID generated by ALLOCATE_UNIQUE.

You can use this handle in subsequent calls to REQUEST, CONVERT, and RELEASE.

A handle is returned instead of the actual lock ID to reduce the chance that a programming error accidentally creates an incorrect, but valid, lock ID. This provides better isolation between different applications that are using this package.

LOCKHANDLE can be up to VARCHAR2 (128).

All sessions using a lock handle returned by ALLOCATE_UNIQUE with the same lock name are referring to the same lock. Therefore, do not pass lock handles from one session to another.

expiration_specs

Number of seconds to wait after the last ALLOCATE_UNIQUE has been performed on a given lock, before permitting that lock to be deleted from the DBMS_LOCK_ALLOCATED table.

The default waiting period is 10 days. You should not delete locks from this table. Subsequent calls to ALLOCATE_UNIQUE may delete expired locks to recover space.

Errors

ORA-20000, ORU-10003: Unable to find or insert lock <lockname> into catalog dbms_lock_allocated.