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

Thanks

I did a search on metalink, and found some good articles.

Note 131704.1 Lots of DBA scripts here.

Note:102925.1   Tracing sessions
Note:74089.1 "Find Session or User who is Locking Record"
Note:132629.1 "How to know which row is locked by what user".

Lots of info on how to tie together V$SESSION with SQL. But nothing to tie together V$TRANSACTION, with SQL.

Select *
from v$CURSOR

gives great info. It even shows the SID, USER_NAME and SQL_TEXT. But it can return info when there is nothing in V$TRANSACTION.

This is a good mystery. Perhaps in the tables underlying the views?

yong321_at_yahoo.com (Yong Huang) wrote in message news:<b3cb12d6.0205021000.e948452_at_posting.google.com>...
> 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 Fri May 03 2002 - 16:52:02 CDT