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 last published in May 2006

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Join the conversation

1 comment

Send me notifications when other members comment.

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

Please create a username to comment.

I see problem when there will be more than one user with own lock on different row on the same table. What is the solution to discover specific user for specific ROWID ?
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close