How to find the sql associated with a transaction?

[Roger Redford]

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

[Sybrand Bakker]

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

[Yong Huang]

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...@yahoo.com (Roger Redford) wrote in message news:<a8c29269.02050...@posting.google.com>...

[Roger Redford]

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?

yon...@yahoo.com (Yong Huang) wrote in message news:<b3cb12d6.0205...@posting.google.com>...