Re: preemptive rollback maintanance?

From: Winnie Liu <poohland_at_hotmail.com>
Date: Mon, 6 Jul 1998 20:25:41 -0700
Message-ID: <6ns47t$cs1@sjx-ixn2.ix.netcom.com>

check the v$rollstat to check the amount of bytes active transactions used up in the rollback segment. Also check the high water mark of each of the rollback segment, if they moved up, that means the current transaction is pushing up the high water mark of the rollback segment. Below is a tip I got from www.oramag.com

select osuser o,       username u,       segment_name s,
       sa.sql_text txtfrom   v$session s,       v$transaction t,
       dba_rollback_segs r,       v$sqlarea sawhere  s.taddr = t.addr

and t.xidusn = r.segment_id(+)and s.sql_address = sa.address(+)

This can view the active transactions in all the rollback segments.

Winnie

David wrote in message ...
>Not easily, but you could write a script that checks dba_rollback_segs /
>dba_extents / dba_free_space to see if a rollback sgement is about to hit
>max extents or the is not enough room for it to extend. Then check
>v$rollstat for active tranactions in that rollback segment. I think
>Enterprise Manager has an event for segments reaching threshholds.
>
>Regards
>David Russell
>
>Steve Haynes wrote in message ...
>>Hi All,
>>Is there a way (oracle 8) to monitor how close a transction
>>is to running out of rollback space?
>>Steve
>>--
>>"The floggings will continue until morale improves."
>>
>
>
Received on Mon Jul 06 1998 - 22:25:41 CDT