Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Free space in rollback segments?
Jeff Boes 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?
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.
-- Rich Woods Technical Field Support Specialist, Oracle Corporation, USA The above statements and opinions are my own and do not necessarily represent those of Oracle Corporation.Received on Thu Feb 13 1997 - 00:00:00 CST