Re: Urgent !!!!!!! LOCKING SQL

On Wed, 20 Dec 2000 10:57:37 -0500, "Bigfoot" <ext6axp_at_ups.com> wrote:

I think there might be a way to see what was done if you are on Oracle 8.1.6 or greater, or if you have a copy of this. There is a utility called Log reader that can read through Archive logs. I believe the SQL statement and the results are placed here and you can use this utility to reclaim it. I am not 100% sure about having the SQL there, but I know the before data is there.

Take a look into this.

Regards
Bob

>Is there a way to find out the DML statement that a 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 I want to know the Update statement that has entry in V$LOCK
>table.
>The transaction is not committed yet and hence there are entries
>available in V$LOCK table for that UPDATE statement.
>
>
>If I query the V$LOCK table for session 11 I get the following rows.
>Using these information, Can I pull out the DML (UPDATE) statement
>corresponding to this.
>
>
>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
>
>
>Thanks in advance for your help.
>
>Pushparaj
>
Received on Tue Dec 26 2000 - 05:37:39 CET