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?
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.