Re: help: v$rollstat, status = full

In article <TCTM8.13377$fM.5315_at_nwrddc01.gnilink.net>,  "charlie.peltier" <charlie.peltier(delete here-antispam)@verizon.net>  wrote:

> What does that suppose to mean?
>
> I checked dba_rollback_segs
> there is plenty space in the tablespace for the rollback segment to grow.
> And the max_extents is way up. I am very confused here. Could someone help
> me out?
>
> Thanks
>
> SQL> ed
> Wrote file afiedt.buf
>
> 1 select max_extents from dba_rollback_segs
> 2* where segment_name = 'TRBS01'
> SQL> /
>
> MAX_EXTENTS
> -----------
> 32765
>
> 1 row selected.
>
> SQL> select status, name,extents from
> 2 v$rollstat a, v$rollname b
> 3 where a.usn=b.usn;
>
> STATUS NAME EXTENTS
> --------------- ------------------------------ ----------
> FULL TRBS01 12
>
> 17 rows selected.
>
> It is only 12 extents, the max is 32765. Why?
>
> Thanks

The STATUS column in DBA_ROLLBACK_SEGS is not as accurate as that in V$ROLLSTAT. To get a true picture, use the latter. That being said, a status of FULL means that the rollback segment has reached the limit of extents as defined by MAX_EXTENTS. It is online, but no new transactions will be allowed to use it. If you have reached this point, you may want to consider some rollback maintenance; adding data files is usually recommended at this point.

As to why 12 extents and not 32765? Probably due to the size of the extents. That, plus there are probably uncommitted transactions that are preventing the rollback segment from wrapping.

-- 
The underscore character does not belong in my address. You know the drill...
***
Anyone sufficiently smart enough to configure and use USEnet for research should
be smart enough to Read The Freakin' Documentation!