Re: get the sql producing lock

From: NetComrade <netcomradeNSPAM_at_bookexchange.net>
Date: Mon, 20 Mar 2006 14:25:15 -0500
Message-ID: <190u129qqs5gdf1sbjb3eqg023d9gj66oa@4ax.com>

On Mon, 20 Mar 2006 20:15:29 +0100, Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote:

>On Mon, 20 Mar 2006 12:34:52 -0500, NetComrade
><netcomradeNSPAM_at_bookexchange.net> wrote:
>
>>I am not aware of 'current transaction sql'.
>
>
>You could find that by linking v$session.taddr to
>v$transaction.address

Yes.. I thought my scripts looked elsewhere.. too bad there isn't a way to associate 'all' sql belonging to a transaction with xidusn.

the following script might be useful for OP.

select username||'@'||machine||'('||osuser||'|'||program||')' username, s.sid||','||s.serial# sidc,rn.name,

        t.xidslot slot,t.xidsqn sqn,t.used_ublk, s.sql_hash_value sqlhash,t.used_urec, (sysdate- to_date(t.start_time,'mm/dd/yy hh24:mi:ss
'))*24*60*60 secs

    from    v$transaction   t
           ,v$session       s
           ,v$rollname      rn
    where  t.addr     = s.taddr

    and t.xidusn = rn.usn
order by secs desc
.......
We run Oracle 9.2.0.6 on RH4 AMD
remove NSPAM to email Received on Mon Mar 20 2006 - 13:25:15 CST