When a record is locked ,can we know which one is locked?
How can I get the record rowid or something else info?
I can get some info by this sql
SELECT c.ROW_WAIT_OBJ#,c.ROW_WAIT_FILE#,c.ROW_WAIT_BLOCK#,c.ROW_WAIT_ROW#
FROM v$locked_object a, dba_objects b, v$session c
WHERE a.object_id = b.object_id
AND a.SESSION_ID = c.sid(+)
I found a method in web to get rowid by using function DBMS_ROWID.ROWID_CREATE()
But it doesn't seem to work.
Best Answer
You can't really list all rows that are being locked by a session. However, once a session is being blocked by another, you can find which session/row is blocking it.
Oracle doesn't maintain a list of individual row locks. Rather, locks are registered directly inside the rows themselves -- think of it as an extra column.
You can find which session has acquired a lock on an object through the
V$LOCK
view, but this will only list general information, not at the row level.With this view you can also find if a session is being blocked by another. In that case, if a session is blocked by another session, the row information is displayed in the
V$SESSION
information.You can retrieve the rowid, let's build an example with 2 sessions:
Session 2 is now waiting on Session 1. We can discover the blocking row with:
For further reading: a description of the process by Tom Kyte.