Re: Free space in rollback segments?

In article <5dteth$3gd_at_lana.zippo.com>,
  Jeff Boes <jboes_at_qtm.net,jboes_at_cimflexpb.zds.com> wrote:
>
> Our database (2 GB data, 2 GB indexes) appears to be running out
> of rollback segment space. My predecessor has some cron scripts
> which monitor free space, and the scripts have suddenly started
> complaining about our rollback segment tablespace. My problem: I'm
> not a very experienced DBA, and this appears to be a serious problem,
> but it is not addressed in any of the documentation I've been able
> to uncover.
>
> Here's some of the pertinent information (then my conclusions, so
> you won't think of me as a lazy slacker):
>
> SQL> l
> 1 SELECT
> 2 SUBSTR(DS.SEGMENT_NAME,1,22) R_SEGMENT,
> 3 SUBSTR(DS.TABLESPACE_NAME,1,20) TABLESPACE,
> 4 DS.BLOCKS,
> 5 DS.EXTENTS,
> 6 DRS.STATUS
> 7 FROM DBA_SEGMENTS DS,DBA_ROLLBACK_SEGS DRS
> 8 WHERE DS.SEGMENT_NAME = DRS.SEGMENT_NAME
> 9* ORDER BY 1
> SQL> /
>
> R_SEGMENT TABLESPACE BLOCKS EXTENTS STATUS
> ---------------------- --------------- ---------- ---------- ----------------
> R00 MMXRBS 24320 18 ONLINE
> R01 MMXRBS 1820 7 ONLINE
> R02 MMXRBS 2860 11 ONLINE
> R03 MMXRBS 5200 20 ONLINE
> R04 MMXRBS 23140 89 ONLINE
> R05 MMXRBS 1560 6 ONLINE
> R06 MMXRBS 31460 121 ONLINE
> R07 MMXRBS 12740 49 ONLINE
> R08 MMXRBS 4160 16 ONLINE
> R09 MMXRBS 1820 7 ONLINE
> R10 MMXRBS 31460 121 ONLINE
> SYSTEM SYSTEM 30 3 ONLINE
>
> 12 rows selected.
>
> SQL> select * from dba_free_space
> 2 where TABLESPACE_NAME = 'MMXRBS'
> 3 /
>
> TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
> -------------------- ---------- ---------- ---------- ----------
> MMXRBS 11 38262 1138688 139
> MMXRBS 10 60702 811008 99
> MMXRBS 6 41582 155648 19
>
> 1 select * from dba_data_files
> 2* where TABLESPACE_NAME = 'MMXRBS'
> SQL> /
>
> FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
> --------------------- --------------- ---------- ------ ---------
> rollback1.dbf 6 MMXRBS 340787200 41600 AVAILABLE
> rollback2.dbf 10 MMXRBS 498073600 60800 AVAILABLE
> rollback3.dbf 11 MMXRBS 314572800 38400 AVAILABLE
>
> What I've figured out so far:
>
> 1) The RBS is fragmented, but that's not bad.
>
> 2) There aren't any long-running queries or heavy-duty transactions
> in progress.
>
> 3) The database was bounced 3 days ago; the scripts started reporting
> the problem Monday evening.
>
> 4) Performance isn't (currently) suffering.
>
> So what is the deal here? The DB appears to have plenty of RBS,
> and not that many of them are in use. Why is there so little
> free space in the tablespace?

I apologize for reposting this in its entirety, but I figure if I'm going to go fishing for a response again, I need to set the context.

I found several replies to my query here, and got a similar response from Oracle, but unfortunately my experience with RBS topics is so limited I can't understand the answers. They appear (to me!) to have nothing to do with my problem.

(Please e-mail responses; I have a very unreliable Usenet connection at present, which is why some of the responses didn't find their way to me for over 10 days.)

Here are a few of the replies I got:

>Rollback segments will continue to extend upon demand unless you specify
>an Optimal value for the segments, in which case they'll be 'shrunk'
>back down to that value when possible.

and:

>If there are no large jobs eating into your rollback, then I would
>advise the use of an optimal rollback segment size. The end result is
>that after a job has finished using rollback, if it did extend past the
>optimal setting, it will shrink back the next time someone uses that
>rollback segment.

And in follow-up to that:

>It will ONLY shrink when:
>1. The Rollback segment is used
>AND
>2. It needs to extend beyond the optimal value for the current transaction.

<puzzled look> I thought that the segmentation and extents for a tablespace had nothing to do with the free space! The tablespace is fragmented, but according to what I've read about RBS, that's *good* as it means that subsequent transactions have lots of different chunks of space to grab when they need it. My (perhaps faulty) premise is that RBS is freed when a transaction is done with it, else how would the next transaction know which RBS it can have?

I must point out again that this database isn't "mine", it's inherited from a DBA who's long gone. His free-space monitoring scripts seem designed to watch over the data and index tablespaces, but the script just recently started complaining about the RBS tablespace as being nearly full.

Jeffery Boes | "A good scapegoat is nearly as welcome Oracle DBA/Developer/ | as a solution to the problem." Web Engineer for hire: SW MI/NW IN perm, contract nearly anywhere!

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet