Q

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?

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.

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

1 comment

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close