I have a lock handle from dbms_lock.allocate_unique() but I would like to get the Lock ID from the handle. By ID I mean the value of SYS.DBMS_ALLOCATED_LOCK.LOCKID and V$LOCK.ID1. From what I can see, it seems that the Lock Handle comprises the Lock ID concatenated with another number. However, they are both just numbers, so I don't know whether I can rely on a blunt lock ID = substr(lock_handle, 10).

Why I want this. I would like to be able to check for active User Defined locks in V$LOCK without having to look up the LOCKID in DBMS_ALLOCATED_LOCK by NAME (as NAME is not indexed).

I know why Oracle obfuscate the Lock ID behind the Lock Handle, but in this case I can be quite sure that the Lock ID won't be in a collision.

  • 1
    What is SYS.DBMS_ALLOCATE_LOCK? Such object does not exist. DBMS_LOCK_ALLOCATED.NAME is indexed. Dec 6, 2022 at 19:29
  • @BalazsPapp I meant DBMS_LOCK_ALLOCATED. I'll check about the index. If you're right about NAME being indexed I will need to change my thinking. I might just need a hint to correct the path.
    – APC
    Dec 6, 2022 at 19:36


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.