Re: How to find the sql associated with a transaction?

You're almost there. You're confusing a transaction state object with a SQL cursor. Their addresses are of course different. (In fact you run the very small risk of accidentally matching them!)

v$sql(text or area) address can match v$session.sql_address. That's the most commonly used. Other views such as v$open_cursor has an address column that can also match v$sqlXXX.address.

If you write a script, make sure you also include the hash value columns. Doing it manually doesn't need this because you can always tell if the query returns two SQLs with the same address but different hash values. Actually I've never seen this happen to me.

Yong Huang

dba_222_at_yahoo.com (Roger Redford) wrote in message news:<a8c29269.0205020647.5a13a1e1_at_posting.google.com>...
> Hi all,
>
> Recently, we had some problems with a distributed transaction.
>
> I can see if there are transactions in v$transaction. The
> question was, what is the SQL for that transction?
>
> To test, I did I simple insert, but didn't commit.
>
> Select count(1)
> from V$TRANSACTION 1
>
> I've managed to join V$TRANSACTION with:
>
> V$SESSION:
>
> Select count(*)
> FROM V$TRANSACTION tx,
> V$session sess
> where tx.ses_ADDR = sess.sADDR 1
>
>
> But, when I've joined to the views:
> V$SQL, V$SQLTEXT, V$SQLAREA, I've got 0 rows.
Received on Thu May 02 2002 - 13:00:49 CDT