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

On 2 May 2002 07:47:43 -0700, dba_222_at_yahoo.com (Roger Redford) wrote:

>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.
>
>Select count(1)
>FROM V$TRANSACTION tx,
> V$SQL sql
>where tx.ADDR = sql.ADDRESS 0
>
>
>Select count(1)
>FROM V$TRANSACTION tx,
> V$SQLTEXT sql
>where tx.ADDR = SQL.ADDRESS 0
>
>
>Select count(1)
>FROM V$TRANSACTION tx,
> V$SQLarea sql
>where tx.ADDR = SQL.ADDRESS 0
>
>
>Select count(1)
>FROM V$TRANSACTION tx,
> V$SQL_BIND_DATA sql
>where tx.ADDR = SQL.BUF_ADDRESS 0
>
>
>Am I not joining the right views? Does anyone know
>how to find the SQL associated with a transaction?
>
>Thanks

v$session.taddr = v$transaction.addr

(sounds logical, don't you think. v$session.paddr = v$process.address)

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu May 02 2002 - 12:10:34 CDT