Ask the Expert

Query to find user who is locking record from rowid

I have the rowid of a locked record in a specified table in Oracle 10g. I need to know the username of the user locking the record from the rowid that I have. Which query can be used to get such information? Thanks for your help in advance.

    Requires Free Membership to View

If you have the rowid, then you can use the DBMS_ROWID package to obtain the object name for the table that holds this row. To obtain the object_id of the table, use the following query:

SELECT DBMS_ROWID.ROWID_OBJECT('rowid') FROM dual;

Make sure you insert your rowid into the function above. Once you know the object_id, you can query V$LOCKED_OBJECT similar to the following:

SELECT session_id,oracle_username,locked_mode 
FROM v$locked_object WHERE object_id = xx;

Obviously, you will replace 'xx' with the object id returned from your call to DBMS_ROWID.

This was first published in May 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: