Re: How to identify large transactions

  • From: Jared Still <jkstill@xxxxxxxxx>
  • To: jdunn@xxxxxxxxx
  • Date: Tue, 4 Jan 2005 21:52:46 +0000

You will may want to check out v$session_longops.

In regards to suggestions that you investigate v$transaction,
this will get you started:

select s.osuser
      ,substr(sa.sql_text,1,200) txt
from v$session s,
     v$transaction t,
     dba_rollback_segs r,
     v$sql sa
where s.saddr=t.ses_addr
and   t.xidusn=r.segment_id(+)
and   s.sql_address=sa.address(+)

You will need to adjust it if using Automatic Undo.


On Tue, 4 Jan 2005 13:08:03 -0000, John Dunn <jdunn@xxxxxxxxx> wrote:
> I believe that some developers sql is generating large transactions possibly
> due to lack of commit statements.
> How can I identify these large transactions and the sql responsible?
> --
> //

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Other related posts: