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
      ,s.username
      ,s.sid
      ,r.segment_name
      ,t.space
      ,t.recursive
      ,t.noundo
      ,t.used_ublk
      ,t.used_urec
      ,t.log_io
      ,t.phy_io
      ,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.

Jared



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?
> 
> --
> //www.freelists.org/webpage/oracle-l
> 


-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
//www.freelists.org/webpage/oracle-l

Other related posts: