Find out the Locking DML statement

From: Bigfoot <ext6axp_at_ups.com>
Date: Wed, 13 Dec 2000 14:21:06 -0500
Message-ID: <918i75$3ug4_at_biko.telecom.ups.com>


Is there a way to find out the DML statement that the session executed using the information available from V$LOCK table.

i.e
An user executes the following statements and his SID is 11

SESSION 11: SQL> update emp set sal = sal + 1000 where empno = 100;

SQL> select sal from emp where empno = 100;

Since the SELECT is last statement executed by joining V$SESSION.sql_hash_value and V$SQL.hash_value I could only find the last executed SELECT statement. But the transaction is not committed yet and hence there are entries available in V$LOCK table for that UPDATE statement.

SESSION 12: SQL> select * from v$lock where sid=11;

addr                               kaddr
sid        type     id1           id2         lmode
------------------------------ ------------------------------      ---
--       ------    ---------      ---------   ---------
80000001000C0CD8 80000001000C0E10     11         TX     196660
34827  6
C00000002313B678 C00000002313B6A0      11        TM     208192      0
3

Is there anyway to find out what is the DML (UPDATE) statement executed by session 11
using the information available from V$LOCK table for the session 11. Received on Wed Dec 13 2000 - 20:21:06 CET

Original text of this message