Re: How to see how much space I have left on the rollback segments ?

> Just so we don't confuse the original poster:
>
> v$rollstat is in all versions of Oracle
> v$undostat is in version 9i only.
>
> Since the OP didn't mention a version, but did mention 'rollback' in his
> subject line, I went with the more-widely-available option.

When looking at original poster's question, then neither v$rollstat nor v$undostat alone help in his matter. Actually the question itself is a bit vague. Is it how many free blocks do I got in rollback segments currently (without extending the segment) or how much can a segment grow before they hit max extents limit.

Even though we see from v$rollstat, which is the current extent or block, we don't know how much non-expired data we got in previous extents. We could only guess or start dumping undo chains. If we want to see, how much a rollback segment could grow before getting error, then this information is very easy to get from dba_rollback_segs (max_extents) and dba_free_space (also dba_data_files if want to consider autoextend).

And v$undostat doesn't actually give us directly any information how much the automatically managed undo segments could grow, or how much free space they got in them. Instead, they provide us even more valuable information such:
* SSOLDERRCNT (how many times ORA-1555 has occurred) * NOSPACEERRCNT (how many times no free space at all was possible to allocate in undo seg)
And three UNXP% parameters. SSOLDERRCNT and NOSPACEERRCNT should always be zero in normally working system and UNXP parameters (number of unexpired undo blocks/extents which have actually been allocated to another transaction before undo_retention should allow).

Tanel. Received on Mon Aug 04 2003 - 17:35:32 CDT